LINQ: adding where clause only when a value is not null

Question

I know a typical way is like this:

IQueryable query = from staff in dataContext.Staffs; if(name1 != null) {      query = from staff in query where (staff.name == name1); } 

However, from a program we took over from other developers, we saw code like this:

IQueryable query = from staff in dataContext.Staffs; query = from staff in query where (name1 == null || staff.name == name1); 

If this is a normal SQL statement, I would definitely say that the 2nd one is a bad practice. Because it adds a meaningless where clause to the query when name1 is null.

But I am new to LINQ, so I am not sure if LINQ is different?

Answer 1

you can write it like

IQueryable query = from staff in dataContext.Staffs; query = from staff in query where (name1 != null && staff.name == name1); 

This way second part of your condition will not be evaluated if your first condition evaluates to false

Update:
if you write

IQueryable query = from staff in dataContext.Staffs;     query = from staff in query where (name1 == null || staff.name == name1); 

and name1 is null second part of your condition will not be evaluated since or condition only requires one condition to return true

plz see this link for further detail

Answer 2

Often this sort of thing feels smoother to write using the fluent syntax, rather than the query syntax.

e.g.

IQueryable query = dataContext.Staffs; if(name1 != null) {      query = query.Where(x => x.name == name1); } 

So if name1 is null, you just don't do any Where() call. If you have multiple different filters, all of which may or may not be required, and perhaps various different sort orders, I find this becomes a lot more manageable.

Edit for alex: OK, I was answering the question about adding a where clause only when a value is not null. In response to the other part of the question, I tried this out with Entity Framework 4 to see what SQL that LINQ produced. You do this by casting query to an ObjectQuery and calling .ToTraceString(). The results were that the WHERE clause came out as follows:

WHERE @p__linq__0 IS NULL OR [Extent1].[name] = @p__linq__1 

So, yes, it's classic bad SQL, if you have an index on the name column, don't expect it to be used.

Edit #2: Tried this again using LINQ to SQL rather than Entity Framework, with rather different results. This time, trying the query with name1 being null results in no WHERE clause at all, as you'd hope; trying it with name1 being "a" resulted in a simple WHERE [t0].[name] = @p0 and @p0 sent as "a". Entity Framework does not seem to optimize thus. That's a bit worrying.