Dynamic where clause in Linq to Entities

Suppose you want to write Linq Query to filter the records by multiple parameters.

For example you have following method which will filter records based on the array of paramters specified.

public static List<Product> GetProducts(string[] params)
{
	var myQuery = from p in ctxt.Products
				select p;

	foreach(string param in params)
	{
	   myQuery = myQuery.Where(p => p.Description.Contains(param);
	}

	var prodResult = prod.ToList();

	return prodResult;
}

This query works fine if you need the AND concatenation of all parameter filters, you want to fetch records when all the paramters need to be statisfied.

What if you want to write the same query but with OR concatenation, as if any of the parameter is passed, it should return the records.

Here comes the PredicateBuilder by Pete Montgomery which will work with Linq-to-SQL and EntityFramework as well.

There is another PredicateBuilder by albahari , but it does not work well with EntityFramework.

You can use the following code for PredicateBuilder (copied from Pete Montgomery's post).

/// 

/// Enables the efficient, dynamic composition of query predicates.
/// 
public static class PredicateBuilder
{
    /// 

    /// Creates a predicate that evaluates to true.
    /// 
    public static Expression<Func<T, bool>> True<T>() { return param => true; }
 
    /// 

    /// Creates a predicate that evaluates to false.
    /// 
    public static Expression<Func<T, bool>> False<T>() { return param => false; }
 
    /// 

    /// Creates a predicate expression from the specified lambda expression.
    /// 
    public static Expression<Func<T, bool>> Create<T>(Expression<Func<T, bool>> predicate) { return predicate; }
 
    /// 

    /// Combines the first predicate with the second using the logical "and".
    /// 
    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);
    }
 
    /// 

    /// Combines the first predicate with the second using the logical "or".
    /// 
    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);
    }
 
    /// 

    /// Negates the predicate.
    /// 
    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);
    }
 
    /// 

    /// Combines the first expression with the second using the specified merge function.
    /// 
    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);
        }
    }
}

This will provide extension methods that you can use to write your queries. Here is an example how to write the above query with OR concatenation.

public static List<Product> GetProducts(string[] params)
{
	var myQuery = from p in ctxt.Products
				select p;

	Expression<Func<Product, bool>> x = null;

	int i = 1;

	foreach(string param in params)
	{
	   if (i == )
	   {
	      x = L => L.Description.Contains(param);
	   }
	   else 
	   {
	      Expression<Func<Product, bool>> y = L => L.Description.Contains(param); 
	      x = x.Or(y); 
	   }

   	   i = i + 1;
	}

	myQuery = myQuery.Where(x); 
	
	var prodResult = myQuery.ToList();

	return prodResult;
}

References:

• A universal PredicateBuilder
• Dynamically Composing Expression Predicates