c# - Multiple WHERE Clauses with LINQ extension methods

Question

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!

Answer 1

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.