Dynamic LINQ Like

Question

How to write a dynamic LINQ method for Like clause.

For reference, there is Dynamic LINQ OrderBy on IEnumerable<T> / IQueryable<T>. I am looking for a similar one for dynamic Like clause.

I have the following extension methods for like:

public static IQueryable<T> Like<T>(this IQueryable<T> source, string propertyName, 
                                    string keyword)
{
    var type = typeof(T);
    var property = type.GetProperty(propertyName);
    var parameter = Expression.Parameter(type, "p");
    var propertyAccess = Expression.MakeMemberAccess(parameter, property);
    var constant = Expression.Constant("%" + keyword + "%");
    var methodExp = Expression.Call(
        null, 
        typeof(SqlMethods).GetMethod("Like", new[] { typeof(string), typeof(string) }),
        propertyAccess, 
        constant);
    var lambda = Expression.Lambda<Func<T, bool>>(methodExp, parameter);
    return source.Where(lambda);
}

The above method gives an error

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

The other method which is somehow modified from Dynamic LINQ OrderBy on IEnumerable<T> / IQueryable<T>:

public static IQueryable<T> ALike<T>(this IQueryable<T> source, string property, 
                                     string keyword)
{
    string[] props = property.Split('.');
    Type type = typeof(T);
    ParameterExpression arg = Expression.Parameter(type, "x");
    Expression expr = arg;

    foreach (string prop in props)
    {
        // use reflection (not ComponentModel) to mirror LINQ
        PropertyInfo pi = type.GetProperty(prop);
        expr = Expression.Property(expr, pi);
        type = pi.PropertyType;
    }
    var constant = Expression.Constant("%" + keyword + "%");
    var methodExp = Expression.Call(
        null, 
        typeof(SqlMethods).GetMethod("Like", new[] { typeof(string), typeof(string) }), 
        expr, 
        constant);
    Type delegateType = typeof(Func<,>).MakeGenericType(typeof(T), type);
    LambdaExpression lambda = Expression.Lambda(delegateType, methodExp, arg);
    object result = typeof(Queryable).GetMethods().Single(
            method => method.IsGenericMethodDefinition
                    && method.GetGenericArguments().Length == 2
                    && method.GetParameters().Length == 2)
            .MakeGenericMethod(typeof(T), type)
            .Invoke(null, new object[] { source, lambda });
    return (IQueryable<T>)result;
}

The above method gives an error:

Expression of type 'System.Boolean' cannot be used for return type 'System.String'

Any ideas on this?

http://stackoverflow.com/a/2456070/1193727 – resnyanskiy Feb 17 '12 at 04:47 — resnyanskiy, Feb 17 '12 at 04:47

Marc Gravell · Answer 1 · 2009-11-02T07:11:11.930

Something like:

static void Main() {
    using(var ctx= new DataClasses1DataContext()) {
        ctx.Log = Console.Out;
        var qry = ctx.Customers.WhereLike("CompanyName", "a%s");

        Console.WriteLine(qry.Count());
    }
}
static IQueryable<T> WhereLike<T>(this IQueryable<T> source,
        string propertyOrFieldName, string pattern) {
    var param = Expression.Parameter(typeof(T), "row");
    var body = Expression.Call(
        null,
        typeof(SqlMethods).GetMethod("Like",
            new[] { typeof(string), typeof(string) }),
        Expression.PropertyOrField(param, propertyOrFieldName),
        Expression.Constant(pattern, typeof(string)));
    var lambda = Expression.Lambda<Func<T, bool>>(body, param);
    return source.Where(lambda);
}
static IQueryable<T> WhereLike<T>(this IQueryable<T> source,
        string propertyOrFieldName, string pattern, char escapeCharacter) {
    var param = Expression.Parameter(typeof(T), "row");
    var body = Expression.Call(
        null,
        typeof(SqlMethods).GetMethod("Like",
            new[] { typeof(string), typeof(string), typeof(char) }),
        Expression.PropertyOrField(param, propertyOrFieldName),
        Expression.Constant(pattern, typeof(string)),
        Expression.Constant(escapeCharacter,typeof(char)));
    var lambda = Expression.Lambda<Func<T, bool>>(body, param);
    return source.Where(lambda);
}

