What's the difference between multiple where clauses and && operator in LINQ-to-SQL?

Question

It looks like I can write a where x.a==1 && x.b==1 as

where x.a==1
where x.b==1

As I understand the latter turns into .Where(x => x.a == 1).Where(x => x.b ==1), but how does this translate to DB? Which would be better in terms of optimization? I can always look at the executed query from profiler but that would hardly be a generalization but more like a single empirical observation, which I don't want to rely on.

Going through System.Linq namespace with reflector is another option but then we would miss the chance to save many people from spending time on the same thing. I'll do it if I don't get any answers.

Answer 1

It's up to LINQ to SQL to do the right thing here. I would expect it to convert two "where" clauses into a single SQL clause with the two parts joined together with "AND".

Try both - but I very much doubt you'll see any difference in the generated SQL.

IMO you should always look at the generated SQL for anything non-trivial, but the way that LINQ works certainly encourages you to build up a large query by composing it out of small clauses - so I'd really expect it to Just Work.

Answer 2

DB side they are identical. It is just a side effect of Linq-to-SQL being composable (i.e. you can start off with one query and then add additional criteria to it, change projections etc).

Answer 3

The code:

where x.a==1
where x.b==1

or

where x.a==1 && x.b==1

is syntactic sugar for C# anyway. It will compile as a LINQ method chain of

Where(...).Where(...)

just as you guessed it probably would, so I really doubt there is any difference in the generated SQL. Try using a tool like Resharper from Jetbrains - it even offers intellisense that gives you the choice to auto-convert between the two to save you time re-writing it for testing.

My preference is to write really simple queries as method chains, (e.g. where there is only 1 collection/table involved and no joins) and anything more complicated in the more expressive Linq sugar-syntax.

Answer 4

Ok here are my findings after going through Reflector output for a while. LINQ-to-Objects combine the consecutive where predicates when using WhereArrayIterator or WhereListIterator causing it to ALMOST behave like && operator, but not as exactly:

When you use x.a==1 && x.b==1 the where clause translates into a Func<TSource, bool> looking like this:

bool daspredicate(TSource x)
{
    return x.a==1 && x.b==1
}

However, when you use consecutive Where clauses there is a slight performance penalty, at least from non-JITted IL-aspect. Here is how code looks like after combining:

bool predicate1(TSource x)
{
     return x.a==1;
}
bool predicate2(TSource x)
{
     return x.b==1;
}
bool daspredicate(TSource x)
{
    return predicate1(x) && predicate2(x);
}

As you can see this involves additional function call overhead. This can be quite expensive unless JIT inlines the functions. I'm sure it does a good job at it but we now know JIT's job becomes much easier if we combine our Where statements ourselves, unless necessary.

On the SQL-side of things though, the queries are the same. Even before execution, debugger evaluates the query object into the same SQL statement. I couldn't go too far in Linq namespace because things seemed much more complex, but since queries are the same, there should be no penalty unlike LINQ-to-objects example above.