Smarter where clause?

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 开发者_Python百科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?

---

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.

---

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

---

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.

---

you could write something like this:

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

---

This should work.

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

---

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

---

.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.

---

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