Dynamically build a "or" LIKE query in LINQ to SQL

Question

I have a LINQ query that consists of an anonymous object.

At this point, I want to limit the results to incoming search parameters, but it can be one or more parameters, and I want to use "LIKE x OR LIKE y OR LIKE z" using them.

In code, it will 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 can I build this dynamically, knowing that reservations is of type IQueryable<'a> (anonymous object) ? I was browsing through various resources, and I can only find a way to do this when I know the type, and not using anonymous types.

It is important to know that this is Linq for SQL, so you need to translate it into an SQL query and not filter in memory ...

Answer 1

Two ways are possible:

• Building Expression as indicated by Coincoin

• Entering all 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 general 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 use:

 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 the general solution that you would like to have - if it exists, but I hope that this can be an acceptable alternative that solves your case by dynamically creating the desired filter.

Answer 3

I found a solution after some debugging, but I create a WhereFilter with several 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 as follows:

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

I checked the query trace string and actually translated to SQL, so filtering is done in the database.