Linq To SQL Provider Rewriting My Queries!
From the title of the blog posting, readers must be thinking, I have already given away control of writing SQL to Linq providers. Now do I get to loose control over writing my Linq queries? Well in reality not. What I meant is Linq provider analyzes the queries and if it finds repetitive filters or duplicate checks or even for that matter finds that a query can be written efficiently based on the data available at runtime which was not be available during compile time, Linq providers will fix the query and translate the fixed version of the query to SQL to be sent to the database. Below is an example that demonstrates Linq provider's ability to modify Linq queries for better performance and less redundancy.
In the above example, I have a class called ProductSearch which searches for products in a certain category that is passed into the method called GetProducts. I have a very simple business rule applied in this method which dictates how to get products for a category. I am stating that if the category passed in is Beverage than products with UnitsInStock greater than 0 and SupplierId of 1 should only be fetched. If the Category is not beverage than fetch all products in that category if they are not discontinued. Based on how I have written the query I would expect Linq to SQL provider to do a full translation of my query into SQL. SQL below shows the query conversion I would expect Linq to SQL provider to do.
The SQL shown above is supposedly an exact translation of what my Linq query intended to do. Notice that in my manual query, I am leaving up to the SQL server to determine which part of the OR statement to execute based on category name that I am passing. However Linq to SQL provider at run time knows what category's products that I am looking for because I am passing in the category to the GetProducts method. Having this information available to Linq to SQL provider, it realizes that only 1 part of where condition needs to be executed based on the category I am dealing. Meaning, in the above query, if we are looking for products in Beverage Category only the predicate with Beverage Category needs to be executed. Otherwise the or portion of the predicate needs to be executed. There is no reason to pass in both predicates and let SQL server determine which part of the or statement needs to be evaluated since this information available to provider. Therefore Linq provider removes the extra check in the query before translating it to SQL. Below are two SQL statements generated by Linq provider; one for Beverage category and other for categories that are not beverage.
In the above generated SQL for Beverage Category, we are just applying the Beverage Category filter which is to show Products with Beverage Category with UnitsInStock greater than 0 and SupplierId 1
To retrieve products for any category other than beverage, I am stating to display products which are not discontinued.