Using Expressions to Filter Data of Database

I would like to start with a description of the problem that I encountered. There are entities in the database that need to be displayed as tables on the UI. The Entity Framework is used to access the database. There are filters for these table columns.

It is necessary to write a code to filter entities by parameters.

For example, there are two entities: User and Product.

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
}

Assume we need to filter users and products by name. We create methods to filter each entity.

public IQueryable<User> FilterUsersByName(IQueryable<User> users, string text)
{
    return users.Where(user => user.Name.Contains(text));
}

public IQueryable<Product> FilterProductsByName(IQueryable<Product> products, string text)
{
    return products.Where(product => product.Name.Contains(text));
}

As you can see, these two methods are almost identical and differ only in the entity property, by which that filters the data.

It may be a challenge if we have dozens of entities with dozens of fields that require filtering. Complexity is in code support, thoughtless copying, and as a result, slow development and high probability of error.

Paraphrasing Fowler, it starts smelling. I would like to write something standard instead of code duplication. For example:

public IQueryable<User> FilterUsersByName(IQueryable<User> users, string text)
{
    return FilterContainsText(users, user => user.Name, text);
}

public IQueryable<Product> FilterProductsByName(IQueryable<Product> products, string text)
{
    return FilterContainsText(products, propduct => propduct.Name, text);
}

public IQueryable<TEntity> FilterContainsText<TEntity>(IQueryable<TEntity> entities,
 Func<TEntity, string> getProperty, string text)
{
    return entities.Where(entity => getProperty(entity).Contains(text));
}

Unfortunately, if we try filtering:

public void TestFilter()
{
    using (var context = new Context())
    {
            var filteredProducts = FilterProductsByName(context.Products, "name").ToArray();
    }
}

We will get the error «Test method ExpressionTests.ExpressionTest.TestFilter threw the exception:
System.NotSupportedException: The LINQ expression node type ‘Invoke’ is not supported in LINQ to Entities.

Expressions

Let’s check what went wrong.

The Where method accepts a parameter of the Expression<Func<TEntity, bool>> type. Thus, Linq works with expression trees, by which it builds SQL queries, rather than with delegates.

The Expression describes a syntax tree. To better understand how they are structured, consider the expression, which checks that a name equals a row.

Expression<Func<Product, bool>> expected = product => product.Name == "target";

When debugging, we can see the structure of this expression (key properties are marked in red).

We have the following tree:

When we passing a delegate as a parameter, a different tree is generated, which calls the Invoke method on the (delegate) parameter instead of invoking the entity property.

When Linq is trying to build an SQL query by this tree, it does not know how to interpret the Invoke method and throws NotSupportedException.

Thus, our task is to replace the cast to the entity property (the tree part marked in red) with the expression that is passed via this parameter.

Let’s try:

Expression<Func<Product, string>> propertyGetter = product => product.Name;
Expression<Func<Product, bool>> filter = product => propertyGetter(product) == "target"

Now, we can see the «Method name expected» error at the compilation stage.

The issue is that an expression is a class that represents nodes of a syntax tree, rather than the delegate and it cannot be called directly. Now, the main task is to find a way to create an expression passing another parameter to it.

The Visitor

After a brief Google search, I found a solution of the similar issue at StackOverflow.

To work with expressions, there is the ExpressionVisitor class, which uses the Visitor pattern. It is designed to traverse all the nodes of the expression tree in the order of parsing the syntax tree and allows modifying them or returning another node instead. If neither the node nor its child nodes are changed, the original expression is returned.

When inheriting from the ExpressionVisitor class, we can replace any tree node with the expression, which we pass via the parameter. Thus, we need to put some node-label, which we will replace with a parameter, into the tree. To do this, write an extension method that will simulate the call of the expression and will be a marker.

public static class ExpressionExtension
{
    public static TFunc Call<TFunc>(this Expression<TFunc> expression)
    {
        throw new InvalidOperationException("This method should never be called. It is a marker for replacing.");
    }
}

Now, we can replace one expression with another

Expression<Func<Product, string>> propertyGetter = product => product.Name;
Expression<Func<Product, bool>> filter = product => propertyGetter.Call()(product) == "target";

It is necessary to write a visitor, which will replace the Call method with its parameter in the expression tree:

public class SubstituteExpressionCallVisitor : ExpressionVisitor
{
    private readonly MethodInfo _markerDesctiprion;

