EntityFramework is somewhat where

Question

I am wondering if I use several Where(...) methods one after another, this EntityFramework is smart enough to combine it in the 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 wrote:

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

Are there any backstage optimizations for the Where clause?

Answer 1

Yes there is. This is not the Entity Framework that does this. Actually, the task of SQL Provider Factory is to compile a query for the database. Depending on the database used, this code will be from different sources.

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

Within this library or similar libraries, a recursive visitor pattern is often used to navigate through a particular graph of expression tree objects to create the most efficient query.

Using multiple situations where clauses are very easy to detect and optimize, the place where these types of libraries have problems is usually associated with deeply nested projections.

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

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