Filtering EF Core by a Large List: The 2,100 Parameter Wall

Filter an EF Core query by a big list of IDs and you eventually meet SQL Server’s 2,100 parameter limit. Through EF Core 10 the usable ceiling is actually 2,098, because SqlClient sends your query via sp_executesql, which spends two parameters on itself.

Here’s the part that surprised me, and it changes the advice you’ll find almost everywhere else: in EF Core 10, .Where(p => ids.Contains(p.Id)) doesn’t throw when your list crosses that ceiling. EF switches translation strategy on its own and keeps working. I ran it with 100,000 IDs and got 100,000 rows back.

What you get instead of an exception is a performance cliff you can’t see from your C#. I benchmarked seven approaches across eight list sizes on a one-million-row table. The sharpest result: the same query with 2,098 IDs took 34.6 ms, and with 2,099 IDs took 4.0 ms. One extra ID made it roughly eight times faster.

TL;DR. EF Core 10 translates Contains to multiple scalar parameters by default, and pads that list, so your parameter count is always at least your list length. Past 2,098 parameters EF silently falls back to a single JSON parameter with OPENJSON. Below roughly 1,000 IDs, leave the default alone. Between 1,000 and 2,098 the default is the slowest built-in option and EF.Parameter(ids) is about 8x faster. Above 2,098 the default already is EF.Parameter in disguise, so it’s fine. At 100,000 IDs a hand-rolled temp table won. EF.Constant is excellent for tiny fixed sets and dies at 100,000.

Every number in this article comes from a runnable project. The sample repo has a probe that captures the SQL EF actually sends, a BenchmarkDotNet matrix, and a Web API showing which approach belongs on which endpoint. Clone it and reproduce all of it against your own schema.

”The incoming request has too many parameters. The server supports a maximum of 2100 parameters”

This error means a single command sent SQL Server more than 2,100 parameters. Microsoft documents 2,100 as the cap for a stored procedure, and SqlClient sends your query as one through sp_executesql, so that is your per-query ceiling too. It’s a server limit, not an EF Core setting.

The more useful question in EF Core 10 is when you actually see it, and the honest answer is: less often than you’d expect, and rarely from Contains. A parameterized collection that grows past the ceiling gets a different translation instead of an exception. The error still bites everywhere EF isn’t making that decision for you:

• SaveChanges batching. Inserts and updates use roughly rows × columns parameters per command, and that path has no fallback. Bulk operations in EF Core covers the batch-size knobs that control it.
• Composite predicates. A list of 2,000 IDs plus joins, pagination values, and anything contributed by global query filters or a soft delete flag. Every one of those draws from the same budget.
• Raw ADO.NET and Dapper, where you’re building the parameter list yourself.
• EF Core 8 and 9, where the default translation was different, and older EF versions where collections were inlined as constants.

What EF Core 10 Actually Sends

Take an ordinary query against a Products table:

var ids = new List<int> { 1, 2, 3 };

var matched = await context.Products
    .AsNoTracking()
    .Where(p => ids.Contains(p.Id))
    .ToListAsync();

In EF Core 8 and 9, that list traveled as one JSON array parameter, unpacked server-side with OPENJSON. In EF Core 10 it becomes one scalar parameter per value:

SELECT COUNT(*)
FROM [Products] AS [p]
WHERE [p].[Id] IN (@ids1, @ids2, @ids3)

The official reasoning is that this shape keeps the SQL stable across different list contents, which protects the plan cache, while still telling the query planner how many values are coming. That’s a good trade for the common case.

One more thing to notice: parameter names lost their old @__ids_0 decoration in EF Core 10 and are now just @ids1. If an article shows you @__ids_0, it was written for an earlier version. Worth planning for on upgrade day, too. Microsoft warns that the rename invalidates almost every cached plan on the server, so expect a compilation spike right after you deploy.

Your parameter count is bigger than your list

EF pads the parameter list so that lists of similar length produce identical SQL. Ask for 8 IDs and you get 10 parameters:

