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

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.

EDIT: I've seen instances where multiple where statements resulting in nested sub-queries on SQL server. I think it's better to stick to single where statements whenever you can to be on the safe side.