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.