Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Linq SqlMethods.Like fails

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.

like image 718
Russell Steen Avatar asked Apr 06 '10 12:04

Russell Steen


1 Answers

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

like image 119
Thomas Levesque Avatar answered Nov 07 '22 23:11

Thomas Levesque