Bug description

Here is the premise to understand the bug:

• you have a Candidates table
• each candidate can have many Projects (candidateId → many ProjectIds)
• each project can have some date (eg when email for that project was sent to the candidate)
• so one candidate — many dates in many projects.

Now you want to write a query to show candidates that are in specific projects of 1,2,3,… and those candidates should be sorted by the max date of email to each person.

EF generates the following code ↓ As you can see, project ids are not reused between where and order by portions — they are duplicated → hence the double number of actual parameters.

exec sp_executesql N'SELECT [c].[CandidateId]
FROM [dbo].[Candidates] AS [c]
WHERE EXISTS (
    SELECT 1
    FROM [dbo].[Projects] AS [p]
    WHERE [c].[CandidateId] = [p].[CandidateId] AND [p].[ProjectId] IN (@ids1, @ids2))
ORDER BY (
    SELECT MAX([p0].[InviteSentTime])
    FROM [dbo].[Projects] AS [p0]
    WHERE [c].[CandidateId] = [p0].[CandidateId] AND [p0].[ProjectId] IN (@projectIds1, @projectIds2)) DESC, [c].[CandidateId] DESC
OFFSET @p ROWS FETCH NEXT @p ROWS ONLY',N'@ids1 bigint,@ids2 bigint,@projectIds1 bigint,@projectIds2 bigint,@p int',@ids1=1,@ids2=2,@projectIds1=1,@projectIds2=2,@p=25

If you have 1500 ids, each part of the query seems ok for EF to use parametrised version. But because total number of parameters in the final query is ×2 (3000), this query will fail to execute with:

Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

Probably, it is safe to say that some even more complex queries could result in ×3, ×4 number of parameters compared to the actual different number of ids.

How I currently fixed this for EF 10 (obviously, wasn’t needed in EF9 because of openjson)

        // EF is supposed to use openjson and what have you when the parameters
        // list exceeds 2000 items, but if we have something like order by project’s
        // max completion date, we basically double the number of parameters
        // (project IDs in “where” and project Ids in “order by”).
        // So 700 is basically 1400 parameters with some safe margin for other parameters.

        var hasTooManyProjects = ids.Count > TOO_MANY_PROJECTS_THRESHOLD;

        return query.Where(
            c => c.Projects.Any(
                p => hasTooManyProjects
                    ? EF.Constant(ids).Contains(p.ProjectId) // uses inline values that poison SQL plan cache but works
                    : ids.Contains(p.ProjectId) // doesn’t poison SQL plan cache but is limited in params number
                      && (filter.IsProjectCompleted == null
// ... other filtering applied

(the logic above is repeated in where and in order by parts)

Is there something like EF.OpenJsonConstant(ids) to force openjson so I don't need to use EF.Constant(ids)? (cc @roji)

So basically this thing boils to four parts:

1. Can one really rely on EF ability in .NET 10 to count the total number of parameters?
2. If the answer to previous question is no, fallback to openjson is not guaranted.
3. Can one manually trigger openjson logic in a single specific linq query? I see that you've added EF.Constant(ids), is there something like EF.OpenJsonConstant(ids)? As I understand, you 100% have this nob internally when you determine when to switch to openjson. I don't need this for the entire context but only for some heavy linq queries like in the case above. Though, I guess, for my example openjson is almost twice less of performance than ordinary in, but it is great to have if for some god-forsaken reason we need more than 64k(?) items in contains.
4. Was changing the default from EF9 OpenJson to EF10 parametrised logic a breaking change? (in a sense that it definitely broke our code in a way we were not expected) It would be nice to see it in breaking changes list of ef 10.

Your code

-

Stack traces

Verbose output

EF Core version

10.0.0

Database provider

Microsoft.EntityFrameworkCore.SqlServer

Target framework

.NET 10

Operating system

No response

IDE

No response