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):

  • How to create a sequential range in SQL?
  • Having Troubles with UnixODBC, FreeTDS, and PyODBC
  • IF UPDATE() in SQL server trigger
  • SQL Sum duration by hour and day
  • php dropdown show selected but one value by defaut
  • How can I pass and process an array of varchars as parameters to/within a SQL Server stored procedure parameter?
  • ...
    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!

  • SQL Server : two foreign keys pointing to one table. Error: Introducing FOREIGN KEY constraint may cause cycles or multiple cascade paths
  • Entity Framework One To One Relationship WithRequiredPrinciple saving entities without dependent entity
  • Two foreign keys reference one table and null able foreign key
  • Rounding UP in SQL Server?
  • SQL rounding mechanism
  • Cannot get rid of “physical connection is not usable” exception
  • 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.