LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method

I’ve been dabbling with the ADO.NET Entity Framework in .NET 3.5, was trying to convert this SQL WHERE clause, which uses the LIKE operator, into LINQ syntax:

String strWhereClause = String.Format(CultureInfo.CurrentCulture, "Type={0} AND Name LIKE '*{0}*'", orgType, orgName);

I read somewhere which suggested to use the SqlMethods helper class. So I tried:

var objQuery = repository.Organization.Where(vendor => vendor.Type == orgType && SqlMethods.Like(vendor.Name, "”%” + orgName + “%”));

The query is targeting an Organization table. repository is an instance of an ObjectContext subclass which has been  auto generated by Visual Studio’s ADO.NET Entity Data Model wizard.  orgType and orgName are passed-in parameters.

The above approach resulted in the following runtime error (notice the redundant occurrence of the word ‘method’ in the error message):

LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression

I then saw a post on stackoverflow which suggested to use Contains(), StartsWith(), or EndsWith() to mimick the LIKE operator. So I tried:

var objQuery = repository.Organization.Where(vendor => vendor.Type == orgType && vendor.Name.Contains(orgName));

This works, but if orgName.Length is zero, then the query returns zero rows.  So finally, a slight mod to the above query achieved the desired effect:

var objQuery = repository.Organization.Where(vendor => vendor.Type == orgType && (orgName.Length == 0 || vendor.Name.Contains(orgName)));

4 comments:

th2tran said...

Trackback: http://stackoverflow.com/questions/1033007/like-operator-in-entity-framework/1161919

Thomas Hansen said...

I have found a workaround to make LINQ accept the StartsWith command..

IEnumerable query = context.PEOPLE;

query = query.Where(a => a.FULL_NAME.StartsWith(authorSearchName, StringComparison.OrdinalIgnoreCase)).Take(50).OrderBy(a => a.FULL_NAME);

The secret is NOT to work on the context.PEOPLE object directly but to create a temporary variable (query) and make the LINQ on this..

regards

Richard Rowley said...
This comment has been removed by the author.
Richard Rowley said...

Doesn't '.contains' produce a SQL 'IN'. If you had a complete value you should have never been looking for an equivalent to 'LIKE' because its inefficient. Only use 'LIKE' when you only have a partial value.