SELECT COUNT(*)
FROM [Products] AS [p]
WHERE [p].[Id] IN (@ids1, @ids2, @ids3, @ids4, @ids5, @ids6, @ids7, @ids8, @ids9, @ids10)

@ids9 and @ids10 repeat the value of @ids8, so the result is unchanged. Microsoft documents the behavior but not the bucket sizes, so I measured them:

The buckets shrink as you approach the ceiling and stop entirely at the top, because rounding up there would push you over 2,098. So 751 IDs send 800 parameters, while 2,094 send exactly 2,094.

After measuring, I went looking for the rule in the provider source. It turns out to be hardcoded in CalculateParameterBucketSize, comments and all:

protected override int CalculateParameterBucketSize(int count, RelationalTypeMapping elementTypeMapping)
{
    if (count <= 5) return 1;
    if (count <= 150) return 10;
    if (count <= 750) return 50;
    if (count <= 2000) return 100;
    if (count <= 2070) return 10; // try not to over-pad as we approach that limit
    if (count <= MaxParameterCount && UseOldBehavior37151) return 0;
    if (count <= MaxParameterCount) return 1; // just don't pad between 2070 and 2100, to minimize the crazy
    return 200;
}

A bucket size of 1 means no padding, which is why small lists and near-ceiling lists both send exactly what you gave them. “To minimize the crazy” is carrying a lot of weight in that comment, and the thresholds line up with what I measured exactly. The practical point: padding puts the ceiling closer than it looks, so counting your list length alone will mislead you.

The Real Ceiling Is 2,098

SQL Server’s documented cap is 2,100. EF Core’s SQL Server provider works to a lower number, visible in SqlServerSqlNullabilityProcessor:

private int MaxParameterCount => UseOldBehavior37336 ? 2100 : 2100 - 2;

Those two missing parameters belong to sp_executesql, which SqlClient uses to send the command. This was corrected in EF Core 10.0.2. The issue is #37336, and its entire body is one sentence: “While the maximum is 2098, because of sp_executesql.” The servicing PR #37334 is where the customer impact gets spelled out: “Query fails to execute when collection with 2099 or 2100 parameters (exactly) is present in the query.”

This particular code path had a rough first few months. Two other fixes landed alongside it, and both are worth knowing if you’re pinned to an early EF Core 10 build:

Microsoft rates the underlying translation change “Low impact” on the breaking changes page. Three shipped fixes in two patch releases is my argument for treating that rating as optimistic. Run 10.0.2 or later before you benchmark anything in this area.

What Happens When You Cross It

I ran the same Contains query at every size around the boundary and captured the command EF sent. The switch is exact:

At 2,099 the SQL becomes the EF Core 9 shape:

SELECT COUNT(*)
FROM [Products] AS [p]
WHERE [p].[Id] IN (
    SELECT [__openjson0].[Value]
    FROM OPENJSON(@ids) WITH ([Value] int '$') AS [__openjson0]
)

EF makes this decision inside VisitIn, which hands oversized collections to a routine whose comment says it plainly: “If we’re over that limit, we switch to using single parameter and processing it through JSON functions.” If the server has no JSON support, it inlines the values as constants instead. Neither path throws.

That’s the correctness story. Here’s the performance story, on a one-million-row table:

One extra ID, and the query got about 8x faster and allocated about 4.6x less. The slow side is the default side. Everything from roughly 1,000 IDs up to the ceiling sits in that expensive band, and nothing in your code signals it.

Benchmarking Every Option

Seven approaches, eight list sizes, one million rows, a clustered primary key on Id and a non-clustered index configured through the Fluent API. Every query runs AsNoTracking so the change tracker isn’t part of what I’m measuring. BenchmarkDotNet 0.15.8 on .NET 10.0.11, EF Core 10.0.11, SQL Server 2025 LocalDB, Intel Core Ultra 9 275HX.

Two honest caveats before the numbers. At 10 and 100 IDs everything except WhereBulkContains finishes within a millisecond or two of everything else, and BenchmarkDotNet warned that the iteration times were too small to be precise, so read those rows as “all the same” rather than as a ranking. Variance in the 1,000 to 2,099 band was also high on a laptop-class LocalDB instance, so treat the shape of the curve as the finding, not the third significant figure.

