Flexible EFCore Filtering with Expression

Filtering data in EntityFramework is a fairly simple task that can be easily solved using the method Where() in LINQ. For examples, I will use the most popular domain area for all university laboratory and practical work, namely the library. For example, if you want to filter books by year of publication, you can do it like this:

var filteredBooks = await context.Books.Where(x => x.Year == 2024);

This approach works fine as long as filtering does not require multiple fields whose values ​​come from the front end. The first idea that might come to mind is to use a book model with nullable fields, which will signal that filtering is not required for these fields.

Let's say our request is to show all books from publisher X, published in year Y and genre Z. Then on the front end we can pass the following JSON:

"filter": {
  "publisherId": X,
  "year": Y,
  "genreId": Z
}

When parsing this JSON, the missing fields will automatically have a value null. As a result, the server-side request may look like this:

var query = context.Books;
if (request.Filter.Year != null)
    query = query.Where(x => x.Year == request.Filter.Year);
if (request.Filter.PublisherId != null)
    query = query.Where(x => x.PublisherId == request.Filter.PublisherId);
if (request.Filter.GenreId != null)
    query = query.Where(x => x.GenreId == request.Filter.GenreId);

This is a simple and working solution. However, as the model grows, such a design will become more complex. Moreover, for each new model you will have to repeat this code adding new fields.

However, the interface IQueryable provides the ability to pass into a method Where() type expression Expression. By the way, when we pass a lambda, it is automatically translated into an expression so that the database provider can interpret it correctly.

Filtering using Expression

In order to use such filtering, you will need to pass fields for filtering in the request in the following form

{
  "filters": [
    { "propName": "Year", "value": "2024" },
    { "propName": "PublisherId", "value": "1234" },
    { "propName": "GenreId", "value": "1234" }
  ]
}

On the server side, this request is converted into an expression, which can then be passed to the method Where(). Here is an example code to generate the expression:

public Expression<Func<T, bool>> ParseToExpression<T>(IEnumerable<FilterField> filters)
{
    if (filters?.Any() != true)
        return x => true;
   
    var param = Expression.Parameter(typeof(T), "x");
    Expression? expressionBody = null;

    foreach (var filter in filters)
    {
        var propertyName = filter.PropertyName.FirstCharToUpper();
        var prop = typeof(T).GetProperty(propertyName);
        var propType = prop?.PropertyType;
        Expression member;

        try
        {
            member = Expression.Property(param, propertyName);
        }
        catch (ArgumentException)
        {
            continue;
        }

        IConstantExpressionHandler handler = propType switch
        {
            _ when propType == typeof(Guid) => new GuidConstantExpressionHandler(),
            _ when propType == typeof(int) => new IntegerConstantExpressionHandler(),
            _ when propType == typeof(string) => new StringConstantExpressionHandler(),
            _ => throw new ArgumentOutOfRangeException()
        };
        
        var constant = handler.Handle(filter.Value);
        var expression = Expression.Equal(member, constant);
        expressionBody = expressionBody == null ? expression : Expression.AndAlso(expressionBody, expression);
    }
   
    return Expression.Lambda<Func<T, bool>>(expressionBody, param);
}

In addition, for proper parsing of different data types, it is necessary to create auxiliary classes that implement the interface we created IConstantExpression Handlerwhich has a single method Handle(string value)
returning ConstantExpression.

Example handler for integer values:

public class IntegerConstantExpressionHandler : IConstantExpressionHandler
{
    public ConstantExpression Handle(string value)
    {
        if (int.TryParse(value, out var number))
            return Expression.Constant(number);
        else
            throw new ArgumentException(nameof(value));
    }
}

Handling multiple values ​​for one field

What if you need to filter data by multiple values ​​of the same field, for example by the years 2023 and 2024? To do this, let's change the method by adding the ability to combine conditions via Expression.OrElse:

