Linq2Entities + Entity Framework query optimization: .Where() vs. Linq where

I had a Linq query against my Entity Framework model that was something like:

from e1 in context.Entity1
from e2 in context.Entity2
from e3summary in
    from e3 in context.Entity3
    where e3.Field1 = value // <-- this is the line in question
    group e3 by new { e3.Field1, e3.Field2, e3.Field3 }
        into e3group
        select new
        {
            e3group.Key.Field1,
            e3group.Key.Field2,
            e3group.Key.Field3,
            Total = e3group.Sum(o => o.Field4)
        }
where
    // conditions on e1 and joining e1, e2, and e3summary
    ... 

select e1;

The SQL that was generated was selecting everything from my e3 table (actually a view in the database) as a derived table, then applying the where clause against the derived table, grouping that, and joining to my other results. That’s almost exactly what I wanted, except I thought I didn’t need the whole e3 view to be grouped (it’s 73M records in my test database, almost 800M in production). I was expecting the WHERE clause in my Linq query to be applied at the inner-most level, but instead I was getting (I’m including only the relevant part):

  • GROUP BY and count whether column is null
  • Datepart Calculation in Where clause
  • View output of 'print' statements using ADOConnection in Delphi
  • System.InvalidOperationException: Cannot perform CAS Asserts in Security Transparent methods
  • How to get the next number in a sequence
  • Error when using Spatial Index in SQL Server
  • ...
    INNER JOIN (SELECT
        [Extent3].[Field1] AS [K1],
        [Extent3].[Field2] AS [K2],
        [Extent3].[Field3] AS [K3],
        SUM([Extent3].Field4] AS [A1]
        FROM (SELECT
            [e3].[ID] AS [ID],
            [e3].[Field1] AS [Field1],
            [e3].[Field2] AS [Field2],
            [e3].[Field3] AS [Field3],
            [e3].[Field4] AS [Field4],
            [e3].[Field5] AS [Field5],
            [e3].[Field6] AS [Field6],
            [e3].[Field7] AS [Field7],
            [e3].[Field8] AS [Field8]
            FROM [dbo].[e3] AS [e3]) AS [Extent3]
                WHERE ([Extent3].[Field1] = @p__linq__0)
                GROUP BY [Extent3].[Field1], [Extent3].[Field2], [Extent3].[Field3] ) AS [GroupBy1]
        ...
    

    I changed my Linq query from

        from e3 in context.Entity3
        where e3.Field1 = value // <-- this is the line in question
    

    to

        from e3 in context.Entity3.Where(e => e.Field1 = value)
    

    and this created what I originally expected, the WHERE clause at the inner-most level:

            ...
            FROM [dbo].[e3] AS [e3] WHERE [e3].Field1] = @p__linq__0) AS [Extent3]
                GROUP BY [Extent3].[Field1], [Extent3].[Field2], [Extent3].[Field3] ) AS [GroupBy1]
    

    Why would there be a difference between applying a .Where([condition]) directly against the collection in my context vs. having a where [condition] in my Linq query? I would think this would get parsed into the expression tree the same way.

    P.S. On a sidenote, putting both queries into SQL Server Management Studio and comparing the query execution plan, I was surprised to find the execution plan was exactly the same either way. SQL’s query plan optimizer really is incredible!

  • Importing SQL Server's CONTAINS() as a model defined function
  • Table-per-type inheritance with the Entity Framework Reverse POCO Generator
  • Entity framework code first migrations, sql user permissions?
  • Entity Framework 6 DBContext with only a subset of all tables
  • Traditional SqlClient can find server instance but EF can't
  • How to enable CodeFirst to migrate schema changes against a replicated database?
  • One Solution collect form web for “Linq2Entities + Entity Framework query optimization: .Where() vs. Linq where”

    The difference between these queries is in representing constructs you used. The first query is evaluated as

     (from e3 in context.Entity3) where e3.Fied1 == value
    

    whereas second query is evaluated as

     from e3 in (context.Entity3.Where(e => e.Field1 == value))
    

    Dotted syntax takes precedence because it is taken as separate expression subtree which must be constructed and attached to expression tree of the outer query. You can think about it as subquery even it doesn’t have to be subquery at the end as shown in your example.

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.