Using expressions to filter data from a database

The article is based on a response to StackOverflow . I will begin with a description of the problem that I encountered. There are several entities in the database that need to be displayed as tables on the UI. Entity Framework is used to access the database. For these tables there are filters, by the fields of these entities. You need to write code to filter entities by parameters.



For example, there are 2 User and Product entities.

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; } } 

Suppose we need to filter users by name, and products by name. We write 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)); } 

Immediately, we note that these two methods are almost identical and differ only in the property of the entity by which the data is filtered. If we have dozens of entities, each of which has dozens of fields for which filtering is needed, this leads to some difficulties: difficulty in maintaining the code, mindless copying and, as a result, slow development and high probability of errors. Paraphrasing Fowler, begins to smack . I would like to write something more universal instead of duplicating the code. Narimer:

 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)); } 

Sorry if we try to filter

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

then we get the error “Test method ExpressionTests.ExpressionTest.TestFilter threw exception:

System.NotSupportedException : The LINQ to Entities. Because

Expressions

Let's try to figure out what went wrong.



The Where method takes a parameter of type Expression <Func <TEntity, bool >>. Those. Linq does not work with delegates, but with expression trees, which builds SQL queries.



Expression describes a syntax tree node. To better understand how they work, consider an expression that checks that the name is equal to the string

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

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







It turns out about such a tree







The fact is that when we pass a delegate as a parameter, another tree is formed in which, instead of accessing the property of the entity, the Invoke method of the parameter (delegate) is called. When Linq tries to build a SQL query on this tree, he does not know how to interpret the Invoke method and throws a NotSupportedException exception.



Thus, our task is to replace the reference to the property of the entity (the part of the tree that is highlighted in red) with the expression passed through the parameter. Let's try:

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

Now we see the error "Method name expected" already at the compilation stage.







The problem is that the expression is a class that represents the nodes of the syntactic tree , not the delegate itself and cannot be called directly. Now the main task is to find a way to form an expression, passing it another parameter as a parameter.

The visitor

After a short googling, I found a solution to a similar problem on StackOverflow .



To work with expressions, there is a special class ExpressionVisitor that uses the Visitor pattern. Its essence is that it bypasses all the nodes of the expression tree in the parsing order of the syntax tree and allows them to be modified or to return another node instead. If neither the node itself nor its child nodes have changed, then the original expression is returned.



Those. inheriting from the ExpressionVisitor class, we can replace any tree node with an expression that we pass through the parameter. Thus, we need to place in the tree some node-label, which we replace with the parameter during the crawl. To do this, we write an extension method that will simulate an expression call and will be a label.

 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 insert one expression into another

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

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

 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; } } 

Now 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 the debug, we see that the expression did not turn out exactly as we expected. The filter still contains an Invoke method.







The fact is that the parameterGetter expression and the finalFilter expression use two different arguments. Therefore, we need to replace the argument in parameterGetter with the argument from finalFilter. To do this, we write another visitor.







As a result, we get the following code:

 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 can finally write our filtering 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()); } 

Afterword

The expression substitution approach can be used not only for filtering, but also for sorting and in general for any queries to the database.



Also, this method allows you to store expressions with business logic separately from the database requests themselves.



Completely reload code on githaba .