public Expression<Func<T, bool>> ParseToExpression<T>(IEnumerable<FilterField> filters)
{
    if (filters?.Any() != true)
        return x => true;

    var filtersMap = new Dictionary<string, List<Expression>>();
    var param = Expression.Parameter(typeof(T), "x");
    Expression? expressionBody = null;

    foreach (var filter in filters)
    {
        var propertyName = filter.PropertyName.FirstCharToUpper();
        
        if (!filtersMap.ContainsKey(propertyName))
            filtersMap.Add(propertyName, new List<Expression>());

        var prop = typeof(T).GetProperty(propertyName);
        var propType = prop?.PropertyType;
        Expression member;

        try
        {
            member = Expression.Property(param, propertyName);
        }
        catch (ArgumentException)
        {
            continue;
        }

        var handler = propType switch
        {
            _ when propType == typeof(Guid) => new GuidConstantExpressionHandler(),
            _ when propType == typeof(int) => new IntegerConstantExpressionHandler(),
            _ when propType == typeof(string) => new StringConstantExpressionHandler(),
            _ => throw new ArgumentOutOfRangeException()
        };

        var constant = handler.Handle(filter.Value);
        var expression = Expression.Equal(member, constant);
        filtersMap[propertyName].Add(expression);
    }

    foreach (var prop in filtersMap)
    {
        var expression = prop.Value.Aggregate((acc, x) => Expression.OrElse(acc, x));
        expressionBody = expressionBody == null ? expression : Expression.AndAlso(expressionBody, expression);
    }

    return Expression.Lambda<Func<T, bool>>(expressionBody, param);
}

Filter by calculated fields

That part is why this article was written. Many people are already familiar with the concept of filtering through Expression, but most articles and examples on the Internet (if not all) say nothing about filtering calculated fields, the calculation of which occurs not on the database side, but immediately before sending the DTO. Let's say that an example of such a field in our domain is the state of the book, which we calculate from the last record in the history of this book. Let's imagine the book's states as follows:

In order for our expression builder to process such a field, we need to create this field in the book class, and so that EFCore does not try to add it to the database table, make it static. Well, somehow indicate to the builder that the field is calculated. This is the property we will create in the book class.

[ComputedField]
public static Expression<Func<Book, int>> StateId => x =>
    x.History != null && x.History.Any()
        ? x.History.OrderByDescending(h => h.WhenChanged).FirstOrDefault().StateId
        : 1;

ComputedField in this case, it is the simplest attribute with a scope of application only to properties. In this case, our property stores not data, but an expression for obtaining this data from a book class object. This means we need to teach our builder to process such properties.

public Expression<Func<T, bool>> ParseToExpression<T>(IEnumerable<FilterField> filters)
{
    if (filters?.Any() != true)
        return x => true;

    var filtersMap = new Dictionary<string, List<Expression>>();
    var param = Expression.Parameter(typeof(T), "x");
    Expression? expressionBody = null;

    foreach (var filter in filters)
    {
        var propertyName = filter.PropertyName.FirstCharToUpper();
        
        if (!filtersMap.ContainsKey(propertyName))
            filtersMap.Add(propertyName, new List<Expression>());

        var prop = typeof(T).GetProperty(propertyName);
        var propType = prop?.PropertyType;
        Expression member;

        try
        {
            if (Attribute.IsDefined(prop, typeof(ComputedFieldAttribute)))
            {
                var computedExpression = prop.GetValue(null) as LambdaExpression;
                member = Expression.Invoke(computedExpression, param);
            }
            else
            {
                member = Expression.Property(param, propertyName);
            }
        }
        catch (ArgumentException)
        {
            continue;
        }

        AbstractConstantExpressionHandler handler = propType switch
        {
            _ when propType == typeof(Guid) => new Base64ConstantExpressionHandler(),
            _ when propType == typeof(int) => new IntegerConstantExpressionHandler(),
            _ when propType == typeof(string) => new StringConstantExpressionHandler(),
            _ => throw new ArgumentOutOfRangeException()
        };

        var constant = handler.Handle(filter.Value);
        var expression = Expression.Equal(member, constant);
        filtersMap[propertyName].Add(expression);
    }

    foreach (var prop in filtersMap)
    {
        var expression = prop.Value.Aggregate((acc, x) => Expression.OrElse(acc, x));
        expressionBody = expressionBody == null ? expression : Expression.AndAlso(expressionBody, expression);
    }

    return Expression.Lambda<Func<T, bool>>(expressionBody, param);
}

Thus, we built a powerful and flexible data filtering mechanism in EFCore, which supports both regular and calculated fields, and also made it universal through the use of Generic methods.
Also, if you are interested in this article, I recommend reading the article on Expression in C# and filtering