<< Nifty Microsoft Internet Explorer Advertising | Development Resources for Any Language! >>

Examining Entity Framework’s SQL Generation

by mark shiffer 30. June 2010 16:00

I am just getting started with this, but so far I am less than impressed with the SQL that is being generated by Entity Framework. The following is an example of a query that is generated by Entity Framework for a system that I am currently working on.

        1: SELECT 

       2: [UnionAll4].[C2] AS [C1], 

       3: ...

       4: [UnionAll4].[C590] AS [C590]

       5: FROM  (SELECT 

       6:     [UnionAll2].[C1] AS [C1], 

       7:         ...

       8:     [UnionAll2].[C590] AS [C590]

       9:     FROM  (SELECT 

        [UnionAll1].[C1] AS [C1], 

        ...

        [UnionAll1].[C547] AS [C590]

        FROM  (SELECT 

            CASE WHEN ([Extent5].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 

            [Extent2].[Id] AS [Id], 

            [Extent3].[Id] AS [Id1], 

            0 AS [C2], 

            '0X0X0X0X' AS [C3], 

            0 AS [C4], 

            [Extent1].[Id] AS [Id2], 

            [Extent1].[ModifiedBy] AS [ModifiedBy], 

            [Extent1].[LastModified] AS [LastModified], 

            [Extent1].[TaxId] AS [TaxId], 

            [Extent1].[Version] AS [Version], 

            [Extent2].[LastName] AS [LastName], 

            [Extent2].[FirstName] AS [FirstName], 

            [Extent2].[BirthDate] AS [BirthDate], 

            [Extent2].[MiddleName] AS [MiddleName], 

            [Extent2].[Gender] AS [Gender], 

            [Extent2].[PreferredName] AS [PreferredName], 

            [Extent3].[SrsStartDate] AS [SrsStartDate], 

            [Extent3].[VacationBaseDate] AS [VacationBaseDate], 

            [Extent3].[CredibleServiceDate] AS [CredibleServiceDate], 

            [Extent3].[ContinuousServiceDate] AS [ContinuousServiceDate], 

            [Extent3].[TerminationDate] AS [TerminationDate], 

            [Extent3].[MonthlySalary] AS [MonthlySalary], 

            [Extent3].[PayStep] AS [PayStep], 

            [Extent3].[IsMeritComp] AS [IsMeritComp], 

            [Extent3].[SeniorityDate] AS [SeniorityDate], 

            [Extent3].[WorkScheduleTypeId] AS [WorkScheduleTypeId], 

            [Extent3].[SupervisorEmployeeId] AS [SupervisorEmployeeId], 

            CAST(NULL AS varchar(1)) AS [C5], 

            CAST(NULL AS varchar(1)) AS [C6], 

            CAST(NULL AS varchar(1)) AS [C7], 

            CAST(NULL AS uniqueidentifier) AS [C8], 

            CAST(NULL AS uniqueidentifier) AS [C9], 

            CAST(NULL AS varbinary(1)) 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 uniqueidentifier) AS [C15], 

            CAST(NULL AS uniqueidentifier) AS [C16], 

            ...

            CAST(NULL AS varbinary(1)) AS [C53], 

            CASE WHEN ([Extent4].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X3X' END AS [C54], 

            CASE WHEN ([Extent4].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 4 END AS [C55], 

            [Extent4].[Id] AS [Id3], 

            [Extent4].[ModifiedBy] AS [ModifiedBy1], 

            [Extent4].[LastModified] AS [LastModified1], 

            CAST(NULL AS varchar(1)) AS [C56], 

            ...

            CAST(NULL AS varbinary(1)) AS [C89], 

            [Extent4].[Sequence] AS [Sequence], 

            [Extent4].[TitleId] AS [TitleId], 

            [Extent4].[AgencyId] AS [AgencyId], 

            [Extent4].[SectionId] AS [SectionId], 

            [Extent4].[EqualEmploymentOpportunityCodeId] AS [EqualEmploymentOpportunityCodeId], 

            [Extent4].[BargainingUnitCodeId] AS [BargainingUnitCodeId], 

            [Extent4].[Version] AS [Version1], 

            CAST(NULL AS datetime2) AS [C90], 

            ...

            CAST(NULL AS varbinary(1)) AS [C116], 

            CASE WHEN ([Extent5].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X1X' END AS [C117], 

            CASE WHEN ([Extent5].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 5 END AS [C118], 

            [Extent5].[Id] AS [Id4], 

            [Extent5].[ModifiedBy] AS [ModifiedBy2], 

            [Extent5].[LastModified] AS [LastModified2], 

            CAST(NULL AS varchar(1)) AS [C119], 

            ...

            CAST(NULL AS uniqueidentifier) AS [C137], 

            [Extent5].[CountryCode] AS [CountryCode], 

            ...

            CAST(NULL AS varbinary(1)) AS [C547]

            FROM     [dbo].[Orgsons] AS [Extent1]

            INNER JOIN [dbo].[Orgsons_Person] AS [Extent2] ON [Extent1].[Id] = [Extent2].[Id]

            INNER JOIN [dbo].[Orgsons_Employee] AS [Extent3] ON [Extent1].[Id] = [Extent3].[Id]

            LEFT OUTER JOIN [dbo].[Positions] AS [Extent4] ON [Extent3].[Position_Id] = [Extent4].[Id]

            LEFT OUTER JOIN [dbo].[PhoneNumbers] AS [Extent5] ON [Extent1].[Id] = [Extent5].[OrgsonId]

            WHERE [Extent1].[Id] = '4F65BC85-06FD-4CC7-BB2B-95F2536EF132'

        UNION ALL

            SELECT 

            2 AS [C1], 

            [Extent7].[Id] AS [Id], 

            [Extent8].[Id] AS [Id1], 

            0 AS [C2], 

            '0X0X0X0X' AS [C3], 

            0 AS [C4], 

            [Extent6].[Id] AS [Id2], 

            [Extent6].[ModifiedBy] AS [ModifiedBy], 

            [Extent6].[LastModified] AS [LastModified], 

            [Extent6].[TaxId] AS [TaxId], 

            [Extent6].[Version] AS [Version], 

            [Extent7].[LastName] AS [LastName], 

            [Extent7].[FirstName] AS [FirstName], 

            [Extent7].[BirthDate] AS [BirthDate], 

            [Extent7].[MiddleName] AS [MiddleName], 

            [Extent7].[Gender] AS [Gender], 

            [Extent7].[PreferredName] AS [PreferredName], 

            [Extent8].[SrsStartDate] AS [SrsStartDate], 

            [Extent8].[VacationBaseDate] AS [VacationBaseDate], 

            [Extent8].[CredibleServiceDate] AS [CredibleServiceDate], 

            [Extent8].[ContinuousServiceDate] AS [ContinuousServiceDate], 

            [Extent8].[TerminationDate] AS [TerminationDate], 

            [Extent8].[MonthlySalary] AS [MonthlySalary], 

            [Extent8].[PayStep] AS [PayStep], 

            [Extent8].[IsMeritComp] AS [IsMeritComp], 

            [Extent8].[SeniorityDate] AS [SeniorityDate], 

            [Extent8].[WorkScheduleTypeId] AS [WorkScheduleTypeId], 

            [Extent8].[SupervisorEmployeeId] AS [SupervisorEmployeeId], 

            CAST(NULL AS varchar(1)) AS [C5], 

            ...

            CAST(NULL AS varbinary(1)) AS [C53], 

            CASE WHEN ([Extent9].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X3X' END AS [C54], 

            CASE WHEN ([Extent9].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 4 END AS [C55], 

            [Extent9].[Id] AS [Id3], 

            [Extent9].[ModifiedBy] AS [ModifiedBy1], 

            [Extent9].[LastModified] AS [LastModified1], 

            CAST(NULL AS varchar(1)) AS [C56], 

            ...

            CAST(NULL AS varbinary(1)) AS [C89], 

            [Extent9].[Sequence] AS [Sequence], 

            [Extent9].[TitleId] AS [TitleId], 

            [Extent9].[AgencyId] AS [AgencyId], 

            [Extent9].[SectionId] AS [SectionId], 

            [Extent9].[EqualEmploymentOpportunityCodeId] AS [EqualEmploymentOpportunityCodeId], 

            [Extent9].[BargainingUnitCodeId] AS [BargainingUnitCodeId], 

            [Extent9].[Version] AS [Version1], 

            CAST(NULL AS datetime2) AS [C90], 

            ...

            CAST(NULL AS varbinary(1)) AS [C216], 

            [Extent10].[Street1] AS [Street1], 

            [Extent10].[Street2] AS [Street2], 

            [Extent10].[City] AS [City], 

            [Extent10].[ZipCode] AS [ZipCode], 

            [Extent10].[OrgsonId] AS [OrgsonId], 

            [Extent10].[AddressTypeId] AS [AddressTypeId], 

            [Extent10].[StateId] AS [StateId], 

            [Extent10].[CountryId] AS [CountryId], 

            [Extent10].[Version] AS [Version2], 

            CAST(NULL AS varchar(1)) AS [C217], 

            ... 

            CAST(NULL AS varbinary(1)) AS [C544]

            FROM     [dbo].[Orgsons] AS [Extent6]

            INNER JOIN [dbo].[Orgsons_Person] AS [Extent7] ON [Extent6].[Id] = [Extent7].[Id]

            INNER JOIN [dbo].[Orgsons_Employee] AS [Extent8] ON [Extent6].[Id] = [Extent8].[Id]

            LEFT OUTER JOIN [dbo].[Positions] AS [Extent9] ON [Extent8].[Position_Id] = [Extent9].[Id]

            INNER JOIN [dbo].[Addresses] AS [Extent10] ON [Extent6].[Id] = [Extent10].[OrgsonId]

            WHERE [Extent6].[Id] = '4F65BC85-06FD-4CC7-BB2B-95F2536EF132') AS [UnionAll1]

    UNION ALL

        SELECT 

        3 AS [C1], 

        [Extent12].[Id] AS [Id], 

        [Extent13].[Id] AS [Id1], 

        0 AS [C2], 

        '0X0X0X0X' AS [C3], 

        0 AS [C4], 

        [Extent11].[Id] AS [Id2], 

        [Extent11].[ModifiedBy] AS [ModifiedBy], 

        [Extent11].[LastModified] AS [LastModified], 

        [Extent11].[TaxId] AS [TaxId], 

        [Extent11].[Version] AS [Version], 

        [Extent12].[LastName] AS [LastName], 

        [Extent12].[FirstName] AS [FirstName], 

        [Extent12].[BirthDate] AS [BirthDate], 

        [Extent12].[MiddleName] AS [MiddleName], 

        [Extent12].[Gender] AS [Gender], 

        [Extent12].[PreferredName] AS [PreferredName], 

        [Extent13].[SrsStartDate] AS [SrsStartDate], 

        [Extent13].[VacationBaseDate] AS [VacationBaseDate], 

        [Extent13].[CredibleServiceDate] AS [CredibleServiceDate], 

        [Extent13].[ContinuousServiceDate] AS [ContinuousServiceDate], 

        [Extent13].[TerminationDate] AS [TerminationDate], 

        [Extent13].[MonthlySalary] AS [MonthlySalary], 

        [Extent13].[PayStep] AS [PayStep], 

        [Extent13].[IsMeritComp] AS [IsMeritComp], 

        [Extent13].[SeniorityDate] AS [SeniorityDate], 

        [Extent13].[WorkScheduleTypeId] AS [WorkScheduleTypeId], 

        [Extent13].[SupervisorEmployeeId] AS [SupervisorEmployeeId], 

        CAST(NULL AS varchar(1)) AS [C5], 

        ...

        CAST(NULL AS varbinary(1)) AS [C53], 

        CASE WHEN ([Extent14].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X3X' END AS [C54], 

        CASE WHEN ([Extent14].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 4 END AS [C55], 

        [Extent14].[Id] AS [Id3], 

        [Extent14].[ModifiedBy] AS [ModifiedBy1], 

        [Extent14].[LastModified] AS [LastModified1], 

        CAST(NULL AS varchar(1)) AS [C56], 

        ...

        CAST(NULL AS varbinary(1)) AS [C89], 

        [Extent14].[Sequence] AS [Sequence], 

        [Extent14].[TitleId] AS [TitleId], 

        [Extent14].[AgencyId] AS [AgencyId], 

        [Extent14].[SectionId] AS [SectionId], 

        [Extent14].[EqualEmploymentOpportunityCodeId] AS [EqualEmploymentOpportunityCodeId], 

        [Extent14].[BargainingUnitCodeId] AS [BargainingUnitCodeId], 

        [Extent14].[Version] AS [Version1], 

        CAST(NULL AS datetime2) AS [C90], 

        ...

        CAST(NULL AS varbinary(1)) AS [C262], 

        '0X4X' AS [C263], 

        10 AS [C264], 

        [Extent15].[Id] AS [Id4], 

        [Extent15].[ModifiedBy] AS [ModifiedBy2], 

        [Extent15].[LastModified] AS [LastModified2], 

        CAST(NULL AS varchar(1)) AS [C265], 

        ...

        CAST(NULL AS varbinary(1)) AS [C305], 

        [Extent15].[SentDate] AS [SentDate], 

        [Extent15].[ReturnedDate] AS [ReturnedDate], 

        [Extent15].[EmployeeId] AS [EmployeeId], 

        [Extent15].[EvaluationTypeId] AS [EvaluationTypeId], 

        [Extent15].[Version] AS [Version2], 

        CAST(NULL AS datetime2) AS [C306], 

        ...

        CAST(NULL AS varbinary(1)) AS [C548]

        FROM     [dbo].[Orgsons] AS [Extent11]

        INNER JOIN [dbo].[Orgsons_Person] AS [Extent12] ON [Extent11].[Id] = [Extent12].[Id]

        INNER JOIN [dbo].[Orgsons_Employee] AS [Extent13] ON [Extent11].[Id] = [Extent13].[Id]

        LEFT OUTER JOIN [dbo].[Positions] AS [Extent14] ON [Extent13].[Position_Id] = [Extent14].[Id]

        INNER JOIN [dbo].[Evaluations] AS [Extent15] ON [Extent11].[Id] = [Extent15].[EmployeeId]

        WHERE [Extent11].[Id] = '4F65BC85-06FD-4CC7-BB2B-95F2536EF132') AS [UnionAll2]

UNION ALL

    SELECT 

    4 AS [C1], 

    [Project6].[Id1] AS [Id], 

    [Project6].[Id2] AS [Id1], 

    [Project6].[C2] AS [C2], 

    [Project6].[C1] AS [C3], 

    [Project6].[C2] AS [C4], 

    [Project6].[Id] AS [Id2], 

    [Project6].[ModifiedBy] AS [ModifiedBy], 

    [Project6].[LastModified] AS [LastModified], 

    [Project6].[TaxId] AS [TaxId], 

    [Project6].[Version] AS [Version], 

    [Project6].[LastName] AS [LastName], 

    [Project6].[FirstName] AS [FirstName], 

    [Project6].[BirthDate] AS [BirthDate], 

    [Project6].[MiddleName] AS [MiddleName], 

    [Project6].[Gender] AS [Gender], 

    [Project6].[PreferredName] AS [PreferredName], 

    [Project6].[SrsStartDate] AS [SrsStartDate], 

    [Project6].[VacationBaseDate] AS [VacationBaseDate], 

    [Project6].[CredibleServiceDate] AS [CredibleServiceDate], 

    [Project6].[ContinuousServiceDate] AS [ContinuousServiceDate], 

    [Project6].[TerminationDate] AS [TerminationDate], 

    [Project6].[MonthlySalary] AS [MonthlySalary], 

    [Project6].[PayStep] AS [PayStep], 

    [Project6].[IsMeritComp] AS [IsMeritComp], 

    [Project6].[SeniorityDate] AS [SeniorityDate], 

    [Project6].[WorkScheduleTypeId] AS [WorkScheduleTypeId], 

    [Project6].[SupervisorEmployeeId] AS [SupervisorEmployeeId], 

    [Project6].[C3] AS [C5], 

    ...

    [Project6].[Id3] AS [Id3], 

    [Project6].[ModifiedBy1] AS [ModifiedBy1], 

    [Project6].[LastModified1] AS [LastModified1], 

    [Project6].[C54] AS [C56], 

    ... 

    [Project6].[C86] AS [C88], 

    [Project6].[C87] AS [C89], 

    [Project6].[Sequence] AS [Sequence], 

    [Project6].[TitleId] AS [TitleId], 

    [Project6].[AgencyId] AS [AgencyId], 

    [Project6].[SectionId] AS [SectionId], 

    [Project6].[EqualEmploymentOpportunityCodeId] AS [EqualEmploymentOpportunityCodeId], 

    [Project6].[BargainingUnitCodeId] AS [BargainingUnitCodeId], 

    [Project6].[Version1] AS [Version1], 

    [Project6].[C88] AS [C90], 

    ...     

    CAST(NULL AS varbinary(1)) AS [C335], 

    ...

    [UnionAll3].[C199] AS [C556]

    FROM   (SELECT 

        [Extent16].[Id] AS [Id], 

        [Extent16].[TaxId] AS [TaxId], 

        [Extent16].[Version] AS [Version], 

        [Extent16].[ModifiedBy] AS [ModifiedBy], 

        [Extent16].[LastModified] AS [LastModified], 

        [Extent17].[LastName] AS [LastName], 

        [Extent17].[FirstName] AS [FirstName], 

        [Extent17].[BirthDate] AS [BirthDate], 

        [Extent17].[MiddleName] AS [MiddleName], 

        [Extent17].[Gender] AS [Gender], 

        [Extent17].[PreferredName] AS [PreferredName], 

        [Extent17].[Id] AS [Id1], 

        [Extent18].[SrsStartDate] AS [SrsStartDate], 

        [Extent18].[VacationBaseDate] AS [VacationBaseDate], 

        [Extent18].[CredibleServiceDate] AS [CredibleServiceDate], 

        [Extent18].[ContinuousServiceDate] AS [ContinuousServiceDate], 

        [Extent18].[TerminationDate] AS [TerminationDate], 

        [Extent18].[MonthlySalary] AS [MonthlySalary], 

        [Extent18].[PayStep] AS [PayStep], 

        [Extent18].[IsMeritComp] AS [IsMeritComp], 

        [Extent18].[SeniorityDate] AS [SeniorityDate], 

        [Extent18].[WorkScheduleTypeId] AS [WorkScheduleTypeId], 

        [Extent18].[SupervisorEmployeeId] AS [SupervisorEmployeeId], 

        [Extent18].[Id] AS [Id2], 

        '0X0X0X0X' AS [C1], 

        0 AS [C2], 

        CAST(NULL AS varchar(1)) AS [C3], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C51], 

        [Extent19].[Id] AS [Id3], 

        [Extent19].[Sequence] AS [Sequence], 

        [Extent19].[TitleId] AS [TitleId], 

        [Extent19].[AgencyId] AS [AgencyId], 

        [Extent19].[SectionId] AS [SectionId], 

        [Extent19].[EqualEmploymentOpportunityCodeId] AS [EqualEmploymentOpportunityCodeId], 

        [Extent19].[BargainingUnitCodeId] AS [BargainingUnitCodeId], 

        [Extent19].[Version] AS [Version1], 

        [Extent19].[ModifiedBy] AS [ModifiedBy1], 

        [Extent19].[LastModified] AS [LastModified1], 

        CASE WHEN ([Extent19].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X3X' END AS [C52], 

        CASE WHEN ([Extent19].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 4 END AS [C53], 

        CAST(NULL AS varchar(1)) AS [C54], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C114]

        FROM    [dbo].[Orgsons] AS [Extent16]

        INNER JOIN [dbo].[Orgsons_Person] AS [Extent17] ON [Extent16].[Id] = [Extent17].[Id]

        INNER JOIN [dbo].[Orgsons_Employee] AS [Extent18] ON [Extent16].[Id] = [Extent18].[Id]

        LEFT OUTER JOIN [dbo].[Positions] AS [Extent19] ON [Extent18].[Position_Id] = [Extent19].[Id]

        WHERE [Extent16].[Id] = '4F65BC85-06FD-4CC7-BB2B-95F2536EF132' ) AS [Project6]

    CROSS APPLY  (SELECT 

        CASE WHEN ([Extent21].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1], 

        12 AS [C2], 

        '0X5X' AS [C3], 

        12 AS [C4], 

        [Extent20].[Id] AS [Id], 

        [Extent20].[ModifiedBy] AS [ModifiedBy], 

        [Extent20].[LastModified] AS [LastModified], 

        CAST(NULL AS varchar(1)) AS [C5], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C61], 

        CASE WHEN ([Extent21].[Id] IS NULL) THEN CAST(NULL AS varchar(1)) ELSE '0X6X' END AS [C62], 

        CASE WHEN ([Extent21].[Id] IS NULL) THEN CAST(NULL AS int) ELSE 14 END AS [C63], 

        [Extent21].[Id] AS [Id1], 

        [Extent21].[ModifiedBy] AS [ModifiedBy1], 

        [Extent21].[LastModified] AS [LastModified1], 

        CAST(NULL AS varchar(1)) AS [C64], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C120], 

        [Extent21].[Description] AS [Description], 

        [Extent21].[Created] AS [Created1], 

        [Extent21].[CreatedBy] AS [CreatedBy], 

        [Extent21].[LeaveOfAbsenceId] AS [LeaveOfAbsenceId], 

        [Extent21].[Version] AS [Version1], 

        CAST(NULL AS datetime2) AS [C121], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C199]

        FROM  [dbo].[LeaveOfAbsences] AS [Extent20]

        LEFT OUTER JOIN [dbo].[Comments] AS [Extent21] ON [Extent20].[Id] = [Extent21].[LeaveOfAbsenceId]

        WHERE ([Project6].[C2] = 0) AND ([Project6].[Id] = [Extent20].[EmployeeId])

    UNION ALL

        SELECT 

        2 AS [C1], 

        12 AS [C2], 

        '0X5X' AS [C3], 

        12 AS [C4], 

        [Extent22].[Id] AS [Id], 

        [Extent22].[ModifiedBy] AS [ModifiedBy], 

        [Extent22].[LastModified] AS [LastModified], 

        CAST(NULL AS varchar(1)) AS [C5], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C50], 

        [Extent22].[RequestReceivedDate] AS [RequestReceivedDate], 

        [Extent22].[PacketSentDate] AS [PacketSentDate], 

        [Extent22].[PacketReceivedDate] AS [PacketReceivedDate], 

        [Extent22].[ReviewSentDate] AS [ReviewSentDate], 

        [Extent22].[ReviewReceivedDate] AS [ReviewReceivedDate], 

        [Extent22].[ApprovedDate] AS [ApprovedDate], 

        [Extent22].[EmployeeId] AS [EmployeeId], 

        [Extent22].[Created] AS [Created], 

        [Extent22].[LeaveTypeId] AS [LeaveTypeId], 

        [Extent22].[LeaveStatusId] AS [LeaveStatusId], 

        [Extent22].[Version] AS [Version], 

        CAST(NULL AS varchar(1)) AS [C51], 

        ...

        CAST(NULL AS varbinary(1)) AS [C134], 

        '0X7X' AS [C135], 

        16 AS [C136], 

        [Extent23].[Id] AS [Id1], 

        [Extent23].[ModifiedBy] AS [ModifiedBy1], 

        [Extent23].[LastModified] AS [LastModified1], 

        CAST(NULL AS varchar(1)) AS [C137], 

        ... 

        CAST(NULL AS varbinary(1)) AS [C198], 

        [Extent23].[StartDate] AS [StartDate], 

        [Extent23].[StopDate] AS [StopDate], 

        [Extent23].[DaysOff] AS [DaysOff], 

        [Extent23].[Created] AS [Created1], 

        [Extent23].[LeaveOfAbsenceId] AS [LeaveOfAbsenceId], 

        [Extent23].[Version] AS [Version1]

        FROM  [dbo].[LeaveOfAbsences] AS [Extent22]

        INNER JOIN [dbo].[LeaveOutOfOffices] AS [Extent23] ON [Extent22].[Id] = [Extent23].[LeaveOfAbsenceId]

        WHERE ([Project6].[C2] = 0) AND ([Project6].[Id] = [Extent22].[EmployeeId])) AS [UnionAll3]) AS [UnionAll4]

ORDER BY [UnionAll4].[C2] ASC, [UnionAll4].[C3] ASC, [UnionAll4].[C7] ASC, [UnionAll4].[C80] ASC, [UnionAll4].[C1] ASC, [UnionAll4].[C373] ASC, [UnionAll4].[C444] ASC;

That amounts to almost 4800 lines of SQL (much of it repetitive and omitted above) for a simple retrieve of an employee by id with some related data! Wow, what can be said about that!

The following query does the same thing in about 20 lines and runs 3 times faster:

       1: SELECT 

       2:     * 

       3: FROM 

       4:     Orgsons_Employee e 

       5:         JOIN 

       6:     Orgsons_Person p ON (e.Id = p.Id) 

       7:         JOIN 

       8:     Orgsons o ON (o.Id = p.Id) 

       9:         LEFT JOIN 

      10:     PhoneNumbers pn ON (pn.OrgsonId = o.Id) 

      11:         LEFT JOIN 

      12:     Addresses a ON (a.OrgsonId = o.Id) 

      13:         LEFT JOIN 

      14:     Positions pos ON (e.Position_Id = pos.Id) 

      15:         LEFT JOIN 

      16:     Evaluations eval ON (eval.EmployeeId = e.Id) 

      17:         LEFT JOIN 

      18:     LeaveOfAbsences loa ON (loa.EmployeeId = e.Id) 

      19:         LEFT JOIN 

      20:     LeaveOutOfOffices loo ON (loo.LeaveOfAbsenceId = loa.Id) 

      21:         LEFT JOIN 

      22:     Comments c ON (c.LeaveOfAbsenceId = loa.Id) 

      23: WHERE 

      24:     e.Id = '4F65BC85-06FD-4CC7-BB2B-95F2536EF132';

The execution plan on SQL Server shows a drastic difference between the two. If I find any hidden tweaking options for this in Entity Framework I’ll update this post.

65910762-320c-4efe-9399-a55908946c16|0|.0

Tags: Entity Framework, SQL

Repository Pattern with Entity Framework 4An original post for once! :-) I am in the process of bringing up a new architecture for a project ...Unit of Work Pattern for Entity Framework 4 and UnityContinuing from my last post on the Repository pattern…here is some background. I am in the ...SQL Server Query OptimizationI came across an article called “13 Things You Should Know About Statistics and the Query Optimizer”...

Comments

7/1/2010 9:37:11 PM #

Hi Mark,

I’m sorry you have encountered this issue. While in the .NET 4.0 release we did numerous improvements to the TSQL generated when querying with the Entity Framework, we continue to work on further improvements for the next release.

Could you please give some more information on your scenario? What is the query that you tried? What is the shape of the model and the mapping strategy?

Thanks,
Kati Iceva,
Entity Framework Developer

Kati Iceva | Reply

7/6/2010 2:14:58 PM #

Hi Kati. So the query is pretty straight forward, although, it has quite a few includes as you could see from the joins above. It breaks down to following in the end:
objectSet<Orgson>.OfType<Employee>().Include("PhoneNumbers", "Addresses", "Position", "Evaluations", "LeaveOfAbsences.Comments", "LeaveOfAbsences.LeaveOutOfOffices").Where(o => o.Id == id).SingleOrDefault();

Our mapping is done as Table Per Type. The above query relies upon a hierarchy of Orgson -> Person -> Employee, with each having a couple of off-shoot joins we are interested in. Phone/Addresses off of Orgson, Then Position, Evals, Leaves are off of Employee.

Thank you for your response Kati. If you have any suggestions on how we may be able to alter our approach to work better with EF, please let me know.

Thanks.

Mark Shiffer | Reply

7/7/2010 2:47:23 AM #

Thank you Mark for the additional information. The complexity of the output query comes from the number of includes. The Entity Framework expects one row per collection element from the database, thus the suggested query isn’t something we can currently produce. We recognize this may be in issue in a scenario like this and are looking into it.

Does it make sense for your application to use less includes and to possibly do lazy loading?

Thanks,
Kati Iceva
Entity Framework Developer

Kati Iceva | Reply

7/12/2010 4:54:29 PM #

Ultimately your suggestion was how I worked around this issue (i.e., reducing the number of includes, not lazy loading yet). However, there are scenarios where the includes are necessary.

I'm glad to hear that it is being worked on. When we get to more performance sensitive operations, it appears we will have to punt and not use Entity Framework though. That is a disappointment as abstracting the data storage logic for the business layer in a more domain friendly manner was a major reason for having an ORM in the first place. Lazy loading may help some, but I'm not sure it will overcome all of the performance issues with the errant SQL being generated.

Mark Shiffer | Reply

Add comment

Name*
E-mail* (Will show your Gravatar icon)
Website
Country Country flag

b i u quote

Comment
Preview

Notify me when new comments are added

MS Software Consulting Blog

Technical findings during my research and daily going abouts...

Examining Entity Framework’s SQL Generation

About the author

Month List

MS Software Consulting Blog

Technical findings during my research and daily going abouts...

Examining Entity Framework’s SQL Generation

About the author

Tag cloud

Month List