LINQ to Entities LIKE Operator

A couple of weeks ago I was asked to move some search functionality that had been prototyped in LINQ to SQL into production code for which we are using the Entity Framework and LINQ to Entities.

One of the main requirements of the search was that we have the ability to query any text column in the search table using any combination of wildcard the user liked. The LINQ to SQL prototype used the SqlMethods.Like method but as this is only available in the SQL implementation of LINQ another method was required. A search around the internet basically said that there is no LIKE operator in LINQ to Entities but the StartsWith(), EndsWith() and Contains() methods on the string class can give the functionality of '%smith', '%smith%' and 'smith%' but I couldn't find a solution that would allow us to use wildcards as per the requirements.

I eventually decided that I'd try and write an extension method on IQueryable that would provide this functionality - while looking into how to go about this I found a Where() method on System.Data.Objects.ObjectQuery which takes a parameter of string and a list of parameters. It turned out that this allowed us to provide a predicate using Entity SQL and, as ObjectQuery implements IQueryable the result can be used as part of a LINQ to Entities query as well.

So our search query could look something like this:

EntityContainer.Customers.Where("It.Surname like '%sm%i%th%' and It.Forename like 'j%o%h'");

Obviously this doesn't give us the type safety of a LINQ implementation in the predicate but is certainly useful for search functionality.