EntityFramework Multiple Where

Question

I am wondering, if I use multiple Where(...) methods one after the other, is EntityFramework smart enough to combine it in a resulting query. Let's say I have:

context.Items
    .Where(item => item.Number > 0)
    .Where(item => item.Number < 5)
    .ToList();

Will the resulting SQL query be the same as if I write:

context.Items
    .Where(item => item.Number > 0 && item.Number < 5)
    .ToList();

Are there any behind-the-scenes optimizations for multiple Where clause?

Answer 1

Yes, there are. It is not Entity Framework that does this. It is actually the SQL Provider Factory's job to compose the query for the database. Depending on the database you use, this code will be from different sources.

For MSSQL, the code is Microsoft's and is in the library System.Data.SqlClient. If you look at your connection element in your web.config, you should notice the attribute "providerName".

Inside of this library, or libraries similar to it, often a recursive visitor pattern is employed to navigate through the defined Expression Tree object graph in order to produce the most efficient query possible.

Using multiple where clauses is very easy to detect and optimize, the place that these sort of libraries have trouble tends to be with deeply nested projections.

You can see the SQL produced by your query if you use

context.Items
.Where(item => item.Number > 0)
.Where(item => item.Number < 5)
.ToString();