Menu
I wasn't expecting a generated query like this...
let go back, if I keep one include, the query look good, it does a simple left join
Query:
using (var db = new Context())
{
var data = db.MainTables.Include(x => x.LookupTables)
.Where(d => d.MainId == 10)
.FirstOrDefault();
}
SQL generated:
Opened connection at 2014-05-12 17:37:10 -04:00
SELECT
[Project1].[MainId] AS [MainId],
[Project1].[C1] AS [C1],
[Project1].[LookupId] AS [LookupId]
FROM ( SELECT
[Limit1].[MainId] AS [MainId],
[Extent2].[LookupId] AS [LookupId],
CASE WHEN ([Extent2].[MainId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
FROM (SELECT TOP (1) [Extent1].[MainId] AS [MainId]
FROM [dbo].[MainTable] AS [Extent1]
WHERE 10 = [Extent1].[MainId] ) AS [Limit1]
LEFT OUTER JOIN [dbo].[MainTable_LookupTable] AS [Extent2] ON [Limit1].[MainId] = [Extent2].[MainId]
) AS [Project1]
ORDER BY [Project1].[MainId] ASC, [Project1].[C1] ASC
-- Executing at 2014-05-12 17:37:11 -04:00
-- Completed in 11 ms with result: SqlDataReader
Closed connection at 2014-05-12 17:37:11 -04:00
if i have more than one, the query get crazy like hell with UNION ALL...
is that an expected behavior? if yes, is it possible to keep the behavior of one include while using more than one, simple left join for all of them?
Model:
Query:
using (var db = new Context())
{
var data = db.MainTables.Include(x => x.LookupTables)
.Include(x => x.MainTable_MoreData)
.Include(x => x.MaintTable_DataLookup)
.Include(x => x.MainTable_EvenMoreData)
.Where(d => d.MainId == 10)
.FirstOrDefault()
}
SQL generated:
Opened connection at 2014-05-12 18:00:56 -04:00
SELECT
[UnionAll3].[C2] AS [C1],
[UnionAll3].[C3] AS [C2],
[UnionAll3].[C4] AS [C3],
[UnionAll3].[C5] AS [C4],
[UnionAll3].[C6] AS [C5],
[UnionAll3].[C7] AS [C6],
[UnionAll3].[C8] AS [C7],
[UnionAll3].[C9] AS [C8],
[UnionAll3].[C10] AS [C9],
[UnionAll3].[C11] AS [C10],
[UnionAll3].[C12] AS [C11],
[UnionAll3].[C13] AS [C12],
[UnionAll3].[C1] AS [C13],
[UnionAll3].[C14] AS [C14],
[UnionAll3].[C15] AS [C15],
[UnionAll3].[C16] AS [C16],
[UnionAll3].[C17] AS [C17],
[UnionAll3].[C18] AS [C18],
[UnionAll3].[C19] AS [C19],
[UnionAll3].[C20] AS [C20],
[UnionAll3].[C21] AS [C21],
[UnionAll3].[C22] AS [C22],
[UnionAll3].[C23] AS [C23],
[UnionAll3].[C24] AS [C24],
[UnionAll3].[C25] AS [C25],
[UnionAll3].[C26] AS [C26],
[UnionAll3].[C27] AS [C27],
[UnionAll3].[C28] AS [C28],
[UnionAll3].[C29] AS [C29],
[UnionAll3].[C30] AS [C30],
[UnionAll3].[C31] AS [C31],
[UnionAll3].[C32] AS [C32],
[UnionAll3].[C33] AS [C33],
[UnionAll3].[C34] AS [C34],
[UnionAll3].[C35] AS [C35],
[UnionAll3].[C36] AS [C36],
[UnionAll3].[C37] AS [C37],
[UnionAll3].[C38] AS [C38],
[UnionAll3].[C39] AS [C39],
[UnionAll3].[C40] AS [C40],
[UnionAll3].[C41] AS [C41],
[UnionAll3].[C42] AS [C42],
[UnionAll3].[C43] AS [C43],
[UnionAll3].[C44] AS [C44],
[UnionAll3].[C45] AS [C45],
[UnionAll3].[C46] AS [C46],
[UnionAll3].[C47] AS [C47],
[UnionAll3].[C48] AS [C48],
[UnionAll3].[C49] AS [C49],
[UnionAll3].[C50] AS [C50],
[UnionAll3].[C51] AS [C51]
FROM (SELECT
[UnionAll2].[C1] AS [C1],
[UnionAll2].[C2] AS [C2],
[UnionAll2].[C3] AS [C3],
[UnionAll2].[C4] AS [C4],
[UnionAll2].[C5] AS [C5],
[UnionAll2].[C6] AS [C6],
[UnionAll2].[C7] AS [C7],
[UnionAll2].[C8] AS [C8],
[UnionAll2].[C9] AS [C9],
[UnionAll2].[C10] AS [C10],
[UnionAll2].[C11] AS [C11],
[UnionAll2].[C12] AS [C12],
[UnionAll2].[C13] AS [C13],
[UnionAll2].[C14] AS [C14],
[UnionAll2].[C15] AS [C15],
[UnionAll2].[C16] AS [C16],
[UnionAll2].[C17] AS [C17],
[UnionAll2].[C18] AS [C18],
[UnionAll2].[C19] AS [C19],
[UnionAll2].[C20] AS [C20],
[UnionAll2].[C21] AS [C21],
[UnionAll2].[C22] AS [C22],
[UnionAll2].[C23] AS [C23],
[UnionAll2].[C24] AS [C24],
[UnionAll2].[C25] AS [C25],
[UnionAll2].[C26] AS [C26],
[UnionAll2].[C27] AS [C27],
[UnionAll2].[C28] AS [C28],
[UnionAll2].[C29] AS [C29],
[UnionAll2].[C30] AS [C30],
[UnionAll2].[C31] AS [C31],
[UnionAll2].[C32] AS [C32],
[UnionAll2].[C33] AS [C33],
[UnionAll2].[C34] AS [C34],
[UnionAll2].[C35] AS [C35],
[UnionAll2].[C36] AS [C36],
[UnionAll2].[C37] AS [C37],
[UnionAll2].[C38] AS [C38],
[UnionAll2].[C39] AS [C39],
[UnionAll2].[C40] AS [C40],
[UnionAll2].[C41] AS [C41],
[UnionAll2].[C42] AS [C42],
[UnionAll2].[C43] AS [C43],
[UnionAll2].[C44] AS [C44],
[UnionAll2].[C45] AS [C45],
[UnionAll2].[C46] AS [C46],
[UnionAll2].[C47] AS [C47],
[UnionAll2].[C48] AS [C48],
[UnionAll2].[C49] AS [C49],
[UnionAll2].[C50] AS [C50],
[UnionAll2].[C51] AS [C51]
FROM (SELECT
[UnionAll1].[C1] AS [C1],
[UnionAll1].[MainId] AS [C2],
[UnionAll1].[MainId1] AS [C3],
[UnionAll1].[Field1] AS [C4],
[UnionAll1].[Field11] AS [C5],
[UnionAll1].[Field12] AS [C6],
[UnionAll1].[Field13] AS [C7],
[UnionAll1].[Field14] AS [C8],
[UnionAll1].[Field15] AS [C9],
[UnionAll1].[Field16] AS [C10],
[UnionAll1].[Field17] AS [C11],
[UnionAll1].[Field18] AS [C12],
[UnionAll1].[Field19] AS [C13],
[UnionAll1].[LookupId] AS [C14],
[UnionAll1].[Field161] AS [C15],
[UnionAll1].[Field151] AS [C16],
[UnionAll1].[Field141] AS [C17],
[UnionAll1].[Field131] AS [C18],
[UnionAll1].[Field121] AS [C19],
[UnionAll1].[Field111] AS [C20],
[UnionAll1].[Field110] AS [C21],
[UnionAll1].[C2] AS [C22],
[UnionAll1].[C3] AS [C23],
[UnionAll1].[C4] AS [C24],
[UnionAll1].[C5] AS [C25],
[UnionAll1].[C6] AS [C26],
[UnionAll1].[C7] AS [C27],
[UnionAll1].[C8] AS [C28],
[UnionAll1].[C9] AS [C29],
[UnionAll1].[C10] AS [C30],
[UnionAll1].[C11] AS [C31],
[UnionAll1].[C12] AS [C32],
[UnionAll1].[C13] AS [C33],
[UnionAll1].[C14] AS [C34],
[UnionAll1].[C15] AS [C35],
[UnionAll1].[C16] AS [C36],
[UnionAll1].[C17] AS [C37],
[UnionAll1].[C18] AS [C38],
[UnionAll1].[C19] AS [C39],
[UnionAll1].[C20] AS [C40],
[UnionAll1].[C21] AS [C41],
[UnionAll1].[C22] AS [C42],
[UnionAll1].[C23] AS [C43],
[UnionAll1].[C24] AS [C44],
[UnionAll1].[C25] AS [C45],
[UnionAll1].[C26] AS [C46],
[UnionAll1].[C27] AS [C47],
[UnionAll1].[C28] AS [C48],
[UnionAll1].[C29] AS [C49],
[UnionAll1].[C30] AS [C50],
[UnionAll1].[C31] AS [C51]
FROM (SELECT
CASE WHEN ([Join1].[MainId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Limit1].[MainId] AS [MainId],
[Limit1].[MainId] AS [MainId1],
[Limit1].[Field1] AS [Field1],
[Limit1].[Field11] AS [Field11],
[Limit1].[Field12] AS [Field12],
[Limit1].[Field13] AS [Field13],
[Limit1].[Field14] AS [Field14],
[Limit1].[Field15] AS [Field15],
[Limit1].[Field16] AS [Field16],
[Limit1].[Field17] AS [Field17],
[Limit1].[Field18] AS [Field18],
[Limit1].[Field19] AS [Field19],
[Join1].[LookupId1] AS [LookupId],
[Join1].[Field16] AS [Field161],
[Join1].[Field15] AS [Field151],
[Join1].[Field14] AS [Field141],
[Join1].[Field13] AS [Field131],
[Join1].[Field12] AS [Field121],
[Join1].[Field11] AS [Field111],
[Join1].[Field1] AS [Field110],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS varchar(1)) AS [C5],
CAST(NULL AS varchar(1)) AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS varchar(1)) AS [C10],
CAST(NULL AS int) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
CAST(NULL AS varchar(1)) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS varchar(1)) AS [C17],
CAST(NULL AS varchar(1)) AS [C18],
CAST(NULL AS int) AS [C19],
CAST(NULL AS int) AS [C20],
CAST(NULL AS int) AS [C21],
CAST(NULL AS varchar(1)) AS [C22],
CAST(NULL AS varchar(1)) AS [C23],
CAST(NULL AS varchar(1)) AS [C24],
CAST(NULL AS varchar(1)) AS [C25],
CAST(NULL AS varchar(1)) AS [C26],
CAST(NULL AS varchar(1)) AS [C27],
CAST(NULL AS varchar(1)) AS [C28],
CAST(NULL AS varchar(1)) AS [C29],
CAST(NULL AS varchar(1)) AS [C30],
CAST(NULL AS int) AS [C31]
FROM (SELECT TOP (1) [Extent1].[MainId] AS [MainId], [Extent1].[Field1] AS [Field1], [Extent1].[Field11] AS [Field11], [Extent1].[Field12] AS [Field12], [Extent1].[Field13] AS [Field13], [Extent1].[Field14] AS [Field14], [Extent1].[Field15] AS [Field15], [Extent1].[Field16] AS [Field16], [Extent1].[Field17] AS [Field17], [Extent1].[Field18] AS [Field18], [Extent1].[Field19] AS [Field19]
FROM [dbo].[MainTable] AS [Extent1]
WHERE 10 = [Extent1].[MainId] ) AS [Limit1]
LEFT OUTER JOIN (SELECT [Extent2].[MainId] AS [MainId], [Extent3].[LookupId] AS [LookupId1], [Extent3].[Field16] AS [Field16], [Extent3].[Field15] AS [Field15], [Extent3].[Field14] AS [Field14], [Extent3].[Field13] AS [Field13], [Extent3].[Field12] AS [Field12], [Extent3].[Field11] AS [Field11], [Extent3].[Field1] AS [Field1]
FROM [dbo].[MainTable_LookupTable] AS [Extent2]
INNER JOIN [dbo].[LookupTable] AS [Extent3] ON [Extent3].[LookupId] = [Extent2].[LookupId] ) AS [Join1] ON [Limit1].[MainId] = [Join1].[MainId]
UNION ALL
SELECT
2 AS [C1],
[Limit2].[MainId] AS [MainId],
[Limit2].[MainId] AS [MainId1],
[Limit2].[Field1] AS [Field1],
[Limit2].[Field11] AS [Field11],
[Limit2].[Field12] AS [Field12],
[Limit2].[Field13] AS [Field13],
[Limit2].[Field14] AS [Field14],
[Limit2].[Field15] AS [Field15],
[Limit2].[Field16] AS [Field16],
[Limit2].[Field17] AS [Field17],
[Limit2].[Field18] AS [Field18],
[Limit2].[Field19] AS [Field19],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS varchar(1)) AS [C5],
CAST(NULL AS varchar(1)) AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
[Extent5].[DataId] AS [DataId],
[Extent5].[MainId] AS [MainId2],
[Extent5].[SomeData] AS [SomeData],
[Extent5].[Field1] AS [Field110],
[Extent5].[Field11] AS [Field111],
[Extent5].[Field12] AS [Field121],
[Extent5].[Field13] AS [Field131],
[Extent5].[Field14] AS [Field141],
[Extent5].[Field15] AS [Field151],
CAST(NULL AS int) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
CAST(NULL AS varchar(1)) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS varchar(1)) AS [C17],
CAST(NULL AS int) AS [C18],
CAST(NULL AS int) AS [C19],
CAST(NULL AS int) AS [C20],
CAST(NULL AS varchar(1)) AS [C21],
CAST(NULL AS varchar(1)) AS [C22],
CAST(NULL AS varchar(1)) AS [C23],
CAST(NULL AS varchar(1)) AS [C24],
CAST(NULL AS varchar(1)) AS [C25],
CAST(NULL AS varchar(1)) AS [C26],
CAST(NULL AS varchar(1)) AS [C27],
CAST(NULL AS varchar(1)) AS [C28],
CAST(NULL AS varchar(1)) AS [C29],
CAST(NULL AS int) AS [C30]
FROM (SELECT TOP (1) [Extent4].[MainId] AS [MainId], [Extent4].[Field1] AS [Field1], [Extent4].[Field11] AS [Field11], [Extent4].[Field12] AS [Field12], [Extent4].[Field13] AS [Field13], [Extent4].[Field14] AS [Field14], [Extent4].[Field15] AS [Field15], [Extent4].[Field16] AS [Field16], [Extent4].[Field17] AS [Field17], [Extent4].[Field18] AS [Field18], [Extent4].[Field19] AS [Field19]
FROM [dbo].[MainTable] AS [Extent4]
WHERE 10 = [Extent4].[MainId] ) AS [Limit2]
INNER JOIN [dbo].[MainTable_MoreData] AS [Extent5] ON [Limit2].[MainId] = [Extent5].[MainId]) AS [UnionAll1]
UNION ALL
SELECT
3 AS [C1],
[Limit3].[MainId] AS [MainId],
[Limit3].[MainId] AS [MainId1],
[Limit3].[Field1] AS [Field1],
[Limit3].[Field11] AS [Field11],
[Limit3].[Field12] AS [Field12],
[Limit3].[Field13] AS [Field13],
[Limit3].[Field14] AS [Field14],
[Limit3].[Field15] AS [Field15],
[Limit3].[Field16] AS [Field16],
[Limit3].[Field17] AS [Field17],
[Limit3].[Field18] AS [Field18],
[Limit3].[Field19] AS [Field19],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS varchar(1)) AS [C5],
CAST(NULL AS varchar(1)) AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS int) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
CAST(NULL AS varchar(1)) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS varchar(1)) AS [C17],
CAST(NULL AS varchar(1)) AS [C18],
[Extent7].[MainId] AS [MainId2],
[Extent7].[Data] AS [Data],
[Extent7].[Field1] AS [Field110],
[Extent7].[Field11] AS [Field111],
[Extent7].[Field12] AS [Field121],
[Extent7].[Field14] AS [Field141],
[Extent7].[Field15] AS [Field151],
[Extent7].[Field13] AS [Field131],
[Extent7].[MainId] AS [MainId3],
CAST(NULL AS int) AS [C19],
CAST(NULL AS int) AS [C20],
CAST(NULL AS varchar(1)) AS [C21],
CAST(NULL AS varchar(1)) AS [C22],
CAST(NULL AS varchar(1)) AS [C23],
CAST(NULL AS varchar(1)) AS [C24],
CAST(NULL AS varchar(1)) AS [C25],
CAST(NULL AS varchar(1)) AS [C26],
CAST(NULL AS varchar(1)) AS [C27],
CAST(NULL AS varchar(1)) AS [C28],
CAST(NULL AS varchar(1)) AS [C29],
CAST(NULL AS int) AS [C30]
FROM (SELECT TOP (1) [Extent6].[MainId] AS [MainId], [Extent6].[Field1] AS [Field1], [Extent6].[Field11] AS [Field11], [Extent6].[Field12] AS [Field12], [Extent6].[Field13] AS [Field13], [Extent6].[Field14] AS [Field14], [Extent6].[Field15] AS [Field15], [Extent6].[Field16] AS [Field16], [Extent6].[Field17] AS [Field17], [Extent6].[Field18] AS [Field18], [Extent6].[Field19] AS [Field19]
FROM [dbo].[MainTable] AS [Extent6]
WHERE 10 = [Extent6].[MainId] ) AS [Limit3]
INNER JOIN [dbo].[MaintTable_DataLookup] AS [Extent7] ON [Limit3].[MainId] = [Extent7].[MainId]) AS [UnionAll2]
UNION ALL
SELECT
4 AS [C1],
[Limit4].[MainId] AS [MainId],
[Limit4].[MainId] AS [MainId1],
[Limit4].[Field1] AS [Field1],
[Limit4].[Field11] AS [Field11],
[Limit4].[Field12] AS [Field12],
[Limit4].[Field13] AS [Field13],
[Limit4].[Field14] AS [Field14],
[Limit4].[Field15] AS [Field15],
[Limit4].[Field16] AS [Field16],
[Limit4].[Field17] AS [Field17],
[Limit4].[Field18] AS [Field18],
[Limit4].[Field19] AS [Field19],
CAST(NULL AS int) AS [C2],
CAST(NULL AS varchar(1)) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS varchar(1)) AS [C5],
CAST(NULL AS varchar(1)) AS [C6],
CAST(NULL AS varchar(1)) AS [C7],
CAST(NULL AS varchar(1)) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS int) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS varchar(1)) AS [C13],
CAST(NULL AS varchar(1)) AS [C14],
CAST(NULL AS varchar(1)) AS [C15],
CAST(NULL AS varchar(1)) AS [C16],
CAST(NULL AS varchar(1)) AS [C17],
CAST(NULL AS varchar(1)) AS [C18],
CAST(NULL AS int) AS [C19],
CAST(NULL AS varchar(1)) AS [C20],
CAST(NULL AS varchar(1)) AS [C21],
CAST(NULL AS varchar(1)) AS [C22],
CAST(NULL AS varchar(1)) AS [C23],
CAST(NULL AS varchar(1)) AS [C24],
CAST(NULL AS varchar(1)) AS [C25],
CAST(NULL AS varchar(1)) AS [C26],
CAST(NULL AS int) AS [C27],
[Extent9].[LookupId] AS [LookupId],
[Extent9].[MainId] AS [MainId2],
[Extent9].[MoreMoreData] AS [MoreMoreData],
[Extent9].[Field17] AS [Field171],
[Extent9].[Field16] AS [Field161],
[Extent9].[Field15] AS [Field151],
[Extent9].[Field14] AS [Field141],
[Extent9].[Field13] AS [Field131],
[Extent9].[Field12] AS [Field121],
[Extent9].[Field11] AS [Field111],
[Extent9].[Field1] AS [Field110],
[Extent9].[MainId] AS [MainId3]
FROM (SELECT TOP (1) [Extent8].[MainId] AS [MainId], [Extent8].[Field1] AS [Field1], [Extent8].[Field11] AS [Field11], [Extent8].[Field12] AS [Field12], [Extent8].[Field13] AS [Field13], [Extent8].[Field14] AS [Field14], [Extent8].[Field15] AS [Field15], [Extent8].[Field16] AS [Field16], [Extent8].[Field17] AS [Field17], [Extent8].[Field18] AS [Field18], [Extent8].[Field19] AS [Field19]
FROM [dbo].[MainTable] AS [Extent8]
WHERE 10 = [Extent8].[MainId] ) AS [Limit4]
INNER JOIN [dbo].[MainTable_EvenMoreData] AS [Extent9] ON [Limit4].[MainId] = [Extent9].[MainId]) AS [UnionAll3]
ORDER BY [UnionAll3].[C3] ASC, [UnionAll3].[C1] ASC
-- Executing at 2014-05-12 18:00:57 -04:00
-- Completed in 39 ms with result: SqlDataReader
sql server result
why 8 rows when 2 is enough?
this db example is not optimized with index and such but here is the execution plan
the plan
ideally i would like to produce a query that would look like this while keeping entity framework tracking change since i would be using the object to update the database
SELECT dbo.MainTable.MainId, dbo.MainTable.Field1, dbo.MainTable.Field11, dbo.MainTable.Field12, dbo.MainTable.Field13, dbo.MainTable.Field14,
dbo.MainTable.Field15, dbo.MainTable.Field16, dbo.MainTable.Field17, dbo.MainTable.Field18, dbo.MainTable.Field19, dbo.MainTable_EvenMoreData.LookupId,
dbo.MainTable_EvenMoreData.MainId AS Expr1, dbo.MainTable_EvenMoreData.MoreMoreData, dbo.MainTable_EvenMoreData.Field17 AS Expr2,
dbo.MainTable_EvenMoreData.Field16 AS Expr3, dbo.MainTable_EvenMoreData.Field15 AS Expr4, dbo.MainTable_EvenMoreData.Field14 AS Expr5,
dbo.MainTable_EvenMoreData.Field13 AS Expr6, dbo.MainTable_EvenMoreData.Field12 AS Expr7, dbo.MainTable_EvenMoreData.Field11 AS Expr8,
dbo.MainTable_EvenMoreData.Field1 AS Expr9, dbo.MainTable_LookupTable.MainId AS Expr10, dbo.MainTable_LookupTable.LookupId AS Expr11,
dbo.MainTable_MoreData.DataId, dbo.MainTable_MoreData.MainId AS Expr12, dbo.MainTable_MoreData.SomeData, dbo.MainTable_MoreData.Field1 AS Expr13,
dbo.MainTable_MoreData.Field11 AS Expr14, dbo.MainTable_MoreData.Field12 AS Expr15, dbo.MainTable_MoreData.Field13 AS Expr16,
dbo.MainTable_MoreData.Field14 AS Expr17, dbo.MainTable_MoreData.Field15 AS Expr18, dbo.MaintTable_DataLookup.MainId AS Expr19,
dbo.MaintTable_DataLookup.Data, dbo.MaintTable_DataLookup.Field1 AS Expr20, dbo.MaintTable_DataLookup.Field11 AS Expr21,
dbo.MaintTable_DataLookup.Field12 AS Expr22, dbo.MaintTable_DataLookup.Field14 AS Expr23, dbo.MaintTable_DataLookup.Field15 AS Expr24,
dbo.MaintTable_DataLookup.Field13 AS Expr25
FROM dbo.MainTable LEFT OUTER JOIN
dbo.MainTable_EvenMoreData ON dbo.MainTable.MainId = dbo.MainTable_EvenMoreData.MainId LEFT OUTER JOIN
dbo.MainTable_LookupTable ON dbo.MainTable.MainId = dbo.MainTable_LookupTable.MainId LEFT OUTER JOIN
dbo.MainTable_MoreData ON dbo.MainTable.MainId = dbo.MainTable_MoreData.MainId LEFT OUTER JOIN
dbo.MaintTable_DataLookup ON dbo.MainTable.MainId = dbo.MaintTable_DataLookup.MainId
WHERE dbo.MainTable.MainId = 10
478
so after a few hours playing around, i did some load balancing / execution plan / statistic on sql server with a simple left join and the huge query that entity framework does.
it seem that entity framework win by a big margin.
I'm going to implement the .include() solution.
166
Yes, this is normal. Entity Framework is taking your (usually hierarchical) relational model and trying to produce a query that will produce all the necessary data in one big, flat table.
is it possible to keep the behavior of one include while using more than one, simple left join for all of them?
It depends on what you mean by "behavior." If you mean producing a similar SQL query, then no, you cannot. But one must ask why you care: these queries might be optimized for SQL Server, but they are not going to be optimized for human readability.
If you mean that you want to have better performance, there are strategies you can use other than eager-loading everything all in one big database round-trip. One strategy that I've found to work well is using a new round-trip per relationship, like so:
var dataQuery = db.MainTables.Where(d => d.MainId == 10);
var data = dataQuery.FirstOrDefault();
dataQuery.Select(x => x.MainTable_MoreData).ToList();
// etc.
Here's a simple utility class you can use to make this less tedious, especially when loading data from several relationships deep:
/// <summary>
/// This class aids in loading a lot of related data in Entity Framework.
/// <para>
/// Typically Entity Framework either lets you load entities Eagerly or Lazily,
/// but neither case handles things very well once you are adding many chained
/// relationships. A more ideal approach in these cases is to load all of the
/// entities you are going to need for a given relationship in a single round-trip,
/// and do this once for every relationship you're interested in.
/// That's what this class helps with.
/// </para>
/// <para>
/// To use: simply create an EntityRelationshipLoader with the initial
/// Entity-Framework-backed queryable that will be the basis of all the data
/// you're going to be loading. Then for each entity you want to load in relationship
/// to that original data type, call either <see cref="Include{TProp}"/> or
/// <see cref="IncludeMany{TProp}"/>. The return value from calling these methods may
/// be retained and used to include other property relationships based on the
/// property that you just defined. Each call to any of these methods will produce a
/// single round-trip.
/// </para>
/// <remarks>
/// Remember that all actions on the loader, including its original
/// construction, must be performed while the query's Entity Framework context
/// is active.
/// </remarks>
/// </summary>
/// <typeparam name="T"></typeparam>
public class EntityRelationshipLoader<T> : IRelationshipPropertyBuilder<T>
{
private readonly IQueryable<T> _src;
public EntityRelationshipLoader(IQueryable<T> src) : this(src, true)
{
}
private EntityRelationshipLoader(IQueryable<T> src, bool evaluateSource)
{
_src = src;
if (evaluateSource)
{
LoadEntities(src);
}
}
public IRelationshipPropertyBuilder<TProp> IncludeMany<TProp>(Expression<Func<T, IEnumerable<TProp>>> navProp)
{
LoadEntities(_src.Select(navProp));
return new EntityRelationshipLoader<TProp>(_src.SelectMany(navProp), false);
}
public IRelationshipPropertyBuilder<TProp> Include<TProp>(Expression<Func<T, TProp>> navProp)
{
return new EntityRelationshipLoader<TProp>(_src.Select(navProp), true);
}
/// <summary>
/// Simple helper method to cause the given query to be executed,
/// thereby loading all the entities the query represents.
/// </summary>
/// <param name="query"></param>
private void LoadEntities<T1>(IQueryable<T1> query)
{
#pragma warning disable 168
foreach (var item in query)
{
}
#pragma warning restore 168
}
This will allow you to say:
var dataQuery = db.MainTables.Where(d => d.MainId == 10);
var dataLoader = new EntityRelationshipLoader<MainTable>(dataQuery);
dataLoader.Include(x => x.LookupTables);
dataLoader.IncludeMany(x => x.MainTable_MoreData)
// Do you need to load MoreData's .LookupTables properties?
.Include(x => x.LookupTables);
dataLoader.Include(x => x.MaintTable_DataLookup)
dataLoader.Include(x => x.MainTable_EvenMoreData);
var data = dataQuery.Single(); // Or ToList() if you need multiple of them.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
