EF Core Where

Summary: in this tutorial, you’ll learn how to use the EF Core Where() method to filter data based on one or more conditions.

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

Using the Where() method with the equal operator

The following example uses the LINQ Where() method to find employees whose first name is "Alexander":

using static System.Console;
using HR;


using var context = new HRContext();


var employees = context.Employees
                .Where(e => e.FirstName == "Alexander")
                .ToList();

foreach (var e in employees)
{
    WriteLine($"{e.FirstName} {e.LastName}");
}Code language: C# (cs)

Output:

Alexander Young
Alexander GreenCode language: C# (cs)

In this example, EF Core generates an SQL that uses a WHERE clause to find employees whose first name is "Alexander":

SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] = N 'Alexander'
Code language: C# (cs)

Note that EF Core doesn’t parameterize the query and passes the literal string "Alexander" to the SQL. It is vulnerable to SQL injection attacks.

However, if you use a variable instead of a literal string, EF Core will parameterize the query which can protect the application from SQL injections. For example:

using static System.Console;
using HR;


using var context = new HRContext();

var firstName = "Alexander";
var employees = context.Employees
                .Where(e => e.FirstName == firstName)
                .ToList();

foreach (var e in employees)
{
    WriteLine($"{e.FirstName} {e.LastName}");
}Code language: C# (cs)

In this example, EF Core parameterizes the query as follows:

-- [Parameters=[@__firstName_0='Alexander' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] = @__firstName_0Code language: C# (cs)

Using AND operator

To query data using multiple conditions and combine the condition using the AND operator, you use the && in the Where() method.

For example, the following uses the Where() method to find employees whose first name is Alexander and the last name is Young:

using static System.Console;
using HR;


using var context = new HRContext();

var firstName = "Alexander";
var lastName = "Young";

var employees = context.Employees
                .Where(e => e.FirstName == firstName
                        && e.LastName == lastName)
                .ToList();

foreach (var e in employees)
{
    WriteLine($"{e.FirstName} {e.LastName}");
}Code language: C# (cs)

Output:

Alexander YoungCode language: C# (cs)

EF Core generates an SQL that uses a WHERE clause with an AND operator:

-- [Parameters=[@__firstName_0='Alexander' (Size = 4000), @__lastName_1='Young' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] = @__firstName_0 
  AND [e].[LastName] = @__lastName_1Code language: C# (cs)

The following example uses the && operator to find employees who join the company between 3/1/2023 and 3/31/2023:

using HR;


using var context = new HRContext();


var startDate = new DateTime(2023, 3, 1);
var endDate = new DateTime(2023, 3, 31);

var employees = context.Employees
                        .Where(e => e.JoinedDate >= startDate && e.JoinedDate <= endDate)
                        .ToList();

foreach (var e in employees)
{
    Console.WriteLine($"{e.FirstName} {e.LastName} - {e.JoinedDate.ToShortDateString()}");
}Code language: JavaScript (javascript)

Output:

Michael Johnson - 3/5/2023
Alexander Young - 3/18/2023
Sofia Thomas - 3/17/2023
Chloe Young - 3/5/2023
Levi Gonzalez - 3/24/2023

EF Core generates the following SQL statement:

-- [Parameters=[@__startDate_0='2023-03-01T00:00:00.0000000', @__endDate_1='2023-03-31T00:00:00.0000000'], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[JoinedDate] >= @__startDate_0 
  AND [e].[JoinedDate] <= @__endDate_1
Code language: PHP (php)

Using OR operator

To query data using multiple conditions and combine them using the OR operator, you use the || in the Where() method.

using static System.Console;
using HR;


using var context = new HRContext();

var firstName = "Emily";
var lastName = "Brown";

var employees = context.Employees
                    .Where(e => e.FirstName == firstName || e.LastName == lastName)
                    .ToList();

foreach (var e in employees)
{
    WriteLine($"{e.FirstName} {e.LastName}");
}Code language: C# (cs)

Output:

Emily Brown
Emily Harris
Ethan BrownCode language: C# (cs)

EF Core generates an SQL that uses a WHERE clause with an OR operator:

-- [Parameters=[@__firstName_0='Emily' (Size = 4000), @__lastName_1='Brown' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[LastName], 
  [e].[Salary] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] = @__firstName_0 
  OR [e].[LastName] = @__lastName_1Code language: C# (cs)

Summary

• Use LINQ Where() method to filter data.
• Use && to form an AND operator.
• Use || to form an OR operator.