When working with Entity Framework Core, developers often need to retrieve multiple entities from the database based on a collection of IDs or values.

The typical approach is to use the Contains method:

csharp
1var productIds = GetProductIdsForSync();
2
3var products = await dbContext.Products
4    .Where(p => productIds.Contains(p.Id))
5    .ToListAsync();

This approach works fine for small collections, but it creates significant problems when dealing with large datasets:

• Performance degradation - Even with proper indexes, the SQL WHERE IN clause becomes slower as the number of parameters increases.
• Parameter limit exceeded - SQL Server has a hard limit of 2,100 parameters per query. While you can work around this by splitting queries into batches in SQL, this is not always practical when using EF Core, especially in complex scenarios with additional filters and joins.
• Memory and connection issues - Multiple round-trips to the database consume more memory and keep database connections open longer, potentially causing connection pool exhaustion.

This becomes a real problem in scenarios like synchronizing product catalogs, processing bulk orders, or updating inventory across thousands of items.

There is a better solution: Entity Framework Extensions library provides specialized methods for bulk data retrieval that solve all these problems.

In this post, you will explore 5 methods that allow you to efficiently retrieve large datasets without hitting parameter limits or sacrificing performance.

Let's dive in.

All examples in this post were tested on the SQL Server database.

The Contains method in EF Core translates to a SQL WHERE IN clause.

When you pass a collection of 10,000 product IDs, EF Core generates a query with 10,000 parameters:

csharp
1var productIds = await GetProductIdsFromExternalSystem();
2
3var products = await dbContext.Products
4    .Where(p => productIds.Contains(p.Id))
5    .ToListAsync();

csharp
1SELECT * FROM Products
2WHERE Id IN (@p0, @p1, @p2, ... @p9999)

This approach has critical limitations: SQL Server Parameter Limit - SQL Server allows a maximum of 2,100 parameters in a single query. If you try to query more than 2,100 products, EF Core will throw an exception:

1System.Data.SqlClient.SqlException: The incoming request has too many parameters. 
2The server supports a maximum of 2100 parameters.

As a workaround, you could split the query into batches:

csharp
1var batchSize = 2000;
2var allProducts = new List<Product>();
3
4for (int i = 0; i < productIds.Count; i += batchSize)
5{
6    var batch = productIds.Skip(i).Take(batchSize).ToList();
7    var products = await dbContext.Products
8        .Where(p => batch.Contains(p.Id))
9        .ToListAsync();
10    
11    allProducts.AddRange(products);
12}

However, this solution creates multiple round-trips to the database, keeps connections open longer, and makes it difficult to apply additional filters or includes across all batches.

In SQL, you could use a temporary table or table-valued parameter to work around the limit. Still, these approaches require raw SQL and lose the benefits of EF Core's strongly-typed queries, change tracking, and navigation property support.

For product synchronization scenarios where you need to retrieve 50,000 products from your database to compare with an external system, this becomes a significant bottleneck.

Entity Framework Extensions is a library that extends EF Core with high-performance bulk operations. While it's well-known for bulk inserts, updates, and deletes, it also provides powerful methods for bulk data retrieval.

To get started with Entity Framework Extensions, install the following NuGet package:

bash
1dotnet add package Z.EntityFramework.Extensions.EFCore

The library provides specialized methods that use temporary tables internally to bypass the parameter limit and improve performance. Instead of passing thousands of parameters in a WHERE IN clause, these methods:

• Create a temporary table in the database
• Insert your filter values into that temporary table
• Join your entity table with the temporary table
• Return the filtered results
• Clean up the temporary table automatically

This approach significantly improves query performance for large datasets.

The Entity Framework Extensions library offers five main methods for bulk data retrieval:

• WhereBulkContains - filter entities with a LINQ query using all items from an existing list. Only the database items that match the items in your list will be returned.
• WhereBulkNotContains - filter entities with a LINQ query by excluding all items from an existing list.
• BulkRead - filter entities with LINQ query by including all items from an existing list and then return the result immediately
• WhereBulkContainsFilterList - returns items from the list that already exist in the database.
• WhereBulkNotContainsFilterList - returns items from the list that don't exist in the database. It's the inverse of the WhereBulkContainsFilterList method.

Let's explore these methods in more detail.

The WhereBulkContains method is the most commonly used bulk retrieval method. It retrieves entities where a specified property matches any value in a collection.

This method is particularly useful for product synchronization scenarios where you need to retrieve thousands of products from your database to compare with data from an external system, API, or file import.

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var productIds = await GetProductIdsFromExternalSystem();
5
6var products = await dbContext.Products
7    .WhereBulkContains(productIds)
8    .ToListAsync();

The method automatically uses the entity's primary key to match items. In this example, it joins on Id.

You can also filter by specifying any property as the 2nd parameter:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var products = await GetProductsFromExternalSystem();
5
6var products = await dbContext.Products
7    .WhereBulkContains(products, x => x.Id)
8    .ToListAsync();

The first parameter is the collection of values to filter by, and the second parameter is a lambda expression that specifies which property to match.

All kinds of lists are supported. The only requirement is that your list is a basic type or contains a property with the same name as the key:

• Basic type such as List<int> and List<Guid>
• Entity Type such as List<Customer>
• Anonymous Type
• Expando Object list

Filtering by Non-Primary Key Properties:

You can use WhereBulkContains with any property, not just the primary key:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var productCodes = new List<string> 
5{ 
6    "SKU-001", "SKU-002", "SKU-003", // ...
7};
8
9var products = await dbContext.Products
10    .WhereBulkContains(productCodes, x => x.ProductCode)
11    .ToListAsync();

