How EF Core 10 translates Contains over a list of IDs, and when to change it
The SQL in the log looks different since EF Core 10. A Contains over a list of IDs used to send one parameter holding a JSON array, unpacked with OPENJSON. Now it comes out as IN (@ids1, @ids2, @ids3). I wanted to know what that means for a list of 3 IDs and for one of 3,000, so I ran it against SQL Server 2022 in Docker.
For short lists, the new form is better and I'd leave it alone. The query to look at is the one that sends hundreds or thousands of IDs. On my laptop, 2,000 IDs took about 49 ms as separate parameters and about 3 ms as one JSON parameter. I'd move that one query back with EF.Parameter and keep the new default everywhere else.
What EF Core 10 sends now
The query is the plain one: ids.Contains(o.Id) inside a Where. For three IDs, then for eight, EF Core 10.0.12 sent this:
WHERE [o].[Id] IN (@ids1, @ids2, @ids3)
WHERE [o].[Id] IN (@ids1, @ids2, @ids3, @ids4, @ids5, @ids6, @ids7, @ids8, @ids9, @ids10)Eight IDs became ten parameters. EF pads the list, and the two extra parameters carry the eighth value again, so the rows don't change.
EF Core 9 printed this for the same three IDs, through ToQueryString:
DECLARE @__ids_0 nvarchar(4000) = N'[1,2,3]';
SELECT [o].[Id], [o].[CustomerId], [o].[Total]
FROM [Orders] AS [o]
WHERE [o].[Id] IN (
SELECT [i].[value]
FROM OPENJSON(@__ids_0) WITH ([value] int '$') AS [i]
)The reason for the change is the row estimate. With one JSON string, SQL Server has to guess how many values come out of OPENJSON. I filtered on a customer column where every customer has exactly 40 orders. For 3, 30 and 300 customers, the new form's estimate was 120, 1,200 and 12,000, spot on. The JSON form estimated 282.8 rows every time.
The padding, and where it stops
Padding keeps the number of different SQL strings down, so SQL Server doesn't cache a plan for every list length. The docs don't give the full rule, so I sent every length from 1 to 2,200 and counted.
Up to 5 IDs there's no padding. Then EF rounds up to the next 10 until 150, the next 50 until 750, and the next 100 until 2,000. After that it rounds to 10 again until 2,070, then sends the exact count up to 2,098. That's 80 different SQL strings for all lengths from 1 to 2,098.
At 2,099 IDs, EF switches back to one JSON parameter and OPENJSON, with no error. SQL Server takes at most 2,100 parameters in a request, and sp_executesql uses two of them itself (the fix that set this). The count covers the whole command, so one more parameter in the same WHERE moves the switch down by one, and two lists of 1,200 in one query both go to OPENJSON.
Posts about this disagree on what happens past the limit. Some say it falls back quietly, others mention a DivideByZeroException. As far as I can tell, they describe different patches, so I pinned the older packages to check. On 10.0.0, a list of 2,071 to 2,100 IDs threw a DivideByZeroException before anything was sent. On 10.0.1, 2,099 and 2,100 IDs got SQL Server's "too many parameters" error. On 10.0.2 and on 10.0.12, every case I tried falls back.
What I measured
I ran 200 lists, of lengths 1 to 200, through each form and counted the cached plans. The new default made 21. EF.Parameter, the JSON form, made 1. EF.Constant, which writes the values into the SQL, made 200.
Then timings: 30 runs per row, a new random list each time, SQL Server in Docker on the same laptop. Times are in milliseconds; the median is measured in the app and the server column is SQL Server's own average:
mode IDs first median server plans
default 5 4.9 0.55 0.05 1
EF.Parameter 5 4.7 0.54 0.05 1
default 500 21.5 4.29 0.98 1
EF.Parameter 500 5.2 1.35 0.57 1
default 2000 225.4 48.88 15.50 1
EF.Parameter 2000 8.7 3.34 2.12 1At 5 IDs I can't see a difference. At 500 it's about 4 ms against 1.4, and at 2,000 about 49 against 3. The first run at 2,000 took 225 ms against 9. SQL Server's own figure is 15 ms against 2, so most of the gap is outside the query's execution. My guess is the work of building, sending and binding 2,000 parameters and a statement of almost 19,000 characters, though I didn't dig into which costs most.
The better estimate didn't make my 5-ID query faster, since a primary-key lookup has little to gain from it. It matters when the planner has a real choice, like a join order, and I didn't build that case.
Changing it for one query
For the query that sends a long list, I'd change only that query:
var orders = await db.Orders
.Where(o => EF.Parameter(ids).Contains(o.Id))
.ToListAsync();That sends the list as one JSON parameter, whatever its length. There's also a global switch, UseParameterizedCollectionMode, and I'd leave it alone. Flipping it gives up the good estimates on every short list to fix one long one.
The global switch also fooled me while measuring. My first test gave one context Parameter mode and another Constant, in the same process, and both still sent the default SQL. EF caches translated queries, and the mode isn't part of that cache's key in 10.0.12. So the first context to translate a query decides its SQL for all of them. An app with one setting won't notice. A test that compares modes side by side will, unless each context gets its own internal service provider.
When a list reaches the thousands, I'd also ask where the IDs come from. If they came out of the same database a moment earlier, a join or a subquery does the job without the round trip. If they really come from outside, a temp table or a table-valued parameter fits better than 2,000 of anything in a WHERE. All of this is SQL Server; other providers have limits of their own that I didn't measure.
The Contains calls to check are the ones whose lists can grow: IDs from an upload, a grid with select-all, an earlier query. Run each with a realistic list and look at the SQL. Give the long ones EF.Parameter and leave the rest on the new default. And be on 10.0.2 or later, because the first two patches fail on lists near the limit.