facebook

Entity Framework Extensions How to filter entities contained from an existing list with Entity Framework?

Description

Filtering entities by including items from an existing list is a common scenario.

For example, you want to retrieve customers to update properties you retrived for a list of Customer that you have previously deserialized from a JSON file.

A frequent solution is using the Contains methods such as:

var customerIds = deserializedCustomers.Select(x => x.CustomerID).ToList();
var customers = context.Customers.Where(x => customerIds.Contains(x.CustomerID)).ToList();

It works great and is easy to use.

However, this solution has some limitations such as:

  • It only supports basic types like int or guid
  • The list of ids is limited due to SQL limitations
  • It doesn't support surrogate key (more than one key) or other complex scenarios

The WhereBulkContains method lets you filter a query by including all entities from the list. It's doesn't have any of the Contains method limitations.

FAQ

How to use the method WhereBulkContains?

The most basic scenario is passing a DTO list to the WhereBulkContains method.

This method will filter entities to include those contained in the DTO list.

// Use the entity type key if none is provided (CustomerID)
var customers = context.Customers.WhereBulkContains(deserializedCustomers).ToList();

//  Allow specifying a custom join with one or many properties.
var customers = context.Customers.WhereBulkContains(deserializedCustomers, x => x.Code).ToList();

What kind of list is supported?

All kinds of lists are supported. The only requirement is that your list is a basic type or must contain 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
// example coming soon

Can I use WhereBulkContains with millions of items?

Yes, you can use the WhereBulkContains method with an unlimited amount of items.

Under the hood, we create a temporary table and populated it with our very fast BulkInsert method. Then we use this temporary table to perform an INNER JOIN statement.

How to join different properties than the entity key?

By default, we create the join using the entity key, but you can choose one or many properties by passing:

  • A lambda expression
  • A list of string
// example coming soon

Can I use WhereBulkContains after a Where

Yes, the WhereBulkContains is like any other LINQ method. You can chain any linq method, even a Where with a contains method.

// example coming soon

Can I use WhereBulkContains with UpdateFromQuery and DeleteFromQuery?

The WhereBulkContains method is compatible with some of our other methods such as:

// example coming soon

Do WhereBulkContains faster than Contains method?

In most scenarios, the answer will probably be no. The Contains method is faster due to simply using a very fastIN (...) statement.

The WhereBulkContains method is also very fast, but the main advantage is his flexibility by supporting multiple scenarios that the Contains method doesn't support.

What are the limitations?

We currently only support SQL Server.