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

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

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

Solution 3:

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.