How to use SQL 'LIKE' with LINQ to Entities?

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

var people = from t in entities.People
                 where t.Name.ToLower().Contains(searchTerm.ToLower())
                 select new { t.Name };

EDIT- I might be mixing syntax. I usually use extension methods; but contains will work.

Answer 2

Well, your choices are:

• Use Contains. I know you don't like it, but it could probably be made to work.
• Pick a function from SqlFunctions. They're all supported in L2E.
• Map your own function.
• +1 to @Yury for ESQL.

Answer 3

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));