You might also consider making it more reusable:

static void Main() {
    using(var ctx= new DataClasses1DataContext()) {
        ctx.Log = Console.Out;
        var qry1 = ctx.Customers.WhereInvoke<Customer, string>(
            "CompanyName", s => s.Contains("abc"));
        Console.WriteLine(qry1.Count());

        var qry2 = ctx.Customers.WhereInvoke<Customer, string>(
            "CompanyName", s => s.StartsWith("abc"));
        Console.WriteLine(qry2.Count());

        var qry3 = ctx.Customers.WhereInvoke<Customer, string>(
            "CompanyName", s => s.EndsWith("abc"));
        Console.WriteLine(qry3.Count());
    }
}
static IQueryable<TSource> WhereInvoke<TSource, TValue>(
        this IQueryable<TSource> source,
        string propertyOrFieldName,
        Expression<Func<TValue, bool>> func) {
    var param = Expression.Parameter(typeof(TSource), "row");
    var prop = Expression.PropertyOrField(param, propertyOrFieldName);
    if(prop.Type != typeof(TValue)) {
        throw new InvalidOperationException("The property must be " + typeof(TValue).Name);
    }
    var body = Expression.Invoke(func, prop);
    var lambda = Expression.Lambda<Func<TSource, bool>>(body, param);
    return source.Where(lambda);
}

The above methods also gives the errror: Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL. — Prasad, Nov 02 '09 at 07:20
Ah... you didn't make your source clear. There is no really convenient "LIKE" equivalent unless you reference the VB libs, and that would work against db providers - does the Contains/StartsWith/EndsWith not help any? — Marc Gravell, Nov 02 '09 at 12:29
Contains/Starts/Endwith also giving errors. You gave a real good solution for Orderby extension which i was searching for a long time. The Beauty of that code is it handles nested properties also. i am trying for similar one for 'Like'... — Prasad, Nov 02 '09 at 13:57
@MarcGravell, what can be done if I want to search a property which is not of a `string` type? Can your methods be more generic, so that they would apply to all types? — OfirD, Dec 19 '17 at 16:11
@HeyJude the code in `WhereInvoke` doesn't mention `string` at all. Did you have something specific in mind? — Marc Gravell, Dec 19 '17 at 23:21
@MarcGravell, I just reffered to your example, where you used ``. `WhereInvoke` requires to specify the type parameter (of `string` type, in this example), and what I meant to ask is if its implementation can be changed so that it supports any type *without the need to specify it* (like Dynamic Linq library enables), or is it too much out of its original purpose? — OfirD, Dec 20 '17 at 07:32
@HeyJude yes, there are ways of getting around generic type inference, but it would really really need an example of what you had in mind, I suspect. — Marc Gravell, Dec 20 '17 at 11:08

score 3 · Answer 2 · answered Oct 31 '09 at 17:25

3

Are you aware of SqlMethods.Like ?

answered Oct 31 '09 at 17:25

Amy B

100,846
20
127
174

This gives the same error mentioned above: "Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL." – Ryan Elkins Sep 01 '10 at 13:55
@Ryan Elkins, you want the functionality of "like", but do not want to call a database server? – Amy B Sep 01 '10 at 15:41

Todd · Answer 3 · 2012-03-01T01:39:17.753

0

Had the same problem as you. SqlMethods.Like only works when executing on an SQL server, not on memory collections. So I have made a Like evaluator that will work, on collections - see my blog post here

edited Mar 01 '12 at 01:39

answered Mar 01 '12 at 01:25

Todd

14,946
6
42
56

Dynamic LINQ Like

3 Answers3

Linked