    public SubstituteExpressionCallVisitor()
    {
        _markerDesctiprion =
            typeof(ExpressionExtension).GetMethod(nameof(ExpressionExtension.Call)).GetGenericMethodDefinition();
    }

    protected override Expression VisitMethodCall(MethodCallExpression node)
    {
        if (IsMarker(node))
        {
            return Visit(ExtractExpression(node));
        }
        return base.VisitMethodCall(node);
    }

    private LambdaExpression ExtractExpression(MethodCallExpression node)
    {
        var target = node.Arguments[0];
        return (LambdaExpression)Expression.Lambda(target).Compile().DynamicInvoke();
    }

    private bool IsMarker(MethodCallExpression node)
    {
        return node.Method.IsGenericMethod && node.Method.GetGenericMethodDefinition() == _markerDesctiprion;
    }
}

We can replace our marker:

public static Expression<TFunc> SubstituteMarker<TFunc>(this Expression<TFunc> expression)
{
    var visitor = new SubstituteExpressionCallVisitor();
    return (Expression<TFunc>)visitor.Visit(expression);
}

Expression<Func<Product, string>> propertyGetter = product => product.Name;
Expression<Func<Product, bool>> filter = product => propertyGetter.Call()(product).Contains("123");
Expression<Func<Product, bool>> finalFilter = filter.SubstituteMarker();

In debugging, we can see that the expression is not what we expected. The filter still contains the Invoke method.

The fact is that the parameterGetter and finalFilter expressions use two different arguments. Thus, we need to replace an argument in parameterGetter with the argument in finalFilter. To do this, we create another visitor:

The result is as follows:

public class SubstituteParameterVisitor : ExpressionVisitor
{
    private readonly LambdaExpression _expressionToVisit;
    private readonly Dictionary<ParameterExpression, Expression> _substitutionByParameter;

    public SubstituteParameterVisitor(Expression[] parameterSubstitutions, LambdaExpression expressionToVisit)
    {
        _expressionToVisit = expressionToVisit;
        _substitutionByParameter = expressionToVisit
                .Parameters
                .Select((parameter, index) => new {Parameter = parameter, Index = index})
                .ToDictionary(pair => pair.Parameter, pair => parameterSubstitutions[pair.Index]);
    }

    public Expression Replace()
    {
        return Visit(_expressionToVisit.Body);
    }

    protected override Expression VisitParameter(ParameterExpression node)
    {
        Expression substitution;
        if (_substitutionByParameter.TryGetValue(node, out substitution))
        {
            return Visit(substitution);
        }
        return base.VisitParameter(node);
    }
}

public class SubstituteExpressionCallVisitor : ExpressionVisitor
{
    private readonly MethodInfo _markerDesctiprion;

    public SubstituteExpressionCallVisitor()
    {
        _markerDesctiprion = typeof(ExpressionExtensions)
            .GetMethod(nameof(ExpressionExtensions.Call))
            .GetGenericMethodDefinition();
    }

    protected override Expression VisitInvocation(InvocationExpression node)
    {
        var isMarkerCall = node.Expression.NodeType == ExpressionType.Call &&
                           IsMarker((MethodCallExpression) node.Expression);
        if (isMarkerCall)
        {
            var parameterReplacer = new SubstituteParameterVisitor(node.Arguments.ToArray(),
                Unwrap((MethodCallExpression) node.Expression));
            var target = parameterReplacer.Replace();
            return Visit(target);
        }
        return base.VisitInvocation(node);
    }

    private LambdaExpression Unwrap(MethodCallExpression node)
    {
        var target = node.Arguments[0];
        return (LambdaExpression)Expression.Lambda(target).Compile().DynamicInvoke();
    }

    private bool IsMarker(MethodCallExpression node)
    {
        return node.Method.IsGenericMethod &&
               node.Method.GetGenericMethodDefinition() == _markerDesctiprion;
    }
}

Now, everything works as it should and we, finally, can write our filtration method

public IQueryable<TEntity> FilterContainsText<TEntity>(IQueryable<TEntity> entities, Expression<Func<TEntity, string>> getProperty, string text)
{
    Expression<Func<TEntity, bool>> filter = entity => getProperty.Call()(entity).Contains(text);
    return entities.Where(filter.SubstituteMarker());
}

Conclusion

The approach with the expression replacement can be used not only for filtering but also for sorting and any query to the database.

Also, this method allows storing expressions along with business logic separately from the queries to the database.

You can look at the code at GitHub.

This article is based on a StackOverflow reply.