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.

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

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:

Unfortunately, if we try filtering:

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.

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:

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.

Now, we can replace one expression with another

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

We can replace our marker:

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:

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

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.

Sergey Ryabov

Sergey is a Lead .NET Software Engineer with 6+ years experience in building high-quality software. His experience ranges from developing mobile applications for a small business to building the back-end for responsive high load websites for worldwide companies.

Latest posts by Sergey Ryabov (see all)

Sergey Ryabov

Sergey is a Lead .NET Software Engineer with 6+ years experience in building high-quality software. His experience ranges from developing mobile applications for a small business to building the back-end for responsive high load websites for worldwide companies.