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

Answer 1

There are two possible ways:

1. Building an Expression, as pointed out by Coincoin

2. 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)));
   

Answer 2

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.