How to use SQL 'LIKE' with LINQ to Entities? [duplicate]

Question

I have a textbox that allows a user to specify a search string, including wild cards, for example:

Joh* *Johnson *mit* *ack*on 

Before using LINQ to Entities, I had a stored procedure which took that string as parameter and did:

SELECT * FROM Table WHERE Name LIKE @searchTerm 

And then I would just do a String.Replace('*', '%') before passing it in.

Now with LINQ to Entities I am trying to accomplish the same thing. I know there is StartsWith, EndsWith and Contains support, but it won't support it in the way that I need.

I read about "SqlMethods.Like" and tried this:

var people = from t in entities.People              where SqlMethods.Like(t.Name, searchTerm)              select new { t.Name }; 

However I am getting the following exception:

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. 

How would I get this same functionality using LINQ to Entities?

Answer 1

http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/6529a35b-6629-44fb-8ea4-3a44d232d6b9/

var people = entities.People.Where("it.Name LIKE @searchTerm", new ObjectParameter("searchTerm", searchTerm)); 

Answer 2

How to get it to work seamlessly:

in your EDMX model, add:

    <Function Name="String_Like" ReturnType="Edm.Boolean">       <Parameter Name="searchingIn" Type="Edm.String" />       <Parameter Name="lookingFor" Type="Edm.String" />       <DefiningExpression>         searchingIn LIKE lookingFor       </DefiningExpression>     </Function> 

just after the sections that start:

<edmx:ConceptualModels> <Schema Namespace="Your.Namespace"...

Then, anywhere in your code, add this extension method:

    //prior to EF 6 [System.Data.Objects.DataClasses.EdmFunction("Your.Namespace", "String_Like")]      //With EF 6     [System.Data.Entity.DbFunction("Your.Namespace", "String_Like")]     public static bool Like(this string input, string pattern)     {         /* Turn "off" all regular expression related syntax in          * the pattern string. */         pattern = Regex.Escape(pattern);          /* Replace the SQL LIKE wildcard metacharacters with the          * equivalent regular expression metacharacters. */         pattern = pattern.Replace("%", ".*?").Replace("_", ".");          /* The previous call to Regex.Escape actually turned off          * too many metacharacters, i.e. those which are recognized by          * both the regular expression engine and the SQL LIKE          * statement ([...] and [^...]). Those metacharacters have          * to be manually unescaped here. */         pattern = pattern.Replace(@"\[", "[").Replace(@"\]", "]").Replace(@"\^", "^");          return Regex.IsMatch(input, pattern, RegexOptions.IgnoreCase);     } 

And there you have it.

Now you can do:

(from e in Entities  where e.Name like '%dfghj%'  select e) 

or

string [] test = {"Sydney", "Melbourne", "adelaide", "ryde"};  test.Where(t=> t.Like("%yd%e%")).Dump();