EF Core Where In

Summary: in this tutorial, you will learn how to use EF Core WHERE IN to check whether a value matches a list of values.

Introduction to EF Core Where In

The SQL IN operator allows you to check if a value matches a list of values. To form a query that uses the IN operator you use LINQ Contains() method.

We’ll use the Employee entity that maps to the Employees table for the demonstration:

For example, to find employees whose id in a list of ids 1, 2, and 3, you can use the Contains() method as follows:

using Microsoft.EntityFrameworkCore; using HR; using var context = new HRContext(); int[] ids = < 1, 2, 3 >; var employees = context.Employees .Where(e => ids.Contains(e.Id)) .ToList(); foreach (var e in employees) < Console.WriteLine($" -  "); >Code language: C# (cs)
1 - John Doe 2 - Jane Smith 3 - Michael JohnsonCode language: C# (cs)

EF Core generates an SQL that uses the IN operator in the WHERE clause as follows:

SELECT [e].[Id], [e].[DepartmentId], [e].[FirstName], [e].[JoinedDate], [e].[LastName], [e].[Salary] FROM [Employees] AS [e] WHERE [e].[Id] IN (1, 2, 3)Code language: C# (cs)

NOT IN

To reverse the Contains() method, you use the logical negation operator ( ! ). In this case, EF Core will generate an SQL that uses a NOT IN operator in the WHERE clause:

using Microsoft.EntityFrameworkCore; using HR; using var context = new HRContext(); int[] ids = < 1, 2, 3 >; var employees = context.Employees .Where(e => !ids.Contains(e.Id)) .ToList(); foreach (var e in employees) < Console.WriteLine($" -  "); >Code language: C# (cs)
4 - Emily Brown 5 - William Taylor 6 - Olivia Anderson 7 - Benjamin Lee . Code language: C# (cs)

The generated SQL uses the NOT IN operator in the WHERE clause to return all rows from the Employees table where id is not in the lists 1, 2, and 3:

SELECT [e].[Id], [e].[DepartmentId], [e].[FirstName], [e].[JoinedDate], [e].[Salary], [e].[LastName] FROM [Employees] AS [e] WHERE [e].[Id] NOT IN (1, 2, 3)Code language: C# (cs)

Summary