The two failures in that last column are worth reading carefully. Neither one fails fast. EF.Constant spent 20.5 seconds on the query before SQL Server gave up on it, and WhereContains spent 19.8, both holding a connection the whole time. A fast exception is a bug report. Twenty seconds of a held connection under load is an outage.

Four things fall out of that table.

The default is at its worst just below the ceiling. At 2,098 IDs, EF.Parameter finished in 4.3 ms against the default’s 34.6 ms. That’s a one-word change to your query for an 8x result.

Chunking is the popular answer and the weakest one. It was never fastest at any size, and at 100,000 IDs it took 1.35 seconds against 187 ms for a temp table, while allocating over five times more. Splitting one query into fifty means fifty round trips and fifty result sets to stitch together in memory.

EF.Constant stops scaling hard. It was competitive to about 2,098, then collapsed: 102 ms at 5,000, and at 100,000 SQL Server refused the query with “The query processor ran out of internal resources and could not produce a query plan.” That’s a different failure from the parameter error. It comes from the sheer size of the SQL text, not the parameter count, which is also why no parameter setting saves you from it.

No temp table beat the built-ins until 100,000. At 5,000 the plain default was the fastest thing on the board. At 10,000 EF.Parameter edged past it, 13.5 ms to 14.8 ms, and both were about seven times faster than any temp-table approach. Only at 100,000 did a hand-rolled temp table take the lead.

The plan cache, measured

Plan cache pollution gets repeated a lot in discussions of this topic, so I measured it instead of arguing about it. Twenty queries with twenty different list sizes, from a cleared cache:

Inlining produces a new plan for every distinct list. Padding is doing real work in the default column: twenty different list lengths collapsed into four plans. That’s the strongest argument against reaching for EF.Constant as a general fix, and the reason it belongs only on short, stable sets of values.

Choosing a Translation Mode

EF Core 10 gives you three strategies. Globally:

optionsBuilder.UseSqlServer(connectionString, sql =>
    sql.UseParameterizedCollectionMode(ParameterTranslationMode.Parameter));

Or per query, which is where I’d start:

// One scalar parameter per value. The EF Core 10 default.
.Where(p => EF.MultipleParameters(ids).Contains(p.Id))

// One JSON array parameter, unpacked with OPENJSON. The EF Core 8 and 9 default.
.Where(p => EF.Parameter(ids).Contains(p.Id))

// Values inlined into the SQL text. The pre-EF Core 8 default.
.Where(p => EF.Constant(ids).Contains(p.Id))

EF.Constant earns its place when the set is short and stable, such as a handful of role names or status codes. Inlining lets the planner see the actual values and pick a better plan, and because the set rarely changes you’re not churning the cache.

One EF Core 10 detail that will confuse you at a debugger: inlined constants are now redacted from logs. The database receives IN (1, 333334, 666667) while your log shows IN (?, ?, ?), unless you turn on EnableSensitiveDataLogging(). That’s a deliberate security improvement, and it’s very easy to misread as EF sending the wrong SQL.

A trap if you test this yourself

EF caches compiled queries per model. If you probe several global UseParameterizedCollectionMode settings inside one process, you’ll get the first mode’s SQL for every later run and conclude the setting does nothing. I hit exactly that while building the sample, which is why the probe runs each global mode in its own child process. Per-query EF.Parameter and EF.Constant don’t have the problem, because they change the expression tree and so get their own cache entry.

When the Built-Ins Run Out

Translation modes only help while the thing you’re filtering by is a list of scalars. They cannot express:

• Composite keys. A list of (TenantId, ProductId) pairs has no IN form. This one has its own section below, because it fails in more interesting ways than the other two.
• A list of objects. Filtering by properties of in-memory entities rather than a single column.
• Unbounded input where you’d rather join server-side than ship the list at all.

The general answer to all three is to stop passing the list as a predicate and start treating it as a table: stage the values, then INNER JOIN. By hand, that is a temp table plus SqlBulkCopy:

