How to Use SQL LIKE Operator With LINQ in C#

In this article, we will learn how to use the SQL LIKE operator with LINQ in C#.

Let’s start.

Sample Application

We will start with a .NET 7 console application, and install the Entity Framework Core (EF Core) library.  If you are unfamiliar with EF Core, please first refer to the Entity Framework Core Series.

Setup Logging

In order for us to see the exact query generated by EF Core, we have to configure logging to the application. So, let’s prepare a local database with EF Core and set up a logging method inside the Program.cs class:

var contextOption = new DbContextOptionsBuilder<BlogDbContext>()
    .UseSqlServer(sqlConnectionString)
    .LogTo(Console.WriteLine, new[] { DbLoggerCategory.Database.Name }, LogLevel.Information)
    .Options;

var blogDbContext = new BlogDbContext(contextOption);

We specify the Console.WriteLine as an output method and the DbLoggerCategory.Database with Information level as the logging scope in the LogTo method. By doing this, we can see the actual T-SQL statements in the console.

Use the Contains Method to Generate SQL Like Operator in a Query

EF Core translates the Contains method into a LIKE operator. So, let’s see an example of that:

List<Article> articles = blogDbContext.Articles.Where(x => x.Title.Contains("n")).ToList();

We use the Contains method as a predicate that indicates the condition. When we execute the application, we can find the T-SQL statement in the log:

SELECT [a].[Id], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'%n%'

As we can see, using the Contains method inside the Where method enables us to generate the SQL LIKE operator with the '%n%' search team.

Of course, we can use other LINQ methods with the Contains method as a parameter to generate the same LIKE queries.

Let’s see how to do that with the Count method:

int articleCounts = blogDbContext.Articles.Count(x => x.Title.Contains("n"));

After we run our sample application, we can see the translated query in the console window:

SELECT COUNT(*)
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'%n%'

Or the FirstOrDefault method:

Article? firstArticle = blogDbContext.Articles.FirstOrDefault(x => x.Title.Contains("n"));

This also generates a similar query with a LIKE operator:

SELECT TOP(1) [a].[Id], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'%n%'

These methods, like many other LINQ methods, will generate the SQL query with a LIKE operator.

Use the StartsWith Method to Generate SQL Like Operator in a Query

If we want to check whether a sequence starts with a certain term, we can use the StartsWith method in C#. EF Core translates the StartsWith method into a LIKE operator as a forward match.

So, let’s see an example of that:

articles = blogDbContext.Articles.Where(x => x.Title.StartsWith("n")).ToList();

Now, we can start the application and inspect the result in the console window:

SELECT [a].[Id], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'n%'

We can see the LIKE operator in the WHERE clause with the 'n%' search term.

Use the EndsWith Method to Generate SQL Like Operator in a Query

Likewise, if we want to check whether a sequence ends with a certain term, we can use the EndsWith method.

Let’s see another example that uses the EndsWith method:

articles = blogDbContext.Articles.Where(x => x.Title.EndsWith("n")).ToList();

Once again, we can run our application and inspect the generated query:

SELECT [a].[Id], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'%n'

As we can see, LINQ translates the EndsWith method into a query that uses the LIKE operator with the '%n' search term.

Use EF.Functions.Like Method for Other Wildcard Character

So far, we learned how to translate the LINQ query into the LIKE operator with the % wildcard. But, SQL Server supports other wildcard characters such as _ , [], and [^] for more complex pattern matchings. To use these wildcard characters, we need to use the Like method in the EF.Functions namespace. But why can’t we use the previous methods?

Let’s examine it first:

articles = blogDbContext.Articles.Where(x => x.Title.StartsWith("_n")).ToList();

We specify the "_n" as the pattern with the StartsWith method by assuming it generates the LIKE operator with the '_n%' pattern.

Let’s run the application and inspect the generated query:

SELECT [a].[Id], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'\_n%' ESCAPE N'\'

As a result, it escapes the _ wildcard, which is not what we want here.

Instead, let’s use the EF.Functions.Like method:

articles = blogDbContext.Articles.Where(x => EF.Functions.Like(x.Title, "_n%")).ToList();

After we run the application again, let’s inspect the output:

SELECT [a].[Id], [a].[Title]
FROM [Articles] AS [a]
WHERE [a].[Title] LIKE N'_n%'

It doesn’t escape the wildcard this time. We can use the all supported wildcard characters by using the EF.Functions.Like method. One caveat is if we switch the provider, it may behave differently as that provider may not understand the pattern exactly the same as the SQL Server.

Conclusion

In this article, we’ve learned how to use the SQL LIKE operator in the C# LINQ. We learned about different ways to generate the LIKE operator with LINQ methods and similar translated queries those methods produce.