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($"{e.Id} - {e.FirstName} {e.LastName}");
}Code language: C# (cs)

Output:

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($"{e.Id} - {e.FirstName} {e.LastName}");
}Code language: C# (cs)

Output:

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

• Use LINQ Contains() method to form the IN operator in the WHERE clause.
• Use the logical negation operator (!) to form the NOT IN operator.