Dynamically construct "or" LIKE query in LINQ to SQL

Question

I have a LINQ query which is composed by an anonymous object.

At a given point, I want to limit the results by incoming search parameters, but this can be one or more parameters, and I want to perform a "LIKE x OR LIKE y OR LIKE z" using those.

In code, it would look like this:

reservations = reservations.Where(r =>
  r.GuestLastName.Contains(parameter1) || r.GuestFirstName.Contains(parameter1) || 
  r.GuestLastName.Contains(parameter2) || r.GuestFirstName.Contains(parameter2) || 
  // Parameter 3, 4, 5,..
);

How could I construct this dynamically, knowing that reservations is of the type IQueryable<'a> (anonymous object)? I've looked around on various resources and I can only seem to find a way to do it when I know the type, not when using anonymous types.

It's important to know that it's Linq to SQL, so it should be translated to an SQL query and not be filtered in memory...

score 2 · Answer 1 · answered Nov 27 '12 at 15:32

There are two possible ways:

Building an Expression, as pointed out by Coincoin

Putting all your parameters into an array and using Any:

var parameters = new [] { parameter1, parameter2, /*...*/ }
reservations = reservations
    .Where(r => 
        parameters.Any(p => r.GuestFirstName.Contains(p)
                            || r.GuestLastName.Contains(p)));

jwaliszko · Answer 2 · 2012-11-27T20:49:36.710

I would write my own generic extension method:

public static class CollectionHelper
{
    public static IQueryable Filter<T>(this IQueryable source, string[] properties, string[] values)
    {
        var lambda = CombineLambdas<T>(properties, values);
        var result = typeof (Queryable).GetMethods().First(
            method => method.Name == "Where"
                      && method.IsGenericMethodDefinition)
                                       .MakeGenericMethod(typeof (T))
                                       .Invoke(null, new object[] {source, lambda});
        return (IQueryable<T>) result;
    }

    // combine lambda expressions using OR operator
    private static LambdaExpression CombineLambdas<T>(string[] properties, string[] values)
    {
        var param = Expression.Parameter(typeof (T));
        LambdaExpression prev = null;
        foreach (var value in values)
        {
            foreach (var property in properties)
            {
                LambdaExpression current = GetContainsExpression<T>(property, value);
                if (prev != null)
                {
                    Expression body = Expression.Or(Expression.Invoke(prev, param),
                                                    Expression.Invoke(current, param));
                    prev = Expression.Lambda(body, param);
                }
                prev = prev ?? current;
            }
        }
        return prev;
    }

    // construct expression tree to represent String.Contains
    private static Expression<Func<T, bool>> GetContainsExpression<T>(string propertyName, string propertyValue)
    {
        var parameterExp = Expression.Parameter(typeof (T), "type");
        var propertyExp = Expression.Property(parameterExp, propertyName);
        var method = typeof (string).GetMethod("Contains", new[] {typeof (string)});
        var someValue = Expression.Constant(propertyValue, typeof (string));
        var containsMethodExp = Expression.Call(propertyExp, method, someValue);

        return Expression.Lambda<Func<T, bool>>(containsMethodExp, parameterExp);
    }
}

and the usage:

var reservations = new List<TheType>()  // sample collection
    {
        new TheType {FirstName = "aa", LastName = "bb"},
        new TheType {FirstName = "cc", LastName = "dd"},
        new TheType {FirstName = "ee", LastName = "ff"}
    }.AsQueryable();

var filtered = reservations
    .Filter<TheType>(new[] {"FirstName", "LastName"}, new[] {"d", "e"});
/* returnes 2 elements:
 * {FirstName = "cc", LastName = "dd"} and {FirstName = "ee", LastName = "ff"} */

I don't know a general solution you'd like to have - if exists any, but I hope it can be acceptable alternative which solves your case by building desired filter dynamically.

score 0 · Answer 3 · answered Nov 28 '12 at 10:36

I found the solution after some debugging, but I create a WhereFilter with multiple selectors, one for FirstName and one for LastName..

This is the extension method:

public static IQueryable<T> WhereFilter<T>(this IQueryable<T> source, string[] possibleValues, params Expression<Func<T, string>>[] selectors)
{
    List<Expression> expressions = new List<Expression>();

    var param = Expression.Parameter(typeof(T), "p");

    var bodies = new List<MemberExpression>();
    foreach (var s in selectors)
    {
        bodies.Add(Expression.Property(param, ((MemberExpression)s.Body).Member.Name));
    }

    foreach (var v in possibleValues)
    {
        foreach(var b in bodies) {
            expressions.Add(Expression.Call(b, "Contains", null, Expression.Constant(v)));
        }
    }

    var finalExpression = expressions.Aggregate((accumulate, equal) => Expression.Or(accumulate, equal));

    return source.Where(Expression.Lambda<Func<T, bool>>(finalExpression, param));
}

It can be used like this:

reservations = reservations.WhereFilter(
    array_of_allowed_values,
    r => r.GuestFirstName,
    r => r.GuestLastName
);

I checked the trace string of the query and it actually translated to SQL, so the filtering is performed at the database.

This solution requires from you passing lambda expressions to the function signature. As far as I understood your question, your requirement was to build them dynamically. As an example consider, that you have an action invoked as result of a GET request in the WEB environment. In query parameters the properties names to be filtered are send to the server, along with their possible values. In your case, you are forced to construct lambda expressions based on the properties names which you've received from the client, instead of just passing them further to the filter function. — jwaliszko, Nov 28 '12 at 11:25

Dynamically construct "or" LIKE query in LINQ to SQL

3 Answers3