Использование.include в рамках сущности создать огромный запрос?
Я не ожидал сгенерированный запрос, как этот...
давайте вернемся, если я оставлю один включенный, запрос выглядит хорошо, он делает простое левое соединение
Запрос:
using (var db = new Context())
{
var data = db.MainTables.Include(x => x.LookupTables)
.Where(d => d.MainId == 10)
.FirstOrDefault();
}
SQL генерируется:
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
если у меня их больше одного, запрос сходит с ума с UNION ALL...
это ожидаемое поведение? если да, возможно ли сохранить поведение одного включения при использовании более одного простого левого соединения для всех из них?
Модель:
Запрос:
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 генерируется:
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 результат
почему 8 рядов, когда 2 достаточно?
этот пример БД не оптимизирован с индексом и тому подобное, но вот план выполнения
в идеале я хотел бы создать запрос, который выглядел бы так , сохраняя отслеживание изменений в сущности, поскольку я бы использовал объект для обновления базы данных
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
2 ответа
Решение
Так что после нескольких часов игры, я выполнил некоторый план балансировки нагрузки / выполнения / статистики на сервере sql с простым левым соединением и огромным запросом, который делает структура сущностей.
кажется, что структура сущности выигрывает с большим отрывом.
Я собираюсь реализовать решение.include().
Да, это нормально. Entity Framework берет вашу (обычно иерархическую) реляционную модель и пытается создать запрос, который сгенерирует все необходимые данные в одной большой плоской таблице.
возможно ли сохранить поведение одного включения при использовании более одного простого левого соединения для всех из них?
Это зависит от того, что вы подразумеваете под "поведением". Если вы имеете в виду создание аналогичного SQL-запроса, то нет, вы не можете. Но нужно спросить, почему вас это волнует: эти запросы могут быть оптимизированы для SQL Server, но они не будут оптимизированы для удобства чтения.
Если вы хотите иметь более высокую производительность, есть стратегии, которые вы можете использовать, кроме быстрой загрузки всего в одну большую базу данных. Одна стратегия, которая, как мне показалось, работает хорошо, - это использовать новую двустороннюю связь для каждого отношения, например, так:
var dataQuery = db.MainTables.Where(d => d.MainId == 10);
var data = dataQuery.FirstOrDefault();
dataQuery.Select(x => x.MainTable_MoreData).ToList();
// etc.
Вот простой вспомогательный класс, который вы можете использовать, чтобы сделать его менее утомительным, особенно при загрузке данных из нескольких взаимосвязей:
/// <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
}
Это позволит вам сказать:
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.