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

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

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

Answer 2

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 another answer.

Answer 3

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