One Contains(), Four EF Core Versions: Same Code, Different SQL

In the lab, we ran the same query with EF Core 7, 8, 9, and 10 against SQL Server 2022 and 2025. The result: each version has its strength and its blind spot. EF Core 7 fills the plan cache. EF Core 8 and 9 relieve it, but on skewed data they choose a plan that cost more than twice as much CPU and thirty times as many reads as necessary in the lab. EF Core 10 lies in between and lets the first call decide the plan. Which variant suits your query depends on the data, not on the version number.

Three translations of the same line

var products = await db.Products
    .Where(p => ids.Contains(p.Id))
    .ToListAsync();

This is what the condition [p].[Id] … in the WHERE clause looks like, shortened from the lab logs:

Written out, the form of EF Core 8 and 9 reads:

WHERE [p].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__ids_0) WITH ([value] int '$') AS [i]
)

A few details from the measurements that matter in everyday work:

• EF Core 7 writes the values into the SQL text. Every different list produces a different text. With 3,000 values, the command was a good 25,000 characters long.
• EF Core 8 and 9 always send the same text. Up to a certain length, the parameter is nvarchar(4000), for long lists nvarchar(max); in the lab the switch happened between 100 and 1,000 values. Even an empty list goes through OPENJSON.
• EF Core 10 uses a separate parameter per value and pads the list to fixed sizes: for 1 to 50 values, only ten different texts arose (1, 2, 3, 4, 5, 10, 20, 30, 40, and 50 parameters). The padded slots repeat the last value. From 2,100 values, SQL Server's limit for parameters per command, EF Core 10 switches to OPENJSON. The parameters are also named more briefly (@ids1 instead of @__ids_0).

All variants returned the same rows in all 392 cases checked. So this is not about correctness, but about cost.

Plan cache: 500 texts, one, or ten

In the first experiment, the application calls the query 500 times with random lists of 1 to 50 values, as a saved-items list or a multi-select in a filter would. Measured on SQL Server 2022, median of five repetitions:

With constants, SQL Server compiles nearly every call anew. That costs CPU and fills the plan cache with single-use plans that can push out other, more important plans. Microsoft explicitly names this in the EF Core 10 documentation as the reason for the change. Up to this point, EF Core 8 looks like the clear winner.

Estimates and plans on skewed data

The second experiment does not filter by primary key but by a category:

var prices = await db.Products
    .Where(p => categories.Contains(p.CategoryId))
    .Select(p => new { p.Id, p.Price })
    .ToListAsync();

The table has 2 million rows. One category is large (800,000 rows), 50,000 categories are small (20 rows each). Such distributions are normal in real data: a main tenant, a default status, a major customer. The application asks once for five small categories (100 rows) and once for a list that contains the large one (800,080 rows).

For 100 rows, an index seek with lookup is right; for 800,000 rows, a scan of the table. What SQL Server chooses depends on what it knows about the values (CPU per execution, median of the repeated executions):

What is behind this:

• With the JSON parameter, SQL Server estimates blind. It does not know which values are in the JSON and works with a fixed assumption: 282.7 rows in the lab, no matter which list arrives. That fits the small categories and is off by a factor of 2,800 for the large one. The plan always stays the same: many individual accesses through the index, and for 800,000 rows 2.55 million pages read instead of 81,380.
• With the parameter list of EF Core 10, SQL Server looks at the values on the first call and optimizes for exactly those. That is classic parameter sniffing: if the small list came first, the large one gets a plan that is too small. If the large one came first, the small list scans the whole table, 385 ms CPU instead of 0.7 to 0.8 ms, more than 400 times as much. After a restart or a statistics update, it can turn out the other way round. The typical “sometimes fast, sometimes slow” pattern is described in the performance guide.
• With constants, every list gets its fitting plan, at the price of the compilations from the first experiment.

For the primary key from the first experiment, this does not matter: every value hits exactly one row, every estimate fits. The question “which translation is better” therefore has no general answer. It depends on the column.

When the compatibility level does not fit

OPENJSON requires a database compatibility level of at least 130. Databases that stayed at an old level after a server upgrade are more common than you might think. In the lab with level 120:

• EF Core 8 failed with every list, including the empty one, with error 102 (Incorrect syntax near '$'.). This does not happen at upgrade time, but on the first call in production.
• EF Core 10 initially ran fine at level 120 because it uses parameters instead of JSON. Only from 2,100 values does it switch to OPENJSON and then fails with the same error. A test with small lists does not notice this.
• UseCompatibilityLevel(120) in the provider configuration makes EF write constants again. It does not change the compatibility level of the database, however, only which SQL EF generates. The database stays as it is.

Check the actual level before the update: SELECT name, compatibility_level FROM sys.databases;

Control it instead of accepting it

Since EF Core 8.0.2 and fully from EF Core 9, the translation can be controlled, globally or for a single query. In the lab, all variants built, ran without errors, and produced the expected shape:

• For a single query (EF Core 9 and 10): EF.Constant(ids) writes constants, EF.Parameter(ids) sends a JSON parameter. EF Core 10 additionally has EF.MultipleParameters(ids) for the parameter list.
• As the default for all queries in EF Core 9: in the provider configuration, the methods of the TranslateParameterizedCollections… family, that is …ToConstants() or …ToParameters().
• As the default for all queries in EF Core 10: UseParameterizedCollectionMode(…) with ParameterTranslationMode.Constant, .Parameter, or .MultipleParameters.

A rule of thumb from the measurements, not a guarantee for your data:

• Evenly distributed columns (primary keys, unique numbers): JSON parameter or parameter list. One plan fits all, and the plan cache stays lean.
• Skewed columns (status, tenant, category with outliers): EF.Constant for exactly this query, if it does not run a hundred times per second. Here, compilation costs less than a wrong plan.
• Frequent query on a skewed column: This is no longer an EF topic. Then separate queries for the large values, a suitable index, or plan guidance on the SQL Server side help.

With EF.Constant there was a surprise in the lab: under EF Core 8, the same call took almost twice as long in the client as with EF Core 7 (6.6 instead of 3.5 ms), although the SQL was identical. Under EF Core 9 and 10, the difference was gone. We did not investigate the cause further.

Checking an upgrade

On 10 November 2026, support for EF Core 8 and 9 ends. Anyone who switches to EF Core 10 now changes the SQL of many Contains() queries, and through the new parameter names also the text of every other query with parameters. After the update, SQL Server therefore compiles these statements anew. A short checklist:

1. Check the compatibility level of the databases before EF Core 10 sends lists with more than 2,100 values through OPENJSON.
2. Find the most important queries with lists. Search the code for .Contains( on lists, and search Query Store for OPENJSON(@ or long parameter lists. The query on the overview page shows both.
3. For each of these queries, determine whether the column is skewed. A simple grouping by the column with COUNT(*) is enough.
4. Measure before and after the update with realistic data, with large and small lists in both orders. A single test run shows only the plan that happened to be compiled first.
5. Change EF first, then the compatibility level, not both at once. Otherwise you cannot tell what triggered a change.

More EF topics: Overview · Include, Projection, and Split Query

Sources

• Microsoft: EF Core 8: Queries with primitive collections
• Microsoft: EF Core 8: Contains in LINQ queries may stop working on older SQL Server versions and possible query performance regressions around Contains
• Microsoft: EF Core 9: Parameterized primitive collections
• Microsoft: EF Core 10: Improved translation for parameterized collection and breaking changes
• Microsoft: Compatibility level in the SQL Server provider
• Microsoft: OPENJSON

More EF topics