await using var connection = new SqlConnection(connectionString);
await connection.OpenAsync();

await using (var create = connection.CreateCommand())
{
    create.CommandText = "CREATE TABLE #Ids ([Value] int NOT NULL PRIMARY KEY);";
    await create.ExecuteNonQueryAsync();
}

using var bulk = new SqlBulkCopy(connection) { DestinationTableName = "#Ids" };
bulk.ColumnMappings.Add("Value", "Value");
await bulk.WriteToServerAsync(idTable);

await using var select = connection.CreateCommand();
select.CommandText =
    "SELECT p.[Id] FROM [Products] p INNER JOIN #Ids i ON i.[Value] = p.[Id];";

That won my 100,000-ID benchmark at 187 ms. It also costs you the EF query pipeline: no LINQ composition, no projections, no navigation properties, and a connection you now manage by hand.

Entity Framework Extensions packages the same idea behind WhereBulkContains, which stays inside IQueryable so the rest of your query keeps composing. One package, and its major version tracks your EF Core major version, so an EF Core 10 project takes the 10.x line:

dotnet add package Z.EntityFramework.Extensions.EFCore --version 10.105.8

That is the entire setup. Nothing to register in Program.cs, and no using to add either, because the package declares WhereBulkContains in the global namespace. It shows up on IQueryable<T> as soon as the restore finishes:

var matched = await context.Products
    .AsNoTracking()
    .WhereBulkContains(ids)
    .Select(p => new { p.Id, p.Sku })
    .ToListAsync();

It creates a temporary table, fills it with BulkInsert, and joins. In my numbers it matched the default at 100,000 IDs, 270 ms against 273 ms, and carried real overhead at small sizes: 5.4 ms at 10 IDs, where everything else finished under 2 ms. Their own docs are upfront about that, so I’ll quote them rather than paraphrase:

“In most scenarios, the answer will probably be no. The Contains method is faster due to simply using a very basic IN (...) statement. The WhereBulkContains method is also very fast, but the main advantage is its flexibility by supporting: an unlimited amount of items, any kind of list, custom key/composite key.”

That matches my numbers, and it puts the case for the library where it belongs: capability, not speed. There’s also no connection management to hand-roll, which the temp-table sample above should make you appreciate. Before you adopt it, know the edges. SQL Server and PostgreSQL only. No ExecuteUpdate or ExecuteDelete, though the same library offers UpdateFromQuery and DeleteFromQuery for that job. No TPH, TPT or TPC inheritance mapping. And it’s commercially licensed, with a trial you can re-download each month, so check current terms before you build on it.

There’s also a free option in the same family: WhereContains from Entity Framework Plus. No license, no trial clock, and for the small and medium lists it’s built for it’s a clean one-liner that picks a sensible translation for you instead of making you choose between EF.Constant and EF.Parameter by hand. In my table it tracks the built-ins closely all the way to 2,099 IDs. Just know what it is doing underneath, because the name suggests a bulk path and the free one isn’t. It picks between a Contains expression, an Any expression, or the paid bulk path from Entity Framework Extensions, and that last one is off by default and needs the license anyway. So on a free project you land on one of the built-in forms and inherit their limits.

Installing it is the same one-liner, and the version lines up with the commercial package:

dotnet add package Z.EntityFramework.Plus.EFCore --version 10.105.8

This one does need a using, and it takes an optional key selector for when you are filtering on something other than the primary key:

using Z.EntityFramework.Plus;

var matched = await context.Products
    .AsNoTracking()
    .WhereContains(ids, p => p.Id)
    .Select(p => new { p.Id, p.Sku })
    .ToListAsync();

One detail worth knowing if the project has to stay license-free: Z.EntityFramework.Plus.EFCore takes an exact dependency on Z.EntityFramework.Extensions.EFCore, so installing the free package restores the commercial assembly alongside it. WhereContains is compiled into that assembly rather than into the free one. Nothing is unlocked by this, the paid paths still ask for a license, but both DLLs land in your output folder.

