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

> Since EF Core 8, Contains on SQL Server usually sends the list as one JSON parameter. WhereBulkContains joins a temp table instead, for large or composite keys.

- Author: Pavle Davitkovic
- Published: 2026-01-22
- Updated: 2026-08-25
- Topic: Databases
- Canonical: https://pavle.codes/blog/where-bulk-contains/

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. Measure the crossover point with your own provider and workload.

## The scenario

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

```csharp
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`:

```sql
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](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-8.0/breaking-changes#contains-in-linq-queries-may-stop-working-on-older-sql-server-versions) and the [EF Core 9 controls for collection parameterization](https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-9.0/whatsnew#force-or-prevent-query-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.

The provider matters as much as the version. The same `Contains` call against [a PostgreSQL jsonb column](https://pavle.codes/blog/jsonb-in-postgresql/#querying) translates to a containment operator rather than an `IN` list.

## 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`](https://entityframework-extensions.net/where-bulk-contains?utm_source=pavlecodes&utm_medium=blog&utm_campaign=january-2026) is part of **Z.EntityFramework.Extensions**. I cover the broader library in [Introduction to Entity Framework Extensions](https://pavle.codes/blog/ef-extensions/).

```csharp
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:

```csharp
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:

```sql
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:

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

It also remains composable as `IQueryable<T>`:

```csharp
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:

1. EF Core and database-provider versions.
2. Database version and compatibility level.
3. Collection sizes that reflect production, including duplicate values.
4. Generated SQL and parameter count. A [`DbCommandInterceptor`](https://pavle.codes/blog/ef-core-interceptors-database-commands/) can capture both.
5. Actual execution plan and logical reads.
6. End-to-end elapsed time, not just server execution time.
7. Allocations and result materialization cost.
8. 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](https://entityframework-extensions.net/licensing?utm_source=pavlecodes&utm_medium=blog&utm_campaign=september-2025).