You can filter by multiple properties simultaneously:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var products = context.Products
5    .WhereBulkContains(deserializedProducts, x => new { x.SupplierCode, x.ProductCode })
6    .ToListAsync();

WhereBulkContains returns IQueryable<T> that can be chained with other LINQ methods like Where, Select, and Include:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var productIds = GetProductIdsForSync();
5
6var activeProducts = await dbContext.Products
7    .Where(p => p.IsActive && p.Stock > 0)
8    .WhereBulkContains(productIds, x => x.Id)
9    .Select(p => new
10    {
11        p.Id,
12        p.Name,
13        p.Price,
14        CategoryName = p.Category.Name
15    })
16    .ToListAsync();

This is much more flexible than splitting queries into batches, where applying additional filters and includes across batches becomes complex and error-prone.

The WhereBulkNotContains method is the opposite of WhereBulkContains. It retrieves entities where a specified property does not match any value in the collection.

This method is useful when you need to find entities that are missing from an external system or when you want to exclude a large set of items from your query results.

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var discontinuedProductIds = await GetDiscontinuedProductIdsFromExternalSystem();
5
6var activeProducts = await dbContext.Products
7    .WhereBulkNotContains(discontinuedProductIds, x => x.Id)
8    .ToListAsync();

This retrieves all products except those in the discontinuedProductIds collection, without hitting the parameter limit.

Like WhereBulkContains, you can chain this method with other LINQ operations:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var processedProductIds = await GetProcessedProductIds();
5
6var remainingProducts = await dbContext.Products
7    .Where(p => p.RequiresProcessing)
8    .WhereBulkNotContains(processedProductIds, x => x.Id)
9    .OrderBy(p => p.Priority)
10    .Take(1000)
11    .ToListAsync();

The BulkRead method is a convenience method that combines WhereBulkContains with immediate execution.

It retrieves entities from the database that match items in your list and returns the results immediately as a List<T>.

It works the same as WhereBulkContains but doesn't require you to chain with other methods to return the results:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var deserializedProducts = DeserializeProductsFromJson(); // Returns 10,000 products
5
6// Retrieve matching products from the database immediately
7var products = context.Products.BulkRead(deserializedProducts);

The difference is that BulkRead returns results immediately, while WhereBulkContains returns an IQueryable<T> that you can chain with additional LINQ operations.

The async version is also available:

csharp
1var deserializedProducts = DeserializeProductsFromJson();
2var products = await context.Products.BulkReadAsync(deserializedProducts);

The WhereBulkContainsFilterList method filters your input list by returning only the items that already exist in the database.

This is different from WhereBulkContains, which returns entities from the database. This method returns items from your input list (not database entities).

This method is particularly useful when you need to split your data into existing and non-existent items, such as separating records that need to be inserted from those that need to be updated.

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var deserializedProducts = DeserializeProductsFromJson(); // Returns 20,000 products
5
6// Returns items from deserializedProducts that exist in the database
7var existingProducts = context.Products
8    .WhereBulkContainsFilterList(deserializedProducts)
9    .ToList();

The method returns items from your input list (not database entities) that match rows in the database.

You can specify which property to use for matching:

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var existingProducts = context.Products
5    .WhereBulkContainsFilterList(deserializedProducts, x => x.ProductCode)
6    .ToList();
7
8// Or use a string
9var existingProducts = context.Products
10    .WhereBulkContainsFilterList(deserializedProducts, "ProductCode")
11    .ToList();
12
13// Or use a list of strings
14var existingProducts = context.Products
15    .WhereBulkContainsFilterList(deserializedProducts, new List<string> { "ProductCode" })
16    .ToList();

This method supports filtering by multiple properties.

The WhereBulkNotContainsFilterList method is the inverse of WhereBulkContainsFilterList. It filters your input list by returning only the items that don't exist in the database.

This method returns items from your input list (not database entities).

This method is useful when you want to identify new records that need to be inserted or when you need to find items from your import that are missing from the database.

csharp
1// @nuget: Z.EntityFramework.Extensions.EFCore
2using Z.EntityFramework.Extensions;
3
4var deserializedProducts = DeserializeProductsFromJson(); // Returns 20,000 products
5
6// Returns items from deserializedProducts that don't exist in the database
7var notExistingProducts = context.Products
8    .WhereBulkNotContainsFilterList(deserializedProducts)
9    .ToList();

The method returns items from your input list (not database entities) that don't match rows in the database.

You can specify which property to use for matching.

This method supports filtering by multiple properties.

When to Use BulkRead vs WhereBulkContains:

Use BulkRead when:

• You want immediate results as a List<T>
• You don't need to apply additional LINQ operations
• You're working with deserialized data that needs to be matched against the database

Use WhereBulkContains when:

• You need to chain additional LINQ operations like Where, Select, or Include
• You want to build a query before executing it
• You need the flexibility of IQueryable<T>

When to Use WhereBulkContainsFilterList vs WhereBulkContains:

• Use WhereBulkContainsFilterList when you want to filter your list.
• Use WhereBulkContains when you want to retrieve database entities.

When to Use WhereBulkNotContainsFilterList vs WhereBulkNotContains:

• Use WhereBulkNotContainsFilterList when you want to filter your list
• Use WhereBulkNotContains when you want to retrieve database entities.

Entity Framework Extensions is a commercial library with a free trial, which you can use to see if it's right for your project.

The library requires a license for production use. See more information on licensing here.

For commercial use, the time saved in development and the improved application performance typically pay for the license within the first month of use. Try the free trial to see how these methods can transform your bulk data operations from a performance bottleneck into one of the fastest parts of your application.

Many thanks to ZZZ Projects for sponsoring this blog post.

Hope you find this newsletter useful. See you next time.