Handling multi-select filter queries with the entity framework

Hackered
Sunday, May 24, 2015
by Sean McAlinden

I recently worked on a story to enable a group of multi-select filters for an application built on top of the entity framework.

Each multi-select filter had to be built as a set of AND predicates however within each set the values were to be OR predicates.

For example:

Get me all funds where:

(fundName like 'The first fund' OR fundName like 'My second Fund') AND (fundStatus like 'OpenForInvestment' OR fundStatus like 'ClosedForInvestment')

Building up AND predicates is quite easy however mixing ANDs and ORs is a little more difficult, especially with using Linq to entities.

Linq to entities does not support invoke expressions so when trying to write standard linq to solve the OR predicates results in exceptions such as "The LINQ expression node type 'Invoke' is not supported in LINQ to Entities'".

I found a few potential options on Google including using an expandable context however my preferred approach came from the following: http://blogs.msdn.com/b/meek/archive/2008/05/02/linq-to-entities-combining-predicates.aspx.

First you need to copy the following classes into your codebase:

The Query Predicate Composer

public static class QueryPredicateComposer
{
    public static Expression<T> Compose<T>(
        this Expression<T> first, 
        Expression<T> second, 
        Func<Expression, Expression, Expression> merge)
    {
        var map = first.Parameters.Select((f, i) => new { f, s = second.Parameters[i] })
            .ToDictionary(p => p.s, p => p.f);

        var secondBody = RebindParameterVisitor.ReplaceParameters(map, second.Body);
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(
        this Expression<Func<T, bool>> first, 
        Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.And);
    }

    public static Expression<Func<T, bool>> Or<T>(
        this Expression<Func<T, bool>> first, 
        Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.Or);
    }
}

The Rebind Parameter Visitor

public class RebindParameterVisitor : ExpressionVisitor
{
    private readonly Dictionary<ParameterExpression, ParameterExpression> map;

    public RebindParameterVisitor(
        Dictionary<ParameterExpression, 
        ParameterExpression> map)
    {
        this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
    }

    public static Expression ReplaceParameters(
        Dictionary<ParameterExpression, 
        ParameterExpression> map, 
        Expression exp)
    {
        return new RebindParameterVisitor(map).Visit(exp);
    }

    protected override Expression VisitParameter(ParameterExpression p)
    {
        ParameterExpression replacement;
        if (map.TryGetValue(p, out replacement))
        {
            p = replacement;
        }
        return base.VisitParameter(p);
    }
}

These classes have done all the heavy lifting, now we just need to use them...

Using the QueryPredicateComposer

Now the classes are in place, it's time to see them in action with a practical example.

I will demonstrate using the following simple entity:

public class Fund
{
    public Fund(int id, string name, string status)
    {
        Id = id;
        Name = name;
        Status = status;
    }

    public int Id { get; set; }
    public string Name { get; set; }
    public string Status { get; set; }
}

I generally utilize the repository pattern when using ORM software such as the entity framework, I am going to add a set of filters to my List method parameter:

public class ListQuery<TEntity>
{
    public ListQuery()
    {
        Filters = new List<Expression<Func<TEntity, bool>>>();
    }

    public List<Expression<Func<TEntity, bool>>> Filters { get; private set; }
}

I then use these filters to build up the query predicate in my repository method:

public class FundRepository
{
    public List<Fund> List(ListQuery<Fund> listQuery)
    {
        var queryable = context.Set<Fund>().AsQueryable();

        foreach (var expression in listQuery.Filters)
        {
            queryable = queryable.Where(expression);
        }
        return queryable.ToList();
    }
}

At this point we are pretty much ready to run against EF, we now need to build our OR parameters for each filter set.

Multi 'OR' filter method examples

private static Expression<Func<Fund, bool>> GetFundsByNamePredicate(string[] fundNames)
{
    Expression<Func<Fund, bool>> predicate = null;

    for (var i = 0; i < fundNames.Length; i++)
    {
        var fundName = fundNames[i];

        if (i == 0)
        {
            predicate = x => x.Name.Contains(fundName);
        }
        else
        {
            predicate = predicate.Or(x => x.Name.Contains(fundName));
        }
    }

    return predicate;
}

private static Expression<Func<Fund, bool>> GetFundsByStatusPredicate(string[] fundStatuses)
{
    Expression<Func<Fund, bool>> predicate = null;

    for (var i = 0; i < fundStatuses.Length; i++)
    {
        var fundStatus = fundStatuses[i];

        if (i == 0)
        {
            predicate = x => x.Status == fundStatus;
        }
        else
        {
            predicate = predicate.Or(x => x.Status == fundStatus);
        }
    }

    return predicate;
}

The main things to notice here are the predicate.Or statements and the fact I am copying the filter values to variables outside the lambda expression.

The predicate.Or statements are using our new QueryPredicateComposer.Or extension.

The variables storing the filter values are to aide the Entity framework, it doesn't particular like invoking methods including the get_item of an indexer.

Let's bring it in...

var parameters = new
{
    FundNames = new[] { "US Equity", "Global Macro" },
    FundStatuses = new[] { "OpenForInvestment", "Liquidated" }
};
var fundRepository = new FundRepository();

var getFundQuery = new ListQuery<Fund>();

var fundsByNamePredicate = GetFundsByNamePredicate(parameters.FundNames);
var fundsByStatusPredicate = GetFundsByStatusPredicate(parameters.FundStatuses);

getFundQuery.Filters.Add(fundsByNamePredicate);
getFundQuery.Filters.Add(fundsByStatusPredicate);

var filteredFunds = fundRepository.List(getFundQuery);

As you can see the private methods are called for each filter set (fundNames and fundStatuses) and then added to my getFundQuery object.

Once added I pass the getFundQuery object to the fundRepository.List method.

The result will be a query with the following logic:

SELECT * FROM Fund WHERE (Name like '%US Equity%' OR Name like '%Global Macro%' ) AND (Status like '%OpenForInvestment%' OR Status like '%Liquidated%' )

Conclusion

Expressions are without a doubt a complicated part of C#, however they are extremely powerful and allow us to do some pretty cool things.

I hope this has been useful.