Linq With Multiple Where Conditions

Question

I have 3 Drop Down Lists and a search button. The Drop downs provide the search criteria. So, the values can be all, few or none of them. The T-sql stored procedure would be foll. Is it possible to write a linq for the same.

Select customerId, productId, supplierId
From Orders
Where ( customerId = @customerId Or @customerId IS NULL )
  And ( productId = @productId Or @productId IS NULL )
  And ( supplierId= @supplierId Or @supplierId IS NULL )

I'm using .Net 4.0, Entity data model

possible duplicate of [Multiple WHERE clause in Linq](http://stackoverflow.com/questions/679644/multiple-where-clause-in-linq) — Jens Kloster, Nov 04 '13 at 07:28
Or http://stackoverflow.com/questions/10742793/entity-framework-linq-is-null-is-not-null-issue — Janne Matikainen, Nov 04 '13 at 07:37

doogle · Accepted Answer · 2013-11-04T08:01:22.510

6

It's a very simple query, if you're not entirely sure how to do things in LINQ, check out the LINQ 101 samples by Microsoft. They offer an easy to glance at reference on the common tasks performed with LINQ.

Just shooting from the hip; assuming you have variables for productId, customerId, and supplierId the code below should work.

using(var db = new YourDbContext())
{
    var results = db.Orders.Where(o => 
            (customerId == null || o.CustomerId == customerId) 
            && (productId == null || o.ProductId == productId) 
            && (supplierId == null || o.SupplierId == supplierId))
        .Select(o => new { o.CustomerId, o.ProductId, o.SupplierId }).ToList();
}

edited Nov 04 '13 at 08:01

answered Nov 04 '13 at 07:26

doogle

3,376
18
23

Note that the original SQL said `@customerId IS NULL`, not `customerId IS NULL`... – Heinzi Nov 04 '13 at 07:30
Thanks. Pretty late, didn't catch that. – doogle Nov 04 '13 at 08:00

score 2 · Answer 2 · answered Nov 04 '13 at 07:28

Using LINQ, you have the possibility to only add a Where clause when it is actually needed:

var o = context.Orders;

if (userHasChosenACustomerId)
{
    o = o.Where(o => o.CustomerId == theChosenCustomerId);
}

if (userHasChosenAProductId)
{
    o = o.Where(o => o.ProductId == theChosenProductId);
}    ...

var result = o.ToList();

which might communicate your intent more clearly than the ORs. (Replace userHasChosenACustomerId and theChosenCustomerId by the appropriate methods of your drop down UI control.)

Linq With Multiple Where Conditions

2 Answers2