Build an OR query expression progressively

Question

In LINQ one can build a LINQ query progressively as follows:

var context = new AdventureWorksDataContext();
// Step 1
var query = context.Customers.Where(d => d.CustomerType == "Individual");
// Step 2
query = query.Where(d => d.TerritoryID == 3);

The above query would yield an equivalent SQL statement with a WHERE clause comprising of two predicates combined together by an AND logical operator like the following:

SELECT * FROM Customers WHERE CustomerType = 'Individual' AND TerritoryID = 3

Can one build a LINQ query to yield an equivalent SQL statement, progressively, such that the resulting query has a WHERE clause with the predicates combined together by an OR logical operator as follows?

SELECT * FROM Customers WHERE CustomerType = 'Individual' OR TerritoryID = 3

why not `Where(d => d.TerritoryID == 3 || CustomerType = 'Individual');`? — cuongle, Mar 28 '13 at 08:49
See this answer: http://stackoverflow.com/questions/2101540/linq-or-equivalent-of-where — Alexander, Mar 28 '13 at 08:53
@CuongLe: There are circumstances where you need to build the query progressively. LINQ offers you that ability as I demonstrated above and the resulting predicates are combined using an AND logical operator. What am seeking is a way of doing the same but have the predicates combined using an OR logical operator — John Gathogo, Mar 28 '13 at 08:54

score 23 · Accepted Answer · answered Mar 28 '13 at 08:54

You would need to construct the filters first, and then combine the filters into a single lambda that you can use as the composed query:

var filters = new List<Expression<Func<YourType, bool>>>();
filters.Add(d => d.TerritoryID == 3);
filters.Add(d => d.CustomerType == "Individual");
...

var lambda = AnyOf(filters.ToArray());
// this is: d => d.TerrotoryID == 3 || d.CustomerType == "Individual";

var data = src.Where(lambda);

Using:

static Expression<Func<T,bool>> AnyOf<T>(
          params Expression<Func<T,bool>>[] expressions)
{
    if (expressions == null || expressions.Length == 0) return x => false;
    if (expressions.Length == 1) return expressions[0];

    var body = expressions[0].Body;
    var param = expressions[0].Parameters.Single();
    for (int i = 1; i < expressions.Length; i++)
    {
        var expr = expressions[i];
        var swappedParam = new SwapVisitor(expr.Parameters.Single(), param)
                            .Visit(expr.Body);
        body = Expression.OrElse(body, swappedParam);
    }
    return Expression.Lambda<Func<T, bool>>(body, param);
}
class SwapVisitor : ExpressionVisitor
{
    private readonly Expression from, to;
    public SwapVisitor(Expression from, Expression to){
        this.from = from;
        this.to = to;
    }
    public override Expression Visit(Expression node)
    {
        return node == from ? to : base.Visit(node);
    }
}

This saved me from having to pull in the third party LINQKit library. Thanks. — John Gathogo, Apr 02 '13 at 04:52

score 1 · Answer 2 · answered Mar 28 '13 at 08:56

if you want it in two steps, you can use union:


var context = new AdventureWorksDataContext();
// Step 1
var query = context.Customers.Where(d => d.CustomerType == "Individual");
// step2 
query = query.Union(context.Customers.Where(d => d.TerritoryID == 3));

score 0 · Answer 3 · answered Mar 28 '13 at 09:06

To accomplish what you ask without using the dynamic linq lib, you can define expressions for each test in the where clause, then use them to build a lambda expression:

Expression<Func<Customer, bool>> isIndividualCustomer = c => c.CustomerType == "Individual";

Expression<Func<Customer, bool>> territoryIdIs3 = c => c.TerritoryID == 3;

Expression<Func<Car, bool>> customerIsIndividualOrOfTerritoryId3 = Expression.Lambda<Func<Customer, bool>>(
    Expression.Or(isIndividualCustomer.Body, territoryIdIs3.Body), isIndividualCustomer.Parameters.Single());

Usage:

var query = context.Customers.Where(customerIsIndividualOrOfTerritoryId3);

Important: "variable 'c' of type 'Customer' referenced from scope '', but it is not defined" (that's from the runtime; you can't do that without re-mapping the parameters - for example via a visitor); also, should be `OrElse`, not `Or`. Very minor, but I think you meant `Customer` in the last line. — Marc Gravell, Mar 28 '13 at 09:08

score 0 · Answer 4 · answered Mar 28 '13 at 09:12

While you can do this sort of thing directly, I use the LinqKit library which makes such progressive query building really easy:

You would end up with:

var context = new AdventureWorksDataContext();
// Step 1
var query = context.Customers.Where(d => d.CustomerType == "Individual");
// Step 2
query = query.Or(d => d.TerritoryID == 3);

The system I'm working on needs a lot of this, and LinqKit is a major life saver. You can find it here.

(Note: I'm not affiliated with LinqKit's developer in anyway - just a fan.)

I think this syntax is not valid. You cannot add 'Or' to query, you have to add it to expression, which is major deal breaker. — Cute pumpkin, Nov 16 '20 at 12:35

Build an OR query expression progressively

4 Answers4

Linked

Related