EF Core - Creating queries with lists

When writing queries towards the database at some point the need arises to filter out all items that are within a collection. For example let’s say we have a list of Ids and want to get all the people associated with such an Id. How can we do this using EF Core? And what are the pitfalls that lead to the motivation of writing this blogpost? 🙈

If we had a list of attendee Ids with three Ids (123, 456, 789) and would want to find all the people having such an Id, the following SQL query (for an MS SQL DB) would yield the right result:

SELECT * FROM People WHERE AttendeeId IN (123, 456, 789)

Now don’t hung up on the SELECT * in fact don’t use it in production code. But the point of the SQL sample above is that with IN we can filter for a list of Arguments. If we want EF Core to generate this we have to write the following LINQ statement:

List<Person> attendees = await _context.People.Where(p => attendeeIds.Contains(p.AttendeeId)).ToListAsync();

If we inspect this query by using the ToQueryString() we see that this statement is converted into a SQL statement.

SELECT [p].[PersonId], [p].[AttendeeId], [p].[Name]
FROM [People] AS [p]
WHERE [p].[AttendeeId] IN (1, 2, 3)

As the query above shows, using attendeeIds.Contains(p => p.AttendeeId) is correctly translated into an SQL query using an IN statement.

Update: Erik has brought to my attention, that there are limits to this approach. If you have a large set of Ids this approach will fall short. For example the number of parameters for a SQL Server Query is limited to 2100 parameters per call. If you have larger sets of Ids be sure to check out this Gist and if you are using SQL Server 2016 or later check out this Post. Thanks Erik for pointing this out. 🙂

Potential Pitfalls

Client Evaluation: There are other ways in LINQ how we could write this filter, and EF Core might even go ahead and execute it but it would most probably be executed by the application. In general executing queries in the application is not desired. SQL database engines are way more efficient in handling these queries. Be sure to consult the MS Learn on Client Evaluation to get a better understanding.

Not using List: I have a strong tendency to write functional inspired C#. So I very rarely pass a list into a function and prefer to pass in an IReadOnlyCollection . For the above statement to be generated by EF Core you must use a collection of type List or else you could end up with a runtime exception.

Wrap up

Implementing a Filter using a list with EF Core is thanks to LINQ pretty straight forward. There are some pitfalls though which have to be kept in mind. Since EF Core has to generate an SQL Query from the LINQ statement there can be cases that the LINQ query will not be translated into a SQL. When this happens EF Core will fallback to Client Evaluation, which Filtering Data in a SQL Table using EF Core takes some inside knowledge but is not complicated.

HTH