Smarter where clause?

Question

I've been experimenting with LINQ to SQL recently and have a quick question. The basic premise is I have a search request which contains a Make and Model which I use to search a DB containing cars.

The expression for my Where Clause is shown below:

.Where(c => c.make == search.make  && c.model == search.model)

This is fine when my search contains both a make and a model. The problem arises when it only contains a make(or vice versa) and not both search fields. I want it to return all cars of that make, it is however returning none.

Im assuming this is because it is looking for the make plus a model that is null or empty?

Is there an elegant way to get around this other than manually building up the query with a series of "if not null append to query" type steps?

That would interest me too, now i solved this kind of problem with an If Else and then in the both cases i execute a different Linq to Sql query. — BigL, Sep 23 '11 at 10:53

score 3 · Answer 1 · edited May 23 '17 at 12:27

3

Have you tried:

.Where(c => (search.make == null || c.make == search.make)  && (search.model == null || c.model == search.model))

Update: There's actually a nice treatment of the general problem, and clean solutions, here: LINQ to SQL Where Clause Optional Criteria. The consensus seems to be that an extension method is cleanest.

edited May 23 '17 at 12:27

Community

1
1

answered Sep 23 '11 at 10:50

Tao

13,457
7
65
76

Thanks, this makes sense. I knew there would be a better way, just didnt jump out at me...it's been a long week :) – Boob Sep 23 '11 at 11:05

score 2 · Answer 2 · answered Sep 23 '11 at 10:49

2

.Where(c => (search.make == null || c.make == search.make) && 
            (search.model == null || c.model == search.model))

answered Sep 23 '11 at 10:49

Dennis Traub

50,557
7
93
108

isn't this doing the wrong thing? (getting cars whose model is null, even if the search was for a specific make and model?) – Tao Sep 23 '11 at 10:52
Thanks @Tao, I misunderstood the question. Corrected my answer accordingly. – Dennis Traub Sep 23 '11 at 10:54
1

@Boob: If it works, give Tao the credit, he got it right in the first place. – Dennis Traub Sep 23 '11 at 10:55

score 1 · Answer 3 · answered Sep 23 '11 at 11:04

IMO, split it:

IQueryable<Car> query = ...
if(!string.IsNullOrEmpty(search.make))
    query = query.Where(c => c.make == search.make);
if(!string.IsNullOrEmpty(search.model))
    query = query.Where(c => c.model== search.model);

This produces the most appropriate TSQL, in that it won't include redundant WHERE clauses or additional parameters, allowing the RDBMS to optimise (separately) the "make", "model" and "make and model" queries.

score 0 · Answer 4 · answered Sep 23 '11 at 10:50

0

This should work.

.Where(c => 
    (search.make == null || c.make == search.make) &&
    (search.model == null || c.model == search.model))

answered Sep 23 '11 at 10:50

Chris Snowden

4,982
1
25
34

score 0 · Accepted Answer · answered Sep 23 '11 at 10:51

0

you could write something like this:

.Where(c => c.make == search.make ?? c.make && c.model == search.model ?? c.model)

answered Sep 23 '11 at 10:51

Paulo Santos

11,285
4
39
65

Thanks Paulo, can you explain what's happening here? I'm quite new to LINQ and LAMBDA stuff, haven't seen the "??" syntax used before? – Boob Sep 23 '11 at 11:09
The `??` is the coalesce operator. If the value on the left side is null it will use the value on the right side. It's equivalent to `(x == null ? y : x)` – Paulo Santos Sep 23 '11 at 11:20

score 0 · Answer 6 · answered Sep 23 '11 at 10:51

0

.Where(c => (string.IsNullOrEmpty(c.make) || c.make == search.make) && 
            (string.IsNullOrEmpty(c.model) || c.model == search.model))

answered Sep 23 '11 at 10:51

alexl

6,841
3
24
29

score 0 · Answer 7 · answered Sep 23 '11 at 10:51

0

.Where(c => (string.IsNullOrEmpty(search.make) || c.make == search.make) &&
            (string.IsNullOrEmpty(search.model) || c.model == search.model))

That's assuming the properties are strings.

answered Sep 23 '11 at 10:51

AndrewC

6,680
13
43
71

Actually, on re-reading the question it's probably not even necessary to call IsNullOrEmpty. Depends how you're creating your search object. – AndrewC Sep 23 '11 at 10:58

score 0 · Answer 8 · answered Sep 23 '11 at 10:52

0

Where(c => (search.make == null || c.make == search.make) && 
           (search.model == null || c.model == search.model))

answered Sep 23 '11 at 10:52

slawekwin

6,270
1
44
57

Smarter where clause?

8 Answers8