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.

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

1. Entity framework LINQ translation troubles

Another topic I wanted to write about was my effort to find a solution for chaining together LINQ's "Wheres" to produce a SQL with OR connected conditions against a list of values.

The need was caused by Entity Framework not being able to properly compile certain LINQ queries to SQL. For instance, imagine our database has a container table, each container references an inner container, and each inner container has a "Name" property. We want to get all the items, which contain a string from a list we're holding, like this:

var filterList = new List<string> { "Name1", "Name3" };
var containers = context.Containers
    .Where(container => filterList.Any(filterName => container.InnerContainer.Name.EndsWith(filterName)))
    .ToList();

If we look at the log file however, we will see the following warning:

The LINQ expression 'where {from String filterName in __filterList_0 where ([container.InnerContainer]?.Name?.EndsWith([filterName]) == True) select [filterName] => Any()}' could not be translated and will be evaluated locally.

Entity framework will fail silently, whole Containers table will be extracted from the database and the filtering will happen in memory. This can be very surprising and in case you are not checking your EF logs, you can be dumbfounded by sudden performance issues.

Now, this is not to nitpick on EF Core. Some methods obviously don't have a corresponding SQL version and can't be translated. In this case however I was pretty sure I'll get a nice SQL statement.

2. Simple non-generic solution

After a bit of digging I decided the way to go would be to use expression trees. There were many examples in stackoverflow or msdn forums, but none was, what I've exactly needed. The first simple approach allows us to chain together passed predicates using an Or LINQ extension.

public static Expression<Func<T, bool>> Or<T>(
    this Expression<Func<T, bool>> expr1, 
    Expression<Func<T, bool>> expr2)
{
    var invokedExpr = Expression.Invoke(expr2, expr1.Parameters);
    return Expression.Lambda<Func<T, bool>>
          (Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters);
}

       The extension allows us to chain two other expressions. What's important to note - passing same parameters expressions is required for the chaining to work. Think of one lambda with multiple || inside. Each condition will be using same lambda parameters passed into it.

var filterNames = new List<string> { "Name1", "Name3" };
Expression<Func<Container, bool>> whereClause = c => c.InnerContainer.Name.Contains(filterNames.First());
foreach (var filterName in filterNames.Skip(1))
{
    whereClause = whereClause.Or(c => c.InnerContainer.Name.Contains(filterName));
};  

var result = queryableContainers
    .Where(whereClause)
    .ToList();

Following SQL will be generated:

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

This is precisely what we've wanted. Unfortunately, that's quite a lot of code to write each time we want to do something like this.

It's also important to add that we should not take this approach, if we will have many of such items in the filtering collection - the SQL can grow quite big, each case should be considered separately.

In the next post I'll walk through a generic example.