Dynamic LINQ Like

问题:

How to write a dynamic linq method for Like clause.

For reference, there is Dynamic LINQ OrderBy on IEnumerable<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>:

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?

回答1:

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

回答2:

Are you aware of SqlMethods.Like ?

回答3:

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