There’s a second default worth knowing about. Past ContainsMaxItemsForParameters, which is 200, it stops parameterizing and inlines the values straight into the SQL text, which is exactly what EF.Constant does. My benchmark tracks that theory closely: WhereContains shadowed EF.Constant from 5,000 IDs onward, then took 19.8 seconds at 100,000 before SQL Server refused the query with the same query-processor error. Its own docs describe it as designed for small and medium lists, and that’s a fair, accurate description. It just isn’t a way around the ceiling.

So where does the handover happen? Entity Framework Plus will do it for you, but only if you ask: UseWhereBulkSmart is off by default, and once you turn it on it hands off to WhereBulkContains at MinValuesForWhereBulk, which defaults to 4,000 - counted in values, not items, so a composite key burns through it faster than the list length suggests. My numbers land in roughly the same place. WhereContains is free and perfectly fine up to a couple of thousand IDs, starts paying the inlining tax past that, and is the wrong tool well before 100,000.

When the Key Is Two Columns

Everything so far assumed you’re filtering by a list of single values. Change that to a list of composite keys and the story isn’t “slower”. It’s that none of the built-in options can express the query at all.

The setup is an Inventory table keyed on (TenantId, ProductId), one million rows, and a list of pairs to fetch:

modelBuilder.Entity<InventoryItem>(item =>
{
    item.ToTable("Inventory");
    item.HasKey(i => new { i.TenantId, i.ProductId });
});

The three shapes you’d reach for first all fail on EF Core 10:

// none of these translate
.Where(i => keys.Contains(new ValueTuple<int, int>(i.TenantId, i.ProductId)))
.Where(i => keys.Contains(new { i.TenantId, i.ProductId }))
.Where(i => keys.Any(k => k.TenantId == i.TenantId && k.ProductId == i.ProductId))

All three throw the same thing:

The LINQ expression … could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to ‘AsEnumerable’, ‘AsAsyncEnumerable’, ‘ToList’, or ‘ToListAsync’.

This isn’t a size problem. It fails with two pairs exactly the way it fails with two thousand. SQL Server has no IN form that takes a pair of columns, and EF has nothing to translate a tuple Contains into, so the query never reaches the database.

The OR chain dies before the parameter wall

The usual workaround is to build the predicate yourself: one AND clause per pair, all of them OR’d together. That’s what a foreach loop produces, and what the PredicateBuilder helpers produce:

WHERE ([i].[TenantId] = @p0 AND [i].[ProductId] = @p1)
   OR ([i].[TenantId] = @p2 AND [i].[ProductId] = @p3)
   OR ...

It works, and at 100 pairs it runs in 2.1 ms, level with everything else on the board. Then it stops working in a way I haven’t seen written up anywhere: at 490 pairs the process dies. Not the query. The process.

The exit code is 0xC00000FD, STATUS_STACK_OVERFLOW. A stack overflow can’t be caught in .NET, so there’s no exception to log, no catch that helps, and nothing in your telemetry except a worker that vanished. I bisected it, and the edge is sharp: 489 pairs runs fine, 490 takes the process down.

The cause is the shape of the tree, not the number of parameters. A loop that keeps OR-ing onto an accumulator builds a left-deep expression tree 490 levels deep, and EF walks it recursively while translating. 490 pairs is 980 parameters, less than half the budget. The parameter limit never gets a look in.

Combine the same clauses as a balanced tree and the depth problem goes away:

// merge pairs of clauses until one is left: depth log2(n) instead of n
while (clauses.Count > 1)
{
    var merged = new List<Expression>((clauses.Count + 1) / 2);
    for (var i = 0; i < clauses.Count; i += 2)
    {
        merged.Add(i + 1 < clauses.Count
            ? Expression.OrElse(clauses[i], clauses[i + 1])
            : clauses[i]);
    }

    clauses = merged;
}

Now it survives to 1,049 pairs, and then throws this:

The incoming request has too many parameters. The server supports a maximum of 2100 parameters. Reduce the number of parameters and resend the request.

