重构EF查询的技巧
本文关键字：查询 EF 重构 | 更新日期: 2023-09-27 18:14:46
这个查询几乎40秒(启动时运行3次…):
return context.Audits
.Include(a => a.Phases.Select(p => p.UserActionLogs))
.Include(a => a.Phases.Select(p => p.Reminders))
.Include(a => a.Phases.Select(p => p.PhaseType))
.Include(a => a.AuditResources.Select(ar => ar.Resource))
.Include(a => a.AuditCostCenterManagers.Select(acm => acm.CostCenterManager))
.Include(a => a.AuditResources.Select(ar => ar.ResourceOwners.Select(ro => ro.Owner)))
.Include(a => a.AuditResources.Select(ar => ar.UserFiles.Select(uf => uf.UserLists)))
.Include(a => a.AuditType)
.Where(a => a.Id == context.Audits.Max(aa => aa.Id) && a.EndDate == null).SingleOrDefault();
调用者依赖子对象。如何在交付所有所需子对象的同时提高性能?我使用 EF 6.1.40302.0 在。Net 4.6.1 WPF项目。我很想介绍一下Dapper，但我不知道如何在Dapper中完成这种复杂的查询。
Raw SQL: (yummy)
SELECT
[UnionAll4].[Id] AS [C1],
[UnionAll4].[Id1] AS [C2],
[UnionAll4].[Description] AS [C3],
[UnionAll4].[UserId] AS [C4],
[UnionAll4].[BeginDate] AS [C5],
[UnionAll4].[EndDate] AS [C6],
[UnionAll4].[Deadline] AS [C7],
[UnionAll4].[AuditTypeId] AS [C8],
[UnionAll4].[Id2] AS [C9],
[UnionAll4].[Description1] AS [C10],
[UnionAll4].[C1] AS [C11],
[UnionAll4].[C2] AS [C12],
[UnionAll4].[C3] AS [C13],
[UnionAll4].[C4] AS [C14],
[UnionAll4].[C5] AS [C15],
[UnionAll4].[C6] AS [C16],
[UnionAll4].[C7] AS [C17],
[UnionAll4].[C8] AS [C18],
[UnionAll4].[C9] AS [C19],
[UnionAll4].[C10] AS [C20],
[UnionAll4].[C11] AS [C21],
[UnionAll4].[C12] AS [C22],
[UnionAll4].[C13] AS [C23],
[UnionAll4].[C14] AS [C24],
[UnionAll4].[C15] AS [C25],
[UnionAll4].[C16] AS [C26],
[UnionAll4].[C17] AS [C27],
[UnionAll4].[C18] AS [C28],
[UnionAll4].[C19] AS [C29],
[UnionAll4].[C20] AS [C30],
[UnionAll4].[C21] AS [C31],
[UnionAll4].[C22] AS [C32],
[UnionAll4].[C23] AS [C33],
[UnionAll4].[C24] AS [C34],
[UnionAll4].[C25] AS [C35],
[UnionAll4].[C26] AS [C36],
[UnionAll4].[C27] AS [C37],
[UnionAll4].[C28] AS [C38],
[UnionAll4].[C29] AS [C39],
[UnionAll4].[C30] AS [C40],
[UnionAll4].[C31] AS [C41],
[UnionAll4].[C32] AS [C42],
[UnionAll4].[C33] AS [C43],
[UnionAll4].[C34] AS [C44],
[UnionAll4].[C35] AS [C45],
[UnionAll4].[C36] AS [C46],
[UnionAll4].[C37] AS [C47],
[UnionAll4].[C38] AS [C48],
[UnionAll4].[C39] AS [C49],
[UnionAll4].[C40] AS [C50],
[UnionAll4].[C41] AS [C51],
[UnionAll4].[C42] AS [C52],
[UnionAll4].[C43] AS [C53],
[UnionAll4].[C44] AS [C54],
[UnionAll4].[C45] AS [C55],
[UnionAll4].[C46] AS [C56],
[UnionAll4].[C47] AS [C57],
[UnionAll4].[C48] AS [C58],
[UnionAll4].[C49] AS [C59],
[UnionAll4].[C50] AS [C60],
[UnionAll4].[C51] AS [C61],
[UnionAll4].[C52] AS [C62],
[UnionAll4].[C53] AS [C63],
[UnionAll4].[C54] AS [C64],
[UnionAll4].[C55] AS [C65],
[UnionAll4].[C56] AS [C66],
[UnionAll4].[C57] AS [C67],
[UnionAll4].[C58] AS [C68],
[UnionAll4].[C59] AS [C69],
[UnionAll4].[C60] AS [C70],
[UnionAll4].[C61] AS [C71],
[UnionAll4].[C62] AS [C72],
[UnionAll4].[C63] AS [C73],
[UnionAll4].[C64] AS [C74],
[UnionAll4].[C65] AS [C75],
[UnionAll4].[C66] AS [C76],
[UnionAll4].[C67] AS [C77],
[UnionAll4].[C68] AS [C78],
[UnionAll4].[C69] AS [C79],
[UnionAll4].[C70] AS [C80]
FROM (SELECT
CASE WHEN ([UnionAll1].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Limit1].[Id1] AS [Id],
[Limit1].[Id1] AS [Id1],
[Limit1].[Description1] AS [Description],
[Limit1].[UserId] AS [UserId],
[Limit1].[BeginDate] AS [BeginDate],
[Limit1].[EndDate] AS [EndDate],
[Limit1].[Deadline] AS [Deadline],
[Limit1].[AuditTypeId] AS [AuditTypeId],
[Limit1].[Id2] AS [Id2],
[Limit1].[Description2] AS [Description1],
[UnionAll1].[Id] AS [C2],
[UnionAll1].[Id1] AS [C3],
[UnionAll1].[Description] AS [C4],
[UnionAll1].[AuditId] AS [C5],
[UnionAll1].[PhaseTypeId] AS [C6],
[UnionAll1].[BeginDate] AS [C7],
[UnionAll1].[EndDate] AS [C8],
[UnionAll1].[Deadline] AS [C9],
[UnionAll1].[Id2] AS [C10],
[UnionAll1].[Description1] AS [C11],
[UnionAll1].[C1] AS [C12],
[UnionAll1].[Id3] AS [C13],
[UnionAll1].[UserActionId] AS [C14],
[UnionAll1].[PhaseId] AS [C15],
[UnionAll1].[UserId] AS [C16],
[UnionAll1].[OwnerId] AS [C17],
[UnionAll1].[DiscretionKey] AS [C18],
[UnionAll1].[DiscretionValue] AS [C19],
[UnionAll1].[WorkflowId] AS [C20],
[UnionAll1].[RoleId] AS [C21],
[UnionAll1].[AuditResourceId] AS [C22],
[UnionAll1].[CostCenterId] AS [C23],
[UnionAll1].[UserActionStatus] AS [C24],
[UnionAll1].[CreationDate] AS [C25],
[UnionAll1].[LastModificationDate] AS [C26],
[UnionAll1].[C2] AS [C27],
[UnionAll1].[C3] AS [C28],
[UnionAll1].[C4] AS [C29],
[UnionAll1].[C5] AS [C30],
[UnionAll1].[C6] AS [C31],
CAST(NULL AS int) AS [C32],
CAST(NULL AS int) AS [C33],
CAST(NULL AS int) AS [C34],
CAST(NULL AS int) AS [C35],
CAST(NULL AS int) AS [C36],
CAST(NULL AS varchar(1)) AS [C37],
CAST(NULL AS int) AS [C38],
CAST(NULL AS int) AS [C39],
CAST(NULL AS int) AS [C40],
CAST(NULL AS int) AS [C41],
CAST(NULL AS int) AS [C42],
CAST(NULL AS int) AS [C43],
CAST(NULL AS int) AS [C44],
CAST(NULL AS int) AS [C45],
CAST(NULL AS varchar(1)) AS [C46],
CAST(NULL AS varchar(1)) AS [C47],
CAST(NULL AS varchar(1)) AS [C48],
CAST(NULL AS varchar(1)) AS [C49],
CAST(NULL AS int) AS [C50],
CAST(NULL AS int) AS [C51],
CAST(NULL AS varchar(1)) AS [C52],
CAST(NULL AS int) AS [C53],
CAST(NULL AS datetime2) AS [C54],
CAST(NULL AS int) AS [C55],
CAST(NULL AS int) AS [C56],
CAST(NULL AS varchar(1)) AS [C57],
CAST(NULL AS int) AS [C58],
CAST(NULL AS int) AS [C59],
CAST(NULL AS int) AS [C60],
CAST(NULL AS int) AS [C61],
CAST(NULL AS int) AS [C62],
CAST(NULL AS int) AS [C63],
CAST(NULL AS int) AS [C64],
CAST(NULL AS varchar(1)) AS [C65],
CAST(NULL AS varchar(1)) AS [C66],
CAST(NULL AS varchar(1)) AS [C67],
CAST(NULL AS varchar(1)) AS [C68],
CAST(NULL AS varchar(1)) AS [C69],
CAST(NULL AS varchar(1)) AS [C70]
FROM (SELECT TOP (1) [Extent1].[Id] AS [Id1], [Extent1].[Description] AS [Description1], [Extent1].[UserId] AS [UserId], [Extent1].[BeginDate] AS [BeginDate], [Extent1].[EndDate] AS [EndDate], [Extent1].[Deadline] AS [Deadline], [Extent1].[AuditTypeId] AS [AuditTypeId], [Extent2].[Id] AS [Id2], [Extent2].[Description] AS [Description2]
FROM [Aura].[Audit] AS [Extent1]
LEFT OUTER JOIN [Aura].[AuditType] AS [Extent2] ON [Extent1].[AuditTypeId] = [Extent2].[Id]
WHERE [Extent1].[EndDate] IS NULL ) AS [Limit1]
OUTER APPLY (SELECT
CASE WHEN ([Extent5].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent3].[Id] AS [Id],
[Extent3].[Id] AS [Id1],
[Extent3].[Description] AS [Description],
[Extent3].[AuditId] AS [AuditId],
[Extent3].[PhaseTypeId] AS [PhaseTypeId],
[Extent3].[BeginDate] AS [BeginDate],
[Extent3].[EndDate] AS [EndDate],
[Extent3].[Deadline] AS [Deadline],
[Extent4].[Id] AS [Id2],
[Extent4].[Description] AS [Description1],
[Extent5].[Id] AS [Id3],
[Extent5].[UserActionId] AS [UserActionId],
[Extent5].[PhaseId] AS [PhaseId],
[Extent5].[UserId] AS [UserId],
[Extent5].[OwnerId] AS [OwnerId],
[Extent5].[DiscretionKey] AS [DiscretionKey],
[Extent5].[DiscretionValue] AS [DiscretionValue],
[Extent5].[WorkflowId] AS [WorkflowId],
[Extent5].[RoleId] AS [RoleId],
[Extent5].[AuditResourceId] AS [AuditResourceId],
[Extent5].[CostCenterId] AS [CostCenterId],
[Extent5].[UserActionStatus] AS [UserActionStatus],
[Extent5].[CreationDate] AS [CreationDate],
[Extent5].[LastModificationDate] AS [LastModificationDate],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS datetime2) AS [C5],
CAST(NULL AS bit) AS [C6]
FROM [Aura].[Phase] AS [Extent3]
INNER JOIN [Aura].[PhaseType] AS [Extent4] ON [Extent3].[PhaseTypeId] = [Extent4].[Id]
LEFT OUTER JOIN [Aura].[UserActionLog] AS [Extent5] ON [Extent3].[Id] = [Extent5].[PhaseId]
WHERE [Limit1].[Id1] = [Extent3].[AuditId]
UNION ALL
SELECT
2 AS [C1],
[Extent6].[Id] AS [Id],
[Extent6].[Id] AS [Id1],
[Extent6].[Description] AS [Description],
[Extent6].[AuditId] AS [AuditId],
[Extent6].[PhaseTypeId] AS [PhaseTypeId],
[Extent6].[BeginDate] AS [BeginDate],
[Extent6].[EndDate] AS [EndDate],
[Extent6].[Deadline] AS [Deadline],
[Extent7].[Id] AS [Id2],
[Extent7].[Description] AS [Description1],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) 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 int) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS int) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
CAST(NULL AS int) AS [C13],
CAST(NULL AS datetime2) AS [C14],
CAST(NULL AS datetime2) AS [C15],
[Extent8].[Id] AS [Id3],
[Extent8].[ReminderTypeId] AS [ReminderTypeId],
[Extent8].[PhaseId] AS [PhaseId],
[Extent8].[DueDate] AS [DueDate],
[Extent8].[Sent] AS [Sent]
FROM [Aura].[Phase] AS [Extent6]
INNER JOIN [Aura].[PhaseType] AS [Extent7] ON [Extent6].[PhaseTypeId] = [Extent7].[Id]
INNER JOIN [Aura].[Reminder] AS [Extent8] ON [Extent6].[Id] = [Extent8].[PhaseId]
WHERE [Limit1].[Id1] = [Extent6].[AuditId]) AS [UnionAll1]
UNION ALL
SELECT
2 AS [C1],
[Limit2].[Id3] AS [Id],
[Limit2].[Id3] AS [Id1],
[Limit2].[Description3] AS [Description],
[Limit2].[UserId] AS [UserId],
[Limit2].[BeginDate] AS [BeginDate],
[Limit2].[EndDate] AS [EndDate],
[Limit2].[Deadline] AS [Deadline],
[Limit2].[AuditTypeId] AS [AuditTypeId],
[Limit2].[Id4] AS [Id2],
[Limit2].[Description4] AS [Description1],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS datetime2) AS [C7],
CAST(NULL AS datetime2) AS [C8],
CAST(NULL AS datetime2) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS int) AS [C12],
CAST(NULL AS int) AS [C13],
CAST(NULL AS int) AS [C14],
CAST(NULL AS int) 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 varchar(1)) AS [C19],
CAST(NULL AS int) AS [C20],
CAST(NULL AS int) AS [C21],
CAST(NULL AS int) AS [C22],
CAST(NULL AS varchar(1)) AS [C23],
CAST(NULL AS int) AS [C24],
CAST(NULL AS datetime2) AS [C25],
CAST(NULL AS datetime2) AS [C26],
CAST(NULL AS int) AS [C27],
CAST(NULL AS int) AS [C28],
CAST(NULL AS int) AS [C29],
CAST(NULL AS datetime2) AS [C30],
CAST(NULL AS bit) AS [C31],
[UnionAll2].[Id] AS [C32],
[UnionAll2].[Id1] AS [C33],
[UnionAll2].[AuditId] AS [C34],
[UnionAll2].[ResourceId] AS [C35],
[UnionAll2].[Id2] AS [C36],
[UnionAll2].[Name] AS [C37],
[UnionAll2].[C1] AS [C38],
[UnionAll2].[Id3] AS [C39],
[UnionAll2].[Id4] AS [C40],
[UnionAll2].[OwnerId] AS [C41],
[UnionAll2].[OwnerTypeId] AS [C42],
[UnionAll2].[AuditResourceId] AS [C43],
[UnionAll2].[DelegateForId] AS [C44],
[UnionAll2].[Id5] AS [C45],
[UnionAll2].[UserId] AS [C46],
[UnionAll2].[FirstName] AS [C47],
[UnionAll2].[LastName] AS [C48],
[UnionAll2].[Email] AS [C49],
[UnionAll2].[C2] AS [C50],
[UnionAll2].[C3] AS [C51],
[UnionAll2].[C4] AS [C52],
[UnionAll2].[C5] AS [C53],
[UnionAll2].[C6] AS [C54],
[UnionAll2].[C7] AS [C55],
[UnionAll2].[C8] AS [C56],
[UnionAll2].[C9] AS [C57],
[UnionAll2].[C10] AS [C58],
CAST(NULL AS int) AS [C59],
CAST(NULL AS int) AS [C60],
CAST(NULL AS int) AS [C61],
CAST(NULL AS int) AS [C62],
CAST(NULL AS int) AS [C63],
CAST(NULL AS int) AS [C64],
CAST(NULL AS varchar(1)) AS [C65],
CAST(NULL AS varchar(1)) AS [C66],
CAST(NULL AS varchar(1)) AS [C67],
CAST(NULL AS varchar(1)) AS [C68],
CAST(NULL AS varchar(1)) AS [C69],
CAST(NULL AS varchar(1)) AS [C70]
FROM (SELECT TOP (1) [Extent9].[Id] AS [Id3], [Extent9].[Description] AS [Description3], [Extent9].[UserId] AS [UserId], [Extent9].[BeginDate] AS [BeginDate], [Extent9].[EndDate] AS [EndDate], [Extent9].[Deadline] AS [Deadline], [Extent9].[AuditTypeId] AS [AuditTypeId], [Extent10].[Id] AS [Id4], [Extent10].[Description] AS [Description4]
FROM [Aura].[Audit] AS [Extent9]
LEFT OUTER JOIN [Aura].[AuditType] AS [Extent10] ON [Extent9].[AuditTypeId] = [Extent10].[Id]
WHERE [Extent9].[EndDate] IS NULL ) AS [Limit2]
CROSS APPLY (SELECT
CASE WHEN ([Join8].[Id5] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1],
[Extent11].[Id] AS [Id],
[Extent11].[Id] AS [Id1],
[Extent11].[AuditId] AS [AuditId],
[Extent11].[ResourceId] AS [ResourceId],
[Extent12].[Id] AS [Id2],
[Extent12].[Name] AS [Name],
[Join8].[Id5] AS [Id3],
[Join8].[Id5] AS [Id4],
[Join8].[OwnerId] AS [OwnerId],
[Join8].[OwnerTypeId] AS [OwnerTypeId],
[Join8].[AuditResourceId] AS [AuditResourceId],
[Join8].[DelegateForId] AS [DelegateForId],
[Join8].[Id6] AS [Id5],
[Join8].[UserId] AS [UserId],
[Join8].[FirstName] AS [FirstName],
[Join8].[LastName] AS [LastName],
[Join8].[Email] AS [Email],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS datetime2) AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS int) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS int) AS [C10]
FROM [Aura].[AuditResource] AS [Extent11]
INNER JOIN [Aura].[Resource] AS [Extent12] ON [Extent11].[ResourceId] = [Extent12].[Id]
LEFT OUTER JOIN (SELECT [Extent13].[Id] AS [Id5], [Extent13].[OwnerId] AS [OwnerId], [Extent13].[OwnerTypeId] AS [OwnerTypeId], [Extent13].[AuditResourceId] AS [AuditResourceId], [Extent13].[DelegateForId] AS [DelegateForId], [Extent14].[Id] AS [Id6], [Extent14].[UserId] AS [UserId], [Extent14].[FirstName] AS [FirstName], [Extent14].[LastName] AS [LastName], [Extent14].[Email] AS [Email]
FROM [Aura].[ResourceOwner] AS [Extent13]
INNER JOIN [Aura].[Owner] AS [Extent14] ON [Extent13].[OwnerId] = [Extent14].[Id] ) AS [Join8] ON [Extent11].[Id] = [Join8].[AuditResourceId]
WHERE [Limit2].[Id3] = [Extent11].[AuditId]
UNION ALL
SELECT
2 AS [C1],
[Extent15].[Id] AS [Id],
[Extent15].[Id] AS [Id1],
[Extent15].[AuditId] AS [AuditId],
[Extent15].[ResourceId] AS [ResourceId],
[Extent16].[Id] AS [Id2],
[Extent16].[Name] AS [Name],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS int) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS int) AS [C7],
CAST(NULL AS int) AS [C8],
CAST(NULL AS varchar(1)) AS [C9],
CAST(NULL AS varchar(1)) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS varchar(1)) AS [C12],
[Join11].[Id7] AS [Id3],
[Join11].[Id7] AS [Id4],
[Join11].[Name] AS [Name1],
[Join11].[AuditResourceId] AS [AuditResourceId],
[Join11].[CreationDate] AS [CreationDate],
CASE WHEN ([Join11].[Id8] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C13],
[Join11].[Id8] AS [Id5],
[Join11].[UserId] AS [UserId],
[Join11].[UserFileId] AS [UserFileId]
FROM [Aura].[AuditResource] AS [Extent15]
INNER JOIN [Aura].[Resource] AS [Extent16] ON [Extent15].[ResourceId] = [Extent16].[Id]
INNER JOIN (SELECT [Extent17].[Id] AS [Id7], [Extent17].[Name] AS [Name], [Extent17].[AuditResourceId] AS [AuditResourceId], [Extent17].[CreationDate] AS [CreationDate], [Extent18].[Id] AS [Id8], [Extent18].[UserId] AS [UserId], [Extent18].[UserFileId] AS [UserFileId]
FROM [Aura].[UserFile] AS [Extent17]
LEFT OUTER JOIN [Aura].[UserList] AS [Extent18] ON [Extent17].[Id] = [Extent18].[UserFileId] ) AS [Join11] ON [Extent15].[Id] = [Join11].[AuditResourceId]
WHERE [Limit2].[Id3] = [Extent15].[AuditId]) AS [UnionAll2]
UNION ALL
SELECT
3 AS [C1],
[Limit3].[Id9] AS [Id],
[Limit3].[Id9] AS [Id1],
[Limit3].[Description5] AS [Description],
[Limit3].[UserId] AS [UserId],
[Limit3].[BeginDate] AS [BeginDate],
[Limit3].[EndDate] AS [EndDate],
[Limit3].[Deadline] AS [Deadline],
[Limit3].[AuditTypeId] AS [AuditTypeId],
[Limit3].[Id10] AS [Id2],
[Limit3].[Description6] AS [Description1],
CAST(NULL AS int) AS [C2],
CAST(NULL AS int) AS [C3],
CAST(NULL AS varchar(1)) AS [C4],
CAST(NULL AS int) AS [C5],
CAST(NULL AS int) AS [C6],
CAST(NULL AS datetime2) AS [C7],
CAST(NULL AS datetime2) AS [C8],
CAST(NULL AS datetime2) AS [C9],
CAST(NULL AS int) AS [C10],
CAST(NULL AS varchar(1)) AS [C11],
CAST(NULL AS int) AS [C12],
CAST(NULL AS int) AS [C13],
CAST(NULL AS int) AS [C14],
CAST(NULL AS int) 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 varchar(1)) AS [C19],
CAST(NULL AS int) AS [C20],
CAST(NULL AS int) AS [C21],
CAST(NULL AS int) AS [C22],
CAST(NULL AS varchar(1)) AS [C23],
CAST(NULL AS int) AS [C24],
CAST(NULL AS datetime2) AS [C25],
CAST(NULL AS datetime2) AS [C26],
CAST(NULL AS int) AS [C27],
CAST(NULL AS int) AS [C28],
CAST(NULL AS int) AS [C29],
CAST(NULL AS datetime2) AS [C30],
CAST(NULL AS bit) AS [C31],
CAST(NULL AS int) AS [C32],
CAST(NULL AS int) AS [C33],
CAST(NULL AS int) AS [C34],
CAST(NULL AS int) AS [C35],
CAST(NULL AS int) AS [C36],
CAST(NULL AS varchar(1)) AS [C37],
CAST(NULL AS int) AS [C38],
CAST(NULL AS int) AS [C39],
CAST(NULL AS int) AS [C40],
CAST(NULL AS int) AS [C41],
CAST(NULL AS int) AS [C42],
CAST(NULL AS int) AS [C43],
CAST(NULL AS int) AS [C44],
CAST(NULL AS int) AS [C45],
CAST(NULL AS varchar(1)) AS [C46],
CAST(NULL AS varchar(1)) AS [C47],
CAST(NULL AS varchar(1)) AS [C48],
CAST(NULL AS varchar(1)) AS [C49],
CAST(NULL AS int) AS [C50],
CAST(NULL AS int) AS [C51],
CAST(NULL AS varchar(1)) AS [C52],
CAST(NULL AS int) AS [C53],
CAST(NULL AS datetime2) AS [C54],
CAST(NULL AS int) AS [C55],
CAST(NULL AS int) AS [C56],
CAST(NULL AS varchar(1)) AS [C57],
CAST(NULL AS int) AS [C58],
[Join14].[Id11] AS [Id3],
[Join14].[Id11] AS [Id4],
[Join14].[AuditId] AS [AuditId],
[Join14].[CostCenterManagerId] AS [CostCenterManagerId],
[Join14].[DelegateForId] AS [DelegateForId],
[Join14].[Id12] AS [Id5],
[Join14].[UserId] AS [UserId1],
[Join14].[CostCenterId] AS [CostCenterId],
[Join14].[CostCenterName] AS [CostCenterName],
[Join14].[FirstName] AS [FirstName],
[Join14].[LastName] AS [LastName],
[Join14].[Email] AS [Email]
FROM (SELECT TOP (1) [Extent19].[Id] AS [Id9], [Extent19].[Description] AS [Description5], [Extent19].[UserId] AS [UserId], [Extent19].[BeginDate] AS [BeginDate], [Extent19].[EndDate] AS [EndDate], [Extent19].[Deadline] AS [Deadline], [Extent19].[AuditTypeId] AS [AuditTypeId], [Extent20].[Id] AS [Id10], [Extent20].[Description] AS [Description6]
FROM [Aura].[Audit] AS [Extent19]
LEFT OUTER JOIN [Aura].[AuditType] AS [Extent20] ON [Extent19].[AuditTypeId] = [Extent20].[Id]
WHERE [Extent19].[EndDate] IS NULL ) AS [Limit3]
INNER JOIN (SELECT [Extent21].[Id] AS [Id11], [Extent21].[AuditId] AS [AuditId], [Extent21].[CostCenterManagerId] AS [CostCenterManagerId], [Extent21].[DelegateForId] AS [DelegateForId], [Extent22].[Id] AS [Id12], [Extent22].[UserId] AS [UserId], [Extent22].[CostCenterId] AS [CostCenterId], [Extent22].[CostCenterName] AS [CostCenterName], [Extent22].[FirstName] AS [FirstName], [Extent22].[LastName] AS [LastName], [Extent22].[Email] AS [Email]
FROM [Aura].[AuditCostCenterManager] AS [Extent21]
INNER JOIN [Aura].[CostCenterManager] AS [Extent22] ON [Extent21].[CostCenterManagerId] = [Extent22].[Id] ) AS [Join14] ON [Limit3].[Id9] = [Join14].[AuditId]) AS [UnionAll4]
ORDER BY [UnionAll4].[Id1] ASC, [UnionAll4].[Id2] ASC, [UnionAll4].[C1] ASC, [UnionAll4].[C3] ASC, [UnionAll4].[C10] ASC, [UnionAll4].[C12] ASC, [UnionAll4].[C33] ASC, [UnionAll4].[C36] ASC, [UnionAll4].[C38] ASC, [UnionAll4].[C51] ASC, [UnionAll4].[C55] ASC
-- Executing at 25/10/2016 16:21:14 +02:00
-- Completed in 847 ms with result: SqlDataReader
EntityFramework在使用多个包含时性能很差，因为优化结果查询非常慢。Performance Considerations页面的建议是分解查询:
// First get the max id
int auditId = context.Audits.Max(aa => aa.Id);
// Now, pre-load the related entities
context.Phases.Where(p => p.Audit.Id == auditId && p.Audit.EndDate == null).Load();
context.UserActionLogs.Where(l => l.Phase.Audit.Id == auditId && l.Phase.Audit.EndDate == null).Load();
// ... do the same for all related properties
// And finally get the resulting object
return context.Audits
.Where(a => a.Id == auditId && a.EndDate == null)
.SingleOrDefault();
在这里，您正在权衡慢查询编译和大数据负载与更多(但更小)查询。
请注意，这只适用于启用了跟踪的查询。