none
LINQ to Entity statement generates nested SQL statement... why? RRS feed

  • Question

  • Hi,

    I am using VS2010, EF4, c# with MS SQL Server 2008. I started verifying how my LINQ to Object statements are getting converted to SQL statements. To my surprise, here is one of the senarios.

    This is my LINQ :

    from i in tblInventory

    group i by new

    {i.ProductFID, i.SizeFID, i.WeightFID, i.WarehouseFID, i.ProductDesc }

    into g

    select new

     {

    ProductID = g.Key.ProductFID.Value,

    SizeID = g.Key.SizeFID.Value,

    WeightID = g.Key.WeightFID.Value,

    WarehouseID = g.Key.WarehouseFID.Value,

    ProductDesc = g.Key.ProductDesc,

    OnHandQty = g.Sum(p => p.OnHand)

    }

    And this is the result I get from SQL Query Profiler when converting to SQL :

     SELECT

    1 AS [C1],

    [GroupBy1].[K1] AS [ProductFID],

    [GroupBy1].[K3] AS [SizeFID],

    [GroupBy1].[K4] AS [WeightFID],

    [GroupBy1].[K2] AS [ProductDesc],

    [GroupBy1].[K5] AS [WarehouseFID],

    [GroupBy1].[A1] AS [C2]

    FROM ( SELECT

              [Extent1].[ProductFID] AS [K1],

              [Extent1].[ProductDesc] AS [K2],

              [Extent1].[SizeFID] AS [K3],

              [Extent1].[WeightFID] AS [K4],

              [Extent1].[WarehouseFID] AS [K5],

              SUM([Extent1].[OnHand]) AS [A1]

              FROM [dbo].[tblInventory] AS [Extent1]

              GROUP BY [Extent1].[ProductFID], [Extent1].[ProductDesc], [Extent1].[SizeFID], [Extent1].[WeightFID], [Extent1].[WarehouseFID]

    )  AS [GroupBy1]

    I expected to get the following SQL query :

    SELECT ProductFID, SizeFID, WeightFID, WarehouseFID, ProductDesc, SUM(OnHand) AS OnHandQty
    FROM   tblInventory

    GROUP BY ProductFID, SizeFID, WeightFID, WarehouseFID, ProductDesc

    It seems the generated SQL statement isn't very efficient... does my LINQ synthax need to be optimized?

    Thanks

    Tuesday, August 23, 2011 7:24 PM

Answers

  • your linq to sql statement looks OK. if i were you, i also code like this. but i think your concern was focused on sql generation. it auto generates and optimizes. check here. http://msdn.microsoft.com/en-us/library/ee794148.aspx

    out of curious, did your applicaion run very slow or other concerns?

    Thanks,

    Werewolf,


    Just a newbie for everything.
    • Marked as answer by Kassem Bashir Friday, August 26, 2011 2:20 PM
    Thursday, August 25, 2011 9:34 AM
  • SQL Server's optimizer will eliminate this, and both (A) and (B) should get the same execution plan in SQL Server. I agree that (A) is friendlier for the human eye, but the SQL optimizer is smart enough to see that they are both the same.

    You can compare the execution plan for both queries by running them in SSMS with the "include actual execution plan" option enabled. See http://msdn.microsoft.com/en-us/library/ms178071.aspx


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by Kassem Bashir Friday, August 26, 2011 2:20 PM
    Friday, August 26, 2011 12:50 AM

All replies

  • your linq to sql statement looks OK. if i were you, i also code like this. but i think your concern was focused on sql generation. it auto generates and optimizes. check here. http://msdn.microsoft.com/en-us/library/ee794148.aspx

    out of curious, did your applicaion run very slow or other concerns?

    Thanks,

    Werewolf,


    Just a newbie for everything.
    • Marked as answer by Kassem Bashir Friday, August 26, 2011 2:20 PM
    Thursday, August 25, 2011 9:34 AM
  • Hi,

    Likely because they have to come up with some SQL code that will work on more complex queries. For example you could group on expressions and use also expressions when projecting your final result. So you have one section for both even if in this particular case it could be combined (which could be itself costly to implement).

    As Werewolfs said I doubt it makes a difference here and it's likely optimized anyway by the SQL Server engine...

     


    Please always mark whatever response solved your issue so that the thread is properly marked as "Answered".
    Thursday, August 25, 2011 11:42 AM
  • Werewolf,

    If I run SQL Server Profiler, I can evaluate the SQL statement being executed by SQL Server.

    In that case, doesn't SQL Server process query A more efficiently than query B?

    A)
    SELECT ProductFID, SizeFID, WeightFID, WarehouseFID, ProductDesc, SUM(OnHand) AS OnHandQty
    FROM   tblInventory

    GROUP BY ProductFID, SizeFID, WeightFID, WarehouseFID, ProductDesc

     

    B)
    SELECT

    1 AS [C1],

    [GroupBy1].[K1] AS [ProductFID],

    [GroupBy1].[K3] AS [SizeFID],

    [GroupBy1].[K4] AS [WeightFID],

    [GroupBy1].[K2] AS [ProductDesc],

    [GroupBy1].[K5] AS [WarehouseFID],

    [GroupBy1].[A1] AS [C2]

    FROM ( SELECT

              [Extent1].[ProductFID] AS [K1],

              [Extent1].[ProductDesc] AS [K2],

              [Extent1].[SizeFID] AS [K3],

              [Extent1].[WeightFID] AS [K4],

              [Extent1].[WarehouseFID] AS [K5],

              SUM([Extent1].[OnHand]) AS [A1]

              FROM [dbo].[tblInventory] AS [Extent1]

              GROUP BY [Extent1].[ProductFID], [Extent1].[ProductDesc], [Extent1].[SizeFID], [Extent1].[WeightFID], [Extent1].[WarehouseFID]

    )  AS [GroupBy1]

    Thursday, August 25, 2011 1:36 PM
  • SQL Server's optimizer will eliminate this, and both (A) and (B) should get the same execution plan in SQL Server. I agree that (A) is friendlier for the human eye, but the SQL optimizer is smart enough to see that they are both the same.

    You can compare the execution plan for both queries by running them in SSMS with the "include actual execution plan" option enabled. See http://msdn.microsoft.com/en-us/library/ms178071.aspx


     
       Cool tools for Linq-to-SQL and Entity Framework 4:
     huagati.com/dbmltools - Visual Studio add-in with loads of new features for the Entity Framework and Linq-to-SQL designers
     huagati.com/L2SProfiler - Runtime SQL query profiler for Linq-to-SQL and Entity Framework v4
    • Marked as answer by Kassem Bashir Friday, August 26, 2011 2:20 PM
    Friday, August 26, 2011 12:50 AM
  • "SQL Server's optimizer will eliminate this"

    Well if all coders think that another layer is optimizing the mess they do... I don't think that we should relay to much on optimizations from other layers. Clean generated code was always a good idea since clean code is in most cases also fast.

    And BTW: This can actually mess up into highly nested queries that can't be optimized anymore. Try to include 5-6 joins into the query with some where-clauses.

    Thursday, December 8, 2011 1:14 PM