Linq SqlMethods.Like fails

Question

I'm following the tips here, trying to leverage the statement that the sql doesn't get created until the enumerator is tripped. However I get the following error on the code below. I'm using Linq2Entities, not linq2sql. Is there a way to do this in Linq2entities?

Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL.

            query = db.MyTables.Where(x => astringvar.Contains(x.Field1));

            if (!String.IsNullOrEmpty(typeFilter))
            {
                if (typeFilter.Contains('*'))
                {
                    typeFilter = typeFilter.Replace('*', '%');
                    query = query.Where(x=> SqlMethods.Like(x.Type, typeFilter));
                }
                else
                {
                    query  = query.Where(x => x.Type == typeFilter);
                }
            }

Notes: db is a entity mapping to a sql server.

Answer 1

I don't know how you can make Entity Framework use the "real" LIKE operator, but a possible workaround would be to express a LIKE expression in terms of StartsWith, Contains and EndsWith

For instance :

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

And so on...

Note that it isn't exactly equivalent to using LIKE in SQL : for instance LIKE '%abc%bcd%' would result in Contains("abc") && Contains("bcd"). This would match "abcd" even though the original LIKE condition wouldn't. But for most cases, it should be good enough.

Here's a sample implementation, using PredicateBuilder and LinqKit to build expressions based on a LIKE pattern :

public static class ExpressionHelper
{
    public static Expression<Func<T, bool>> StringLike<T>(Expression<Func<T, string>> selector, string pattern)
    {
        var predicate = PredicateBuilder.True<T>();
        var parts = pattern.Split('%');
        if (parts.Length == 1) // not '%' sign
        {
            predicate = predicate.And(s => selector.Compile()(s) == pattern);
        }
        else
        {
            for (int i = 0; i < parts.Length; i++)
            {
                string p = parts[i];
                if (p.Length > 0)
                {
                    if (i == 0)
                    {
                        predicate = predicate.And(s => selector.Compile()(s).StartsWith(p));
                    }
                    else if (i == parts.Length - 1)
                    {
                        predicate = predicate.And(s => selector.Compile()(s).EndsWith(p));
                    }
                    else
                    {
                        predicate = predicate.And(s => selector.Compile()(s).Contains(p));
                    }
                }
            }
        }
        return predicate;
    }
}

And here's how you could use it :

var expr = ExpressionHelper.StringLike<YourClass>(x => x.Type, typeFilter);
query = query.AsExpandable().Where(expr.Compile());

I just tried it with a simple EF model, and it seems to work fine :)

Answer 2

You could do ESQL and do something like the below..

     db.MyTables.Where("it.Type like '" + typeFilter + "'").ToList();

Answer 3

The SqlMethods class is meant to be used with LINQ-to-SQL. When you use methods from it (which the public documentation tells you not to do, it's not for public consumption), the IQueryable provider for LINQ-to-Entities doesn't know what to do with it or how to translate it.

If you had a single wildcard at the beginning or end of the filter, then you could use StartsWith or EndsWith methods on the String class, and LINQ-to-Entities will support that.

However, in this case, you have a variable number of wildcards, so you will have to drop down to the ESQL level and build a query from that, as indicated in Nix's answer.

Answer 4

You can use a real like in Link to Entities

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>

to your EDMX in this tag:

edmx:Edmx/edmx:Runtime/edmx:ConceptualModels/Schema

Also remember the namespace in the <schema namespace="" /> attribute

Then add an extension class in the above namespace:

public static class Extensions
{
    [EdmFunction("DocTrails3.Net.Database.Models", "String_Like")]
    public static Boolean Like(this String searchingIn, String lookingFor)
    {
        throw new Exception("Not implemented");
    }
}

This extension method will now map to the EDMX function.

More info here: http://jendaperl.blogspot.be/2011/02/like-in-linq-to-entities.html

Answer 5

I posted my solution here, just in case here it is too:

2019 update

For Entity Framework 6.2 you can use DBFunctions

For example:

try
{
    using (var db = new YOUREntities())
    {
        var data = db.LenderProgram.Where(i => DbFunctions.Like(i.LenderProgramCode, "OTO%"))
            .ToList();
        return data;
    }
}
catch (Exception e)
{
    e.HandleException();
}