What are the EF Rewrite Rules?

Question

When a LINQ query syntax expression is written with DBContext, the C# compiler does its usual magic of converting the query syntax into dot / extension syntax using its list of 18 transformation / term rewrite rules. Then when the query is executed, EF applies its own internal rewrite rules to produce a SQL expression.

Just like the article I linked above, I'd like to have a list of the rewrite rules that EF is applying. Where can I find that? If I know the EF rewrite rules, I can predict what SQL EF will generate for a given query, instead of having to wait until run-time to "see" the generated SQL.

For example, consider the following two queries:

var result = from c in context.Customers
             from a in c.Accounts
             where c.ID > 2
             select a;

var result = from c in context.Customers
             where c.ID > 2
             from a in c.Accounts
             select a;

When the C# compiler finishes its own rewrite rules, the above queries are transformed into dot-notation with the following respective formats:

SelectMany(...).Where(...).Select(...); //for the first query

Where(...).SelectMany(...); // for the second query

After these conversions, EF gets to work applying its own rewrite rules. But EF has a single normalized form for both of the above queries. In other words, both queries will produce identical results; either query generates the following SQL statement:

SELECT 
    [Extent1].[ID] AS [ID], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Stat_ID] AS [Stat_ID]
    FROM  [dbo].[Customers] AS [Extent1]
    INNER JOIN [dbo].[Accounts] AS [Extent2] ON [Extent1].[ID] = [Extent2].[Customer_ID]
    WHERE [Extent2].[ID] > 2

Without knowing the EF rewrite rules, I could not have predicted that. I simply had to execute and debug code to make that observation.

So again, where can I find a list of the rewrite rules that EF is applying?

I am also curious what implementation strategy EF has for applying the rules. Here is an article discussing several rewrite rule strategies. Perhaps I could discover this by studying the EF source code but, which I am doing, but I'm not there yet.

Answer 1

Converting linq expression into SQL is unfortunately very complicated and is probably not so easily split into a set of simple discrete rules.

For example things such as linq GroupBy and Join don't tend to map one to one to the SQL keywords by the same name as they behave slightly differently in linq as they do in SQL (linq GroupBy returns all items in the group rather than just aggregates and linq Join is more like SQL Server CROSS APPLY). The actual transformation depends highly on what is selected out at the end of the query, meaning you usually have to process the whole expression rather than a simple mapping of linq method to SQL keyword.

It is well worth reading Matt Warrens articles on building a linq provider http://blogs.msdn.com/b/mattwar/archive/2008/11/18/linq-links.aspx. It may not be exactly what EF is doing but gives you an idea of what steps are involved in converting linq expression to sql and the complexities involved.

Answer 2

Everything depends on database connector, for example if you want to see generation rules for SQL server, you can look at EF sources. But i won't recommend you to relay on this rules, they might change in next version. I work with MySql and updated connector 3 times and every time i am facing problem that EF generates different queries and. For example expression:

    .Where(p => new List<int> {1, 2}.Contains(p.value))

in 6.5.4 generated as:

   WHERE 1 = `value` OR 2 = `value`

And in 6.7.4 generated as:

   WHERE `value` IN (1, 2)

P.S. if you want to see generation rules for MySql, check MySql.Data.Entities sources