Dynamic linq predicates

This post is actually a repost of this great article A universal PredicateBuilder that I found very useful so I decided to share it here.

Sometimes I need to build dynamically the where part of a linq query and, unfortunately, there is no straight forward, out of the box solution inside the .NET framework for doing this.

For example, imagine that you have a list of houses that are for rent, and the user can filter this list depending on: country, region, area, price range, facilities and so on. So your linq query might look something like this:

using (var context = new DatabaseEntities())
{
	var list = context.Rentals.Where(item => 
				(string.IsNullOrEmpty(countryFilterValue) || item.Country == countryFilterValue) &&
				(string.IsNullOrEmpty(regionFilterValue) || item.Region == regionFilterValue) &&
				(string.IsNullOrEmpty(areaFilterValue) || item.Area == areaFilterValue)).ToList();
}

If you’re using linq to entities to query your DB, then the SQL that will be generated will most likely be inefficient. In our example, the generated SQL for the first condition will be more or less like this:

exec sp_executesql N'SELECT 
*
FROM [dbo].[Rentals] AS [Extent1]
WHERE  (@p__linq__0 IS NULL) OR (( CAST(LEN(@p__linq__0) AS int)) = 0) OR ([Extent1].[Country] = @p__linq__1)',N'@p__linq__0 nvarchar(4000),@p__linq__1 varchar(8000)',@p__linq__0=N'',@p__linq__1=''

In the where part you will have a lot of similar conditions (one for each filer). There could be a lot of filters but almost always the user will not complete all of them. So you will end up with an inefficient query that checks if an empty parameter is null or empty.

The solution is to build dynamically the where part of the linq query. Fortunately this is possible using the code that you can find in this post: A universal PredicateBuilder

Below I did a copy/paste of the original code so I can have it forever and ever with me if I need it ūüôā

/// <summary>
/// Enables the efficient, dynamic composition of query predicates.
/// </summary>
public static class PredicateBuilder
{
    /// <summary>
    /// Creates a predicate that evaluates to true.
    /// </summary>
    public static Expression<Func<T, bool>> True<T>() { return param => true; }
 
    /// <summary>
    /// Creates a predicate that evaluates to false.
    /// </summary>
    public static Expression<Func<T, bool>> False<T>() { return param => false; }
 
    /// <summary>
    /// Creates a predicate expression from the specified lambda expression.
    /// </summary>
    public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }
 
    /// <summary>
    /// Combines the first predicate with the second using the logical "and".
    /// </summary>
    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.AndAlso);
    }
 
    /// <summary>
    /// Combines the first predicate with the second using the logical "or".
    /// </summary>
    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> first, Expression<Func<T, bool>> second)
    {
        return first.Compose(second, Expression.OrElse);
    }
 
    /// <summary>
    /// Negates the predicate.
    /// </summary>
    public static Expression<Func<T, bool>> Not<T>(this Expression<Func<T, bool>> expression)
    {
        var negated = Expression.Not(expression.Body);
        return Expression.Lambda<Func<T, bool>>(negated, expression.Parameters);
    }
 
    /// <summary>
    /// Combines the first expression with the second using the specified merge function.
    /// </summary>
    static Expression<T> Compose<T>(this Expression<T> first, Expression<T> second, Func<Expression, Expression, Expression> merge)
    {
        // zip parameters (map from parameters of second to parameters of first)
        var map = first.Parameters
            .Select((f, i) => new { f, s = second.Parameters[i] })
            .ToDictionary(p => p.s, p => p.f);
 
        // replace parameters in the second lambda expression with the parameters in the first
        var secondBody = ParameterRebinder.ReplaceParameters(map, second.Body);
 
        // create a merged lambda expression with parameters from the first expression
        return Expression.Lambda<T>(merge(first.Body, secondBody), first.Parameters);
    }
 
    class ParameterRebinder : ExpressionVisitor
    {
        readonly Dictionary<ParameterExpression, ParameterExpression> map;
 
        ParameterRebinder(Dictionary<ParameterExpression, ParameterExpression> map)
        {
            this.map = map ?? new Dictionary<ParameterExpression, ParameterExpression>();
        }
 
        public static Expression ReplaceParameters(Dictionary<ParameterExpression, ParameterExpression> map, Expression exp)
        {
            return new ParameterRebinder(map).Visit(exp);
        }
 
        protected override Expression VisitParameter(ParameterExpression p)
        {
            ParameterExpression replacement;
 
            if (map.TryGetValue(p, out replacement))
            {
                p = replacement;
            }
 
            return base.VisitParameter(p);
        }
    }
}

Now the where part can be created dynamically, including only the filters for which the user provided a value:

using (var context = new DatabaseEntities ())
{
	var predicate = PredicateBuilder.Create<Rentals>(item => true);

	if (!string.IsNullOrWhiteSpace(countryFilterValue))
		predicate = predicate.And(item => item.Country == countryFilterValue);
	if (!string.IsNullOrWhiteSpace(regionFilterValue))
		predicate = predicate.And(item => item.Region == regionFilterValue);
	if (!string.IsNullOrWhiteSpace(areaFilterValue))
		predicate = predicate.And(item => item.Area == areaFilterValue);

	var list = context.Rentals.Where(predicate).ToList();
}