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

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

Answer 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.

Answer 4

You can do all these statements with LINQ like this

string _search = "johnson";
// joh* OR joh%
items.Where(i => i.Name.StartsWith(_search, StringComparison.OrdinalIgnoreCase));
// *son OR %son
items.Where(i => i.Name.EndsWith(_search, StringComparison.OrdinalIgnoreCase));
// *hns* OR %hns%
items.Where(i => i.Name.ToLower().Contains(_search));

Answer 5

the solution is to use SQLFunctions.PatIndex

var result = from c in items
             where SqlFunctions.PatIndex(searchstring.ToLower(), c.fieldtoSearch) > 0
             select c;

where 'searchstring' is the pattern to search 'fieldtoSearch' is the field to search

Patindex() supports search using string pattern search. The search is case insensitive.

Answer 6

Now, EF supports "LIKE" usage and you can use all sql wildcards. Check this out.

var people = from t in entities.People
             select new { t.Name };
people = people.Where(x => DbFunctions.Like(x.Name, searchTerm));

Answer 7

You can do this:

using System.Data.Entity;  // EntityFramework.dll v4.3
var queryResult=db.Accounts.AsQueryable().Where(x => x.Name.Contains(queryKey));

because Linq to Entity can't transform the method Contains() to the SQL, but Linq to SQL can do this. I tried to find a method that can doing a cast, at last, AsQueryable(), also a generic version AsQueryable<T>(). I found I can do this using it this way in my case, but any side effect it has I don't know, maybe it will lose some feature at Entity.

Answer 8

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 9

It is easily achieved by following methods

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

Use the following specifications to achieve wildcards

LIKE 'a%' => StartsWith("a")
LIKE '%a' => EndsWith("a")
LIKE '%a%' => Contains("a")
LIKE 'a%b' => StartsWith("a") && EndsWith("b")
LIKE '%a%b%' => StartsWith("a") && Contains("b")

Answer 10

You do not need to use percent sign while filtering. e.g;

if I want to check ItemName does not contain '-' I will do it like this

!Item.ItemName.Contains("-")

In SQL it will convert to NOT LIKE '%-%'

Answer 11

We use Database First and the EntityFramework.

The "Map your own function." approach works for us together with the nuget EntityFramework.CodeFirstStoreFunctions.

1 Step: Create a function in the db like this:

CREATE FUNCTION [dbo].[StringLike]
(
      @a nvarchar(4000),
      @b nvarchar(4000)
)
RETURNS bit
AS
BEGIN
    RETURN 
    (SELECT CASE
            WHEN (SELECT 1 WHERE @a LIKE @b) = 1 THEN 1
            ELSE 0
            END)  
END

2 Step: Install nuget EntityFramework.CodeFirstStoreFunctions

3 Step: Create a method in your code like this (I create mine in the DbContext class):

[DbFunction("CodeFirstDatabaseSchema", "StringLike")]
public static bool Like(string input, string pattern)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

4 Step: Initalize EntityFramework.CodeFirstStoreFunctions.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Conventions.Add(new FunctionsConvention("dbo", this.GetType()));
}

5 Step: Now you can use this method in your linq query.