Linq and entity framework: Chaining where clauses to produce OR query. Part 2

1. Generic approach to generating LINQ OR statements

In the previous post Linq and entity framework OR chaining I've given a quick overview of how can we have a quick and dirty SQL translatable solution to filtering IQueryable collection using a list of strings.

I'd like to take this idea further and expand on it, by proposing a generic approach. This is how it's going to work:

var filterList = new List<string> { "Name1", "Name3" };
var resultQuery = queryableContainers.WhereOr(
    (container, filterItem) => container.InnerContainer.Name.Contains(filterItem),
    filterList);

It will query each item in the entity collection against each item in passed list. The resulting SQL will be containing a WHERE clause with multiple OR connected conditions (as many as there are items in the filter list.

Here's there WhereOr method:

/// <summary>
///     WhereOr extension allows to chain together predicates.
///     Against a common collection with OR operator.
/// </summary>
/// <typeparam name="T">Queried item.</typeparam>
/// <typeparam name="TFilter">Filter item type.</typeparam>        
/// <param name="filterPredicate">Predicate, which will return true or false.
/// Resulting from an operation between T and TFilter parameters.</param>
/// <param name="filterItems">Filter items collection to WHERE against. 
/// The number of OR predicate calls will be dependant on the number of filter items.</param>
public static IQueryable<T> WhereOr<T, TFilter>(
    this IQueryable<T> self,            
    Expression<Func<T, TFilter, bool>> filterPredicate,
    IEnumerable<TFilter> filterItems)
{            
    var orChainedWhereClause = self.WhereItemFiltered(
        filterPredicate,
        filterItems.First());

    foreach (var filterItem in filterItems.Skip(1))
    {
        orChainedWhereClause = orChainedWhereClause.Or(
            self.WhereItemFiltered(
                filterPredicate,
                filterItem));
    };

    return self.Where(orChainedWhereClause);
}

The method does pretty much the same, what we've done in the example from the previous post. The only difference is that the condition can be passed from the outside - the rest is generic.

First WhereItemFiltered call produces the initial expression, which the will be joined with the use of Or against other expressions based on the filterItems collection.

To complete the walkthrough, this is the WhereItemFiltered method, which creates an expression, which allows to pass two parameters into the lambda. One is the item of the queryable collection the other is parameter passed using the filter collection.

public static Expression<Func<T, bool>> WhereItemFiltered<T, TFilter>(
    this IEnumerable<T> self,
    Expression<Func<T, TFilter, bool>> filterPredicate,
    TFilter filterItem)
{
    var queryableItemParamExpr = Expression.Parameter(typeof(T));
    var filterItemConstExpr = Expression.Constant(filterItem, typeof(TFilter));
    var predicateInvokeExpr = Expression.Invoke(filterPredicate, queryableItemParamExpr, filterItemConstExpr);
    return Expression.Lambda<Func<T, bool>>(predicateInvokeExpr, queryableItemParamExpr);
}

Here is the generated SQL:

SELECT [c].[Id], [c].[InnerContainerId]
FROM [Containers] AS [c]
LEFT JOIN [InnerContainer] AS [c.InnerContainer] ON [c].[InnerContainerId] = [c.InnerContainer].[Id]
WHERE (CHARINDEX(N'Name1', [c.InnerContainer].[Name]) > 0) 
OR (CHARINDEX(N'Name3', [c.InnerContainer].[Name]) > 0)

As you can see it's a little bit different from our previous example. That's because in the previous post we've used EndsWith and not Contains. We can use any predicate, which is translatable to SQL.

Potential issues, which I see with the approach:

• Complex objects can't be used in the filter item collection. An approach like this:
  
  

  var filterObjList = new List<FilterObj> {
      new FilterObj { Name = "Name1" },
      new FilterObj { Name = "Name2" }
  };            
  var resultQuery = queryableContainers.WhereOr(
      (container, filterItem) => container.InnerContainer.Name.Contains(filterItem.Name),
      filterObjList);
  var result = resultQuery.ToList();
  

  
  Will cause a WARNING, stating that the Where part of the query will be executed locally (in memory). To make it work we'd need a rewrite of the item filtering expression. 

• As mentioned in the previous post - filter item collection should not contain too many items.