That’s the error from the top of this article, and composite keys are the one place in EF Core 10 where I could still make it fire. It fires because an OR chain isn’t a parameterized collection. There’s no array for EF to notice and re-translate, just 2,098 individual parameters that happen to be arranged in a tree. The OPENJSON fallback that quietly rescues a scalar Contains has nothing to hook into. Two parameters per pair, so the ceiling lands at 1,049 pairs rather than 2,098 values.

That’s worth sitting with, because it reverses the main finding of this article. For a list of scalars, EF Core 10 saves you and charges you in latency. For a list of composite keys, it doesn’t save you, and the 2,100 error is real again.

There’s a second surprise in the OR chain, and it arrives long before either ceiling. At 400 pairs the parameterized version took 5.3 seconds. The identical chain with the values inlined took 2.9 ms. Same rows, same predicate, about 1,800 times the difference.

Given real values, SQL Server can expand an OR list into a set of index seeks. Given 800 parameters it doesn’t, and falls back to scanning the table and evaluating 400 OR’d comparisons against every row. So the OR chain isn’t only fragile past a few hundred pairs. It’s also slow well below that unless you inline the values, and inlining is exactly what leaves a fresh plan in the cache for every distinct list, which is the trade EF.Constant already lost earlier in this article.

One caveat on 489. It’s a stack depth limit, so it moves with stack size, build configuration and runtime version, and your number won’t be exactly mine. Don’t treat it as a documented boundary. Treat it as “a few hundred”, and as a good reason to stop building predicates in a loop.

The string key trap

The other common workaround is to glue the key parts into one string so you can go back to a normal Contains:

var keys = pairs.Select(p => p.TenantId + "-" + p.ProductId).ToList();

var matched = await context.Inventory
    .Where(i => keys.Contains(i.TenantId + "-" + i.ProductId))
    .ToListAsync();

This one translates. That’s the trap. It produces exactly what you asked for:

WHERE CAST([i].[TenantId] AS nvarchar(max)) + N'-' + CAST([i].[ProductId] AS nvarchar(max))
      IN (@keys1, @keys2, ...)

Both key columns go through a CAST before the comparison, so the primary key index is useless and SQL Server scans all one million rows, evaluating a concatenation per row. At 100, 400 and 1,000 pairs it didn’t finish at all: every run hit the default 30 second command timeout. The OR chain answered the same question in 2.1 ms.

Then it gets strange. At 5,000 pairs it completed in 1.3 seconds, and at 100,000 pairs in 1.9 seconds. The string list is subject to the same rule as any other collection, so once it crosses 2,098 values EF drops it into a single JSON parameter and the IN becomes a subquery over OPENJSON. One scan with a hash match beats one scan with thousands of per-row string comparisons. So this approach is at its worst on the small lists it looks safest on, and it is never the right answer.

What actually works

The answer is the same one from the scalar case, and for composite keys it stops being an optimization and becomes the only option left: stop passing the keys as a predicate and stage them as a table. By hand, that’s a two-column temp table and a join on both columns:

using (var create = connection.CreateCommand())
{
    create.CommandText =
        "CREATE TABLE #Keys ([TenantId] int NOT NULL, [ProductId] int NOT NULL, " +
        "PRIMARY KEY ([TenantId], [ProductId]));";
    create.ExecuteNonQuery();
}

// SqlBulkCopy the pairs into #Keys, then:
select.CommandText =
    "SELECT i.[TenantId], i.[ProductId] FROM [Inventory] i " +
    "INNER JOIN #Keys k ON k.[TenantId] = i.[TenantId] AND k.[ProductId] = i.[ProductId];";

No parameters, no tree depth, no ceiling. You’re back to managing a connection by hand and you’ve left the EF query pipeline behind, which is the same trade as before.

WhereBulkContains does this without leaving IQueryable, and here it needs no key selector at all, because it reads the composite key off your model:

var wanted = pairs
    .Select(p => new InventoryItem { TenantId = p.TenantId, ProductId = p.ProductId })
    .ToList();

var matched = await context.Inventory
    .AsNoTracking()
    .WhereBulkContains(wanted)
    .ToListAsync();

