News, examples, tips, ideas and plans.
Thoughts around ORM, .NET and SQL databases.

Thursday, July 01, 2010

Local collections in Entity Framework: analyzing generated SQL

Alexey Gamzov just sent me an example showing how EF deals with large local collections.

Query:
from edge in session.GetAll<Edge>()
join nodeId in nodes on edge.ChildId equals nodeId
select edge.ParentId
nodes is local collection of 60+ GUIDs (IEnumerable<Guid>).

SQL:
SELECT
[Extent1].[ParentId] AS [ParentId]
FROM  [dbo].[Edges] AS [Extent1]
INNER JOIN  (SELECT
  [UnionAll66].[C1] AS [C1]
  FROM  (SELECT
    [UnionAll65].[C1] AS [C1]
    FROM  (SELECT
      [UnionAll64].[C1] AS [C1]
      FROM  (SELECT
        [UnionAll63].[C1] AS [C1]
        FROM  (SELECT
          [UnionAll62].[C1] AS [C1]
-- 60 more similar constructions
              FROM  (SELECT
                [UnionAll1].[C1] AS [C1]
                FROM  (SELECT
                  cast('5b029492-7aaf-4253-a347-a85eca6d08e0' as uniqueidentifier) AS [C1]
                  FROM  ( SELECT 1 AS X ) AS [SingleRowTable1]
                UNION ALL
                  SELECT
                  cast('bc83a784-87d8-45a0-b3f9-5a74be942ee1' as uniqueidentifier) AS [C1]
                  FROM  ( SELECT 1 AS X ) AS [SingleRowTable2]) AS [UnionAll1]
              UNION ALL
                SELECT
                cast('ee7acdbb-73d4-4228-bcc6-b83dcc22d286' as uniqueidentifier) AS [C1]
                FROM  ( SELECT 1 AS X ) AS [SingleRowTable3]) AS [UnionAll2]
-- 60 more similar constructions  
          UNION ALL  
            SELECT   
            cast('e6c729f2-fd19-43e1-9209-094d165cd551' as uniqueidentifier) AS [C1]  
            FROM  ( SELECT 1 AS X ) AS [SingleRowTable63]) AS [UnionAll62]  
        UNION ALL  
          SELECT   
          cast('ed1c2991-75bc-4123-b524-ce9f9cee090a' as uniqueidentifier) AS [C1]  
          FROM  ( SELECT 1 AS X ) AS [SingleRowTable64]) AS [UnionAll63]  
      UNION ALL  
        SELECT   
        cast('0d5809e3-c841-4e1a-956d-ffca42446ac9' as uniqueidentifier) AS [C1]  
        FROM  ( SELECT 1 AS X ) AS [SingleRowTable65]) AS [UnionAll64]  
    UNION ALL  
      SELECT   
      cast('17574132-f67c-4622-aba9-abcde209a886' as uniqueidentifier) AS [C1]  
      FROM  ( SELECT 1 AS X ) AS [SingleRowTable66]) AS [UnionAll65]  
  UNION ALL  
    SELECT   
    cast('cbadcf79-0b51-4f96-8c95-b9576f31878c' as uniqueidentifier) AS [C1]  
    FROM  ( SELECT 1 AS X ) AS [SingleRowTable67]) AS [UnionAll66]  
UNION ALL  
  SELECT   
  cast('55c50611-3745-47b3-af50-fdec7458b19d' as uniqueidentifier) AS [C1]  
  FROM  ( SELECT 1 AS X ) AS [SingleRowTable68]) AS [UnionAll67] ON [Extent1].[ChildId] = [UnionAll67].[C1]
Obviously, it fails - maximum subquery nesting level on SQL Server is 32. Luckily, it was possible to get rid of join in this case:

Query:
from edge in session.GetAll<Edge>()
where nodes.Contains(edge.ChildId)
select edge.ParentId
SQL:
SELECT 
[Extent1].[ParentId] AS [ParentId]
FROM [dbo].[Edges] AS [Extent1]
WHERE [Extent1].[ChildId] IN (
  cast('5b029492-7aaf-4253-a347-a85eca6d08e0' as uniqueidentifier),
-- About 60 more similar constructions
  cast('55c50611-3745-47b3-af50-fdec7458b19d' as uniqueidentifier)
)
It works ;) Although it's a bit strange they don't parameterize GUIDs here.

No comments:

Post a Comment