EF 쿼리 리팩터링 팁

c# dapper entity-framework entity-framework-6

문제

이 쿼리는 거의 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();

호출자는 자식 객체를 사용합니다. 필요한 모든 자식 객체를 전달하는 동안 어떻게하면 더 효율적으로 만들 수 있습니까? .NET 4.6.1 WPF 프로젝트에서 EF 6.1.40302.0 을 사용하고 있습니다. Dapper를 소개하고 싶지만 Dapper에서 이런 종류의 복잡한 쿼리를 어떻게 수행 할 수 있는지 알지 못합니다.

원시 SQL : (맛있는)

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는 결과 쿼리를 최적화하는 속도가 매우 느리기 때문에 여러 포함을 사용할 때 성능이 좋지 않습니다. 성능 고려 사항 페이지의 권장 사항 은 쿼리를 분리하는 것입니다.

// 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();

여기에서는 느린 쿼리 컴파일과 대용량 데이터 페이로드를 더 많은 (그러나 더 작은) 쿼리와 비교하여 거래합니다.

그러나이 기능은 추적 기능이 설정된 쿼리에서만 작동합니다.


인기 답변

나는이 방법을 발견했다. 너도 이것을 시도해보고 결과에 대해 알려줄 수있다. 나는 꽤 확신한다. 당신은 그 성능에 대해 놀랄 것이다 : D

참고 : 나는 이것에 대한 기사도 썼다. 여기있다 : Entity Framework의 성능 향상

var auditObject = context.Audits.Where(a => a.Id == context.Audits.Max(aa => aa.Id) && a.EndDate == null)
        .Include(a => a.Phases.Select(p => p.UserActionLogs))
        .Include(a => a.Phases.Select(p => p.Reminders))
        .FirstOrDefault();


auditObject = context.Audits.Where(a => a.Id == context.Audits.Max(aa => aa.Id) && a.EndDate == null)
        .Include(a => a.Phases.Select(p => p.PhaseType))
        .Include(a => a.AuditResources.Select(ar => ar.Resource))
        .FirstOrDefault();

auditObject = context.Audits.Where(a => a.Id == context.Audits.Max(aa => aa.Id) && a.EndDate == null)
        .Include(a => a.AuditCostCenterManagers.Select(acm => acm.CostCenterManager))
        .Include(a => a.AuditResources.Select(ar => ar.ResourceOwners.Select(ro => ro.Owner)))
        .FirstOrDefault();

auditObject = context.Audits.Where(a => a.Id == context.Audits.Max(aa => aa.Id) && a.EndDate == null)
          .Include(a => a.AuditResources.Select(ar => ar.UserFiles.Select(uf => uf.UserLists)))
          .Include(a => a.AuditType)
          .FirstOrDefault();

return  auditObject;


아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.
아래 라이선스: CC-BY-SA with attribution
와 제휴하지 않음 Stack Overflow
이 KB는 합법적입니까? 예, 이유를 알아보십시오.