WhereBulkContains vs Where in EF Core

When working with Entity Framework Core, filtering entities using .Where(x => ids.Contains(x.Id)) is the natural starting point. It is built in, readable, and often the right choice.

The generated SQL is not universal, though. It depends on the EF Core version, database provider, compatibility settings, and query configuration. Large collections can still produce an inefficient plan or an expensive payload, but it is incorrect to assume that every value always becomes a separate SQL parameter.

This article compares modern EF Core collection translation with WhereBulkContains, a commercial extension that loads filter values into a temporary table and joins against it. Treat the crossover point as something to measure in your own provider and workload, not as a fixed list-size rule.

The scenario

Suppose a synchronization job receives 10,000 product IDs from a third-party API and needs the matching database rows:

var productIds = await GetProductIdsFromCsv();

var products = await dbContext.Products
    .Where(product => productIds.Contains(product.Id))
    .ToListAsync();

The query may be completely adequate. Before replacing it, inspect the generated SQL with ToQueryString(), capture the actual execution plan, and benchmark a representative list size.

How EF Core translates Contains

EF Core’s translation changed over time. Before EF Core 8, the SQL Server provider commonly inserted collection values as constants in the IN list. Starting with EF Core 8, a typical SQL Server translation passes the collection as JSON and expands it with OPENJSON:

SELECT [p].[Id], [p].[Name], [p].[Price]
FROM [Products] AS [p]
WHERE [p].[Id] IN (
    SELECT [i].[value]
    FROM OPENJSON(@__productIds_0)
         WITH ([value] int '$') AS [i]
);

Microsoft documents both the EF Core 8 SQL Server change and the EF Core 9 controls for collection parameterization. EF Core 9 parameterizes primitive collections by default, while configuration and per-query APIs can force constant translation.

That means SQL Server’s 2,100-parameter limit is relevant only when the generated command actually contains that many scalar parameters. It is not a universal consequence of LINQ Contains.

What can still go wrong with a large collection

Using one collection parameter does not make list size irrelevant. Large collections can still expose tradeoffs:

Payload size: thousands of values still have to travel to the database, even when carried in one JSON parameter.
Cardinality estimation: the optimizer may estimate a parameterized collection poorly and choose the wrong join strategy.
Compatibility: the EF Core 8 OPENJSON translation requires a compatible SQL Server version and database compatibility level.
Query shape: composite-key matching is not expressed by a simple primitive Contains call.
Provider differences: PostgreSQL, SQLite, SQL Server, and third-party providers do not have to translate the same LINQ expression identically.

Use EF logging, ToQueryString(), the actual execution plan, and a benchmark that includes network and materialization cost. Do not infer the SQL shape from the LINQ alone.

The WhereBulkContains approach

WhereBulkContains is part of Z.EntityFramework.Extensions. I cover the broader library in Introduction to Entity Framework Extensions.

using Z.EntityFramework.Extensions;

var products = await dbContext.Products
    .WhereBulkContains(productIds)
    .ToListAsync();

The default join uses the entity key. You can also select another key:

var incomingProducts = await GetProductsFromCsv();

var products = await dbContext.Products
    .WhereBulkContains(incomingProducts, product => product.Sku)
    .ToListAsync();

According to the library’s documentation, the extension creates a temporary table, populates it through its bulk-insert path, and performs an INNER JOIN. That avoids expanding the filter into one scalar SQL parameter per item and supports more complex key shapes.

Conceptually, the read query looks like this:

SELECT p.*
FROM Products AS p
INNER JOIN #TempFilter AS f ON p.Id = f.Value;

The exact temporary table, bulk-copy operation, and SQL depend on the extension and provider versions. Inspect the actual commands in your application rather than treating the example as guaranteed output.

Composite keys and composed queries

The extension accepts a key with multiple properties:

var products = await dbContext.Products
    .WhereBulkContains(productKeys, product => new
    {
        product.WarehouseId,
        product.ProductCode
    })
    .ToListAsync();

It also remains composable as IQueryable<T>:

var products = await dbContext.Products
    .WhereBulkContains(productIds, product => product.Id)
    .Where(product => product.Category == category)
    .Where(product => product.Price >= minimumPrice)
    .Where(product => product.IsActive && !product.IsDiscontinued)
    .Include(product => product.Supplier)
    .Select(product => new
    {
        product.Id,
        product.Name,
        Supplier = product.Supplier.Name
    })
    .ToListAsync();

The final read query includes the additional predicates, projection, and join. The extension separately handles temporary-table population and cleanup, so “one LINQ query” does not mean “one database operation.”

Contains vs. WhereBulkContains

Consideration	`Where` + `Contains`	`WhereBulkContains`
SQL strategy	Provider/version-dependent; SQL Server EF Core 8+ commonly uses a collection parameter with `OPENJSON`	Temporary table + `JOIN`, per vendor documentation
Scalar parameter limit	Relevant only if the selected translation expands values into scalar parameters	Does not require one scalar parameter per item
Small lists	Usually the simplest baseline; measure it	Temporary-table setup may cost more than it saves
Large lists	May suffer from payload or plan-estimation issues	Designed for large filter sets; verify with your workload
Composite keys	Requires a different query shape	Built-in custom/composite key support
Plan reuse	Modern collection parameterization can keep SQL text stable	Fixed temporary-table naming is designed to encourage reuse
Diagnostics	Inspect `ToQueryString()` and the actual plan	Inspect extension logs, generated SQL, and the actual plan
Dependency	Native EF Core	Commercial library with a free trial

This table describes query shapes, not benchmark results. There is no honest universal threshold such as 100 or 1,000 values because network latency, row width, indexes, data distribution, provider version, and database configuration all affect the result.

A benchmark checklist

Test the two approaches with the same data and context lifetime. Record:

EF Core and database-provider versions.
Database version and compatibility level.
Collection sizes that reflect production, including duplicate values.
Generated SQL and parameter count.
Actual execution plan and logical reads.
End-to-end elapsed time, not just server execution time.
Allocations and result materialization cost.
Cold and warm plan-cache behavior.

Benchmark the failure modes as well as the happy path. A technique that is faster at 10,000 IDs may add needless setup at 20 IDs.

When not to use WhereBulkContains

The native query already meets the latency and resource budget.
Collections are small and temporary-table setup dominates the request.
The application cannot accept a commercial dependency.
The provider/version combination has not been validated.
A permanent staging table or a different data-flow design better fits the workload.

Summary

Start with native .Contains() and inspect the translation produced by your EF Core version and provider. Modern SQL Server translations do not necessarily create one scalar parameter per list item, so the 2,100-parameter limit is not an automatic failure mode.

Large collections can still create payload and plan-quality problems. WhereBulkContains trades a commercial dependency and temporary-table setup for a relational join strategy and composite-key support. Benchmark both with production-like values before choosing.

For licensing terms, see the licensing page.

Where vs. WhereBulkContains: What's the real difference in EF Core?