CODEWITHSUNDEEP
c#linq
user609886
user609886

Reputation: 1669

Multiple WHERE Clauses with LINQ extension methods

I have a LINQ query that looks like the following:

DateTime today = DateTime.UtcNow;
var results = from order in context.Orders
              where ((order.OrderDate <= today) && (today <= order.OrderDate))
              select order;

I am trying to learn / understand LINQ. In some cases, I need to add two additional WHERE clauses. In an effort to do this, I'm using:

if (useAdditionalClauses)
{
  results = results.Where(o => o.OrderStatus == OrderStatus.Open)  // Now I'm stuck.
}

As you can see, I know how to add an additional WHERE clause. But how do I add multiple? For instance, I'd like to add

WHERE o.OrderStatus == OrderStatus.Open AND o.CustomerID == customerID

to my previous query. How do I do this using extension methods?

Thank you!

Upvotes: 98

Views: 185838

Answers (7)

cadrell0
cadrell0

Reputation: 17307

Just use the && operator like you would with any other statement that you need to do boolean logic.

if (useAdditionalClauses)
{
  results = results.Where(
                  o => o.OrderStatus == OrderStatus.Open 
                  && o.CustomerID == customerID)     
}

Upvotes: 0

Sevenate
Sevenate

Reputation: 6475

If you working with in-memory data (read "collections of POCO") you may also stack your expressions together using PredicateBuilder like so:

// initial "false" condition just to start "OR" clause with
var predicate = PredicateBuilder.False<YourDataClass>();

if (condition1)
{
    predicate = predicate.Or(d => d.SomeStringProperty == "Tom");
}

if (condition2)
{
    predicate = predicate.Or(d => d.SomeStringProperty == "Alex");
}

if (condition3)
{
    predicate = predicate.And(d => d.SomeIntProperty >= 4);
}

return originalCollection.Where<YourDataClass>(predicate.Compile());

The full source of mentioned PredicateBuilder is bellow (but you could also check the original page with a few more examples):

using System;
using System.Linq;
using System.Linq.Expressions;
using System.Collections.Generic;

public static class PredicateBuilder
{
  public static Expression<Func<T, bool>> True<T> ()  { return f => true;  }
  public static Expression<Func<T, bool>> False<T> () { return f => false; }

  public static Expression<Func<T, bool>> Or<T> (this Expression<Func<T, bool>> expr1,
                                                      Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.OrElse (expr1.Body, invokedExpr), expr1.Parameters);
  }

  public static Expression<Func<T, bool>> And<T> (this Expression<Func<T, bool>> expr1,
                                                       Expression<Func<T, bool>> expr2)
  {
    var invokedExpr = Expression.Invoke (expr2, expr1.Parameters.Cast<Expression> ());
    return Expression.Lambda<Func<T, bool>>
          (Expression.AndAlso (expr1.Body, invokedExpr), expr1.Parameters);
  }
}

Note: I've tested this approach with Portable Class Library project and have to use .Compile() to make it work:

Where(predicate .Compile() );

Upvotes: 19

Gent
Gent

Reputation: 2685

results = context.Orders.Where(o => o.OrderDate <= today && today <= o.OrderDate)

The select is uneeded as you are already working with an order.

Upvotes: 1

Josh C.
Josh C.

Reputation: 4363

you can use && and write all conditions in to the same where clause, or you can .Where().Where().Where()... and so on.

Upvotes: 2

David
David

Reputation: 218847

Two ways:

results = results.Where(o => (o.OrderStatus == OrderStatus.Open) &&
                             (o.CustomerID == customerID));

or:

results = results.Where(o => (o.OrderStatus == OrderStatus.Open))
                 .Where(o => (o.CustomerID == customerID));

I usually prefer the latter. But it's worth profiling the SQL server to check the query execution and see which one performs better for your data (if there's any difference at all).

A note about chaining the .Where() methods: You can chain together all the LINQ methods you want. Methods like .Where() don't actually execute against the database (yet). They defer execution until the actual results are calculated (such as with a .Count() or a .ToList()). So, as you chain together multiple methods (more calls to .Where(), maybe an .OrderBy() or something to that effect, etc.) they build up what's called an expression tree. This entire tree is what gets executed against the data source when the time comes to evaluate it.

Upvotes: 188

Bryan B
Bryan B

Reputation: 4535

You can continue chaining them like you've done.

results = results.Where (o => o.OrderStatus == OrderStatus.Open);
results = results.Where (o => o.InvoicePaid);

This represents an AND.

Upvotes: 29

Andras Zoltan
Andras Zoltan

Reputation: 42353

Surely:

if (useAdditionalClauses) 
{ 
  results = 
    results.Where(o => o.OrderStatus == OrderStatus.Open && 
    o.CustomerID == customerID)  
}

Or just another .Where() call like this one (although I don't know why you would want to, unless it's split by another boolean control variable):

if (useAdditionalClauses) 
{ 
  results = results.Where(o => o.OrderStatus == OrderStatus.Open).
    Where(o => o.CustomerID == customerID);
}

Or another reassignment to results: `results = results.Where(blah).

Upvotes: 6

Related Questions