Using SQL LIKE operator in Entity Framework

Like clause is most useful free text search in SQL Server. Currently Entity Framework doesn't have support for like operator. There are alternative ways, you can use StartWith, Contains or EndWith operators to build your query.

In this article I am going to show you how to use SQL LIKE operator using SqlFunctions.PatIndex. It will return the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types. You cannot call this function directly. This function can only appear within a LINQ to Entities query.

Syntax
PATINDEX ( '%pattern%' , expression )
Pattern
A character expression that contains the sequence to be found. Wildcard characters can be used; however, the % character must come before and follow pattern (except when you search for first or last characters).
Expression
A column that is searched for the specified pattern.
How to Use
var employeeList = 
    Employees
        .Where(e => SqlFunctions.PatIndex("%b%", e.Name) > 0)
        .ToList();
SqlFunctions.PatIndex function will filter employees with a B in the name.

References

Comments

Popular posts from this blog

12 opensource and free asp.net MVC gridview contols

Scheduling background jobs using Hangfire in ASP.NET Core

Difference ExecuteNonQuery vs ExecuteScalar vs ExecuteReader in asp.net