It stages a temp table, then joins with an EXISTS against a CTE. Zero parameters on the command, so none of this section’s ceilings apply to it.

Here are the numbers on the same one-million-row table, filtering by pairs. Same harness and the same caveat as before: at 100 pairs everything except WhereBulkContains lands within a few hundred microseconds of everything else, so read that column as “all the same” rather than as a ranking.

crash means the process died with a stack overflow, so there’s no exception and no result. timeout means the query was still running when the 30 second command timeout expired. Both are real outcomes of running this code, not gaps in the harness.

Two things stand out. The hand-rolled temp table wins every column from 400 pairs onward, which is much earlier than in the scalar benchmark, where nothing beat the built-ins until 100,000 IDs. That’s the point: with composite keys there are no built-ins to beat. And WhereBulkContains is the only row that returns a correct answer at every size, at 1.5x to 2.4x the cost of doing the same staging by hand.

One last note on the free method, because I benchmarked it rather than taking the name at face value. WhereContains does accept a composite key, and it resolves it to an inlined OR chain, which means it inherits that approach’s ceiling exactly: it died at the same few hundred pairs, with the same stack overflow. It’s a genuine convenience below that, and it is not an answer for composite keys at scale. WhereBulkContains is the one that holds up here, and that gap between the free method and the paid one is wider on composite keys than anywhere else in this article.

Staying Out of Trouble

Three habits keep this from surprising you later.

Log the parameter count, not the list length. A DbCommandInterceptor reading command.Parameters.Count is about ten lines of code and tells you the only number that matters.

Alert on query duration bucketed by list size, not on error rate. On this code path EF has already swallowed the error for you, so duration is the only signal left.

Pin your EF Core patch version in benchmarks and write it into the results. The behavior described here moved twice between 10.0.0 and 10.0.2, and it will move again.

Decision Matrix

My Take

The advice I’d give a teammate is simple: leave Contains alone until a query tells you otherwise, and stop treating 2,100 as a planning number. Padding, joins, filters and pagination values all draw from the same budget, so the only count that matters is the one on the command, and a DbCommandInterceptor will show it to you in about ten lines of code.

The caution I’d attach is about the silence. An exception is a good failure: it points at a line and stops. What EF Core 10 does here is quieter and, on a bad day, worse. A query that’s been fine for months sits at 1,800 IDs on the slow side of the cliff, and nothing in your code, your logs, or your exception tracker says so. Then the list grows past 2,099 in production and the query gets faster, which is not a sentence anyone expects to debug. Watch query duration by list size, not just error rates, because on this code path the errors are the part that already got handled for you.

The one thing I wouldn’t do is reach for a bulk library at 5,000 IDs because an article said Contains breaks at 2,100. In my benchmarks the built-ins beat every temp-table approach at both 5,000 and 10,000. Reach for a temp table when you cross into six figures, or when you need to express something IN simply cannot.

It’s also worth asking why the list is that long in the first place. A request that arrives with 50,000 IDs is often a paging problem wearing a filter costume, and pagination, sorting and searching solves it without the list ever reaching the database. When the same list genuinely does get queried over and over, second-level caching removes the round trip instead of optimizing it.

Summary

EF Core 10 handles large lists a lot better than its reputation suggests. Contains won’t throw at you, which is the good news and also the problem, because the expensive band sits just below a ceiling you can’t see. Learn the shape: default under a thousand, EF.Parameter from a thousand to the ceiling, default again above it, EF.Constant only for short stable sets, and a staged temp table when the list reaches six figures or stops being a list of scalars at all.

Clone the sample repo, point the connection string at your own database, and run the probe first. Seeing your own parameter counts and your own generated SQL is worth more than any table in this article.

If you’re working through EF Core more broadly, this one sits in the query-performance corner of the same map as concurrency control, and the EF Core interview questions roundup is a fast way to find the gaps you haven’t hit yet.

If this saved you a debugging session, share it with your team. And if you’ve hit this ceiling in production, I’d like to hear what it looked like from your side - drop a comment below.

Happy Coding :)