Alexey Gamzov just sent me an example showing how EF deals with large local collections.
Query:
SQL:
Query:
from edge in session.GetAll<Edge>() join nodeId in nodes on edge.ChildId equals nodeId select edge.ParentIdnodes 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.ParentIdSQL:
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