EF Core LIKE

Summary: in this tutorial, you will learn how to query data based on pattern matching using the EF Core EF.Functions.Like() .

Introduction to the EF Core EF.Functions.Like()

In SQL, the LIKE operator allows you to search for values that match a specific pattern using wildcard characters:

• % matches zero or more characters.
• _ matches any single character.

To perform the SQL LIKE operator in EF Core, you use EF.Functions.Like() in conjunction with the LINQ Where() method.

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

Using the % wildcard character

The following example uses the EF.Functions.Like() to find employees whose first name contains the substring "ac":

using Microsoft.EntityFrameworkCore;
using HR;


using var context = new HRContext();

var keyword = "%ac%";
var employees = context.Employees
                        .Where(e => EF.Functions.Like(e.FirstName, keyword))
                        .ToList();

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

Output:

Jacob Turner
Jackson Evans
Jacob Lopez
Grace Allen
Jack WrightCode language: plaintext (plaintext)

Behind the scenes, EF Core generates an SQL that uses a WHERE clause with the LIKE operator:

-- [Parameters=[@__Format_1='%ac%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[Salary], 
  [e].[LastName] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] LIKE @__Format_1Code language: SQL (Structured Query Language) (sql)

To find employees whose first names start with the string da, you can use a wildcard character % at the end of the pattern like this:

using Microsoft.EntityFrameworkCore;
using HR;


using var context = new HRContext();


var keyword = "da%";
var employees = context.Employees
                        .Where(e => EF.Functions.Like(e.FirstName, keyword))
                        .ToList();

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

Output:

Daniel Clark
Daniel LewisCode language: plaintext (plaintext)

EF Core generates an SQL statement that uses the LIKE operator with the 'da%' as follows:

-- [Parameters=[@__Format_1='da%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[Salary], 
  [e].[LastName] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] LIKE @__Format_1Code language: SQL (Structured Query Language) (sql)

Using the _ wildcard character

To find employees whose first names match strings that start with "H", followed by any single character, followed letter n, and then followed by any string of zero or more characters, you can combine the wildcard _ with the wildcard %:

using Microsoft.EntityFrameworkCore;
using HR;


using var context = new HRContext();


var keyword = "H_n%";
var employees = context.Employees
                        .Where(e => EF.Functions.Like(e.FirstName, keyword))
                        .ToList();

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

Output:

Henry Lopez
Hannah Evans
Henry AdamsCode language: C# (cs)

EF Core generates the following SQL statement that uses a WHERE clause with the LIKE operator:

-- [Parameters=[@__keyword_1='H_n%' (Size = 4000)], CommandType='Text', CommandTimeout='30']
SELECT 
  [e].[Id], 
  [e].[DepartmentId], 
  [e].[FirstName], 
  [e].[JoinedDate], 
  [e].[Salary], 
  [e].[LastName] 
FROM 
  [Employees] AS [e] 
WHERE 
  [e].[FirstName] LIKE @__keyword_1Code language: SQL (Structured Query Language) (sql)

Summary

• Use the EF.Functions.Like() method to form a LIKE operator for query data based on pattern matching.