How to use SQL 'LIKE' with LINQ to Entities?
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));
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();