Entity Framework generates invalid SQL with LEFT OUTER JOINS

Question

We have installed .NET 4.5, so we should not be exposed to known errors like this from previous versions of EF. Entity Framework creates bad SQL. I have the following request

from a in _practiceRepository.Adjustments select new AdjustmentAndFinancialInformation { IsReport = true, BillingProvider = a.InvoiceReceivable.Invoice.BillingProvider.User.UserName, ServiceLocation = a.InvoiceReceivable.Invoice.Encounter.ServiceLocation.Name, BillingOrganization = a.InvoiceReceivable.Invoice.BillingProvider.BillingOrganization.Name, FinancialTypeGroup = "", AdjustmentTypeName = a.AdjustmentType.Name, AdjustmentTypeShortName = a.AdjustmentType.ShortName, IsDebit = a.AdjustmentType.IsDebit, IsCash = a.AdjustmentType.IsCash, FinancialInformationTypeName = "", FinancialInformationTypeShortName = "", DateTimePosted = a.PostedDateTime, PatientLastName = a.InvoiceReceivable.Invoice.Encounter.Patient.LastName, PatientFirstName = a.InvoiceReceivable.Invoice.Encounter.Patient.FirstName, PatientId = a.InvoiceReceivable.Invoice.Encounter.Patient.Id, Amount = a.Amount, InvoiceDateTime = a.InvoiceReceivable.Invoice.DateTime }; 

it generates the next SQL with tons of LEFT JOINS (which degrades performance by about 10x).

I have checked several times that all relationships are not null.

  SELECT TOP (10) [Project1].[AdjustmentTypeId] AS [AdjustmentTypeId], [Project1].[C1] AS [C1], [Project1].[UserName] AS [UserName], [Project1].[Name] AS [Name], [Project1].[Name1] AS [Name1], [Project1].[Name3] AS [Name2], [Project1].[Name2] AS [Name3], [Project1].[ShortName] AS [ShortName], [Project1].[IsDebit] AS [IsDebit], [Project1].[IsCash] AS [IsCash], [Project1].[C2] AS [C2], [Project1].[C3] AS [C3], [Project1].[PostedDateTime] AS [PostedDateTime], [Project1].[LastName] AS [LastName], [Project1].[FirstName] AS [FirstName], [Project1].[PatientId] AS [PatientId], [Project1].[Amount] AS [Amount], [Project1].[DateTime] AS [DateTime] FROM (SELECT [Extent1].[Amount] AS [Amount], [Extent1].[PostedDateTime] AS [PostedDateTime], [Extent1].[AdjustmentTypeId] AS [AdjustmentTypeId], [Extent5].[UserName] AS [UserName], [Extent8].[Name] AS [Name], [Extent11].[Name] AS [Name1], [Extent12].[Name] AS [Name2], [Extent12].[ShortName] AS [ShortName], [Extent12].[IsCash] AS [IsCash], [Extent12].[IsDebit] AS [IsDebit], [Extent13].[Name] AS [Name3], [Extent16].[LastName] AS [LastName], [Extent19].[FirstName] AS [FirstName], [Extent21].[PatientId] AS [PatientId], [Extent22].[DateTime] AS [DateTime], cast(1 as bit) AS [C1], N'' AS [C2], N'' AS [C3] FROM [model].[Adjustments] AS [Extent1] LEFT OUTER JOIN [model].[InvoiceReceivables] AS [Extent2] ON [Extent1].[InvoiceReceivableId] = [Extent2].[Id] LEFT OUTER JOIN [model].[Invoices] AS [Extent3] ON [Extent2].[InvoiceId] = [Extent3].[Id] LEFT OUTER JOIN [model].[Providers] AS [Extent4] ON [Extent3].[BillingProviderId] = [Extent4].[Id] LEFT OUTER JOIN [model].[Users] AS [Extent5] ON ([Extent4].[UserId] = [Extent5].[Id]) AND ([Extent5].[__EntityType__] IN (N'User',N'Doctor')) LEFT OUTER JOIN [model].[Invoices] AS [Extent6] ON [Extent2].[InvoiceId] = [Extent6].[Id] LEFT OUTER JOIN [model].[Encounters] AS [Extent7] ON [Extent6].[EncounterId] = [Extent7].[Id] LEFT OUTER JOIN [model].[ServiceLocations] AS [Extent8] ON [Extent7].[ServiceLocationId] = [Extent8].[Id] LEFT OUTER JOIN [model].[Invoices] AS [Extent9] ON [Extent2].[InvoiceId] = [Extent9].[Id] LEFT OUTER JOIN [model].[Providers] AS [Extent10] ON [Extent9].[BillingProviderId] = [Extent10].[Id] LEFT OUTER JOIN [model].[BillingOrganizations] AS [Extent11] ON ([Extent10].[BillingOrganizationId] = [Extent11].[Id]) AND ([Extent11].[__EntityType__] IN (N'BillingOrganization',N'PersonBillingOrganization')) LEFT OUTER JOIN [model].[AdjustmentTypes] AS [Extent12] ON [Extent1].[AdjustmentTypeId] = [Extent12].[Id] LEFT OUTER JOIN [model].[FinancialTypeGroups] AS [Extent13] ON [Extent12].[FinancialTypeGroupId] = [Extent13].[Id] LEFT OUTER JOIN [model].[Invoices] AS [Extent14] ON [Extent2].[InvoiceId] = [Extent14].[Id] LEFT OUTER JOIN [model].[Encounters] AS [Extent15] ON [Extent14].[EncounterId] = [Extent15].[Id] LEFT OUTER JOIN [model].[Patients] AS [Extent16] ON [Extent15].[PatientId] = [Extent16].[Id] LEFT OUTER JOIN [model].[Invoices] AS [Extent17] ON [Extent2].[InvoiceId] = [Extent17].[Id] LEFT OUTER JOIN [model].[Encounters] AS [Extent18] ON [Extent17].[EncounterId] = [Extent18].[Id] LEFT OUTER JOIN [model].[Patients] AS [Extent19] ON [Extent18].[PatientId] = [Extent19].[Id] LEFT OUTER JOIN [model].[Invoices] AS [Extent20] ON [Extent2].[InvoiceId] = [Extent20].[Id] LEFT OUTER JOIN [model].[Encounters] AS [Extent21] ON [Extent20].[EncounterId] = [Extent21].[Id] LEFT OUTER JOIN [model].[Invoices] AS [Extent22] ON [Extent2].[InvoiceId] = [Extent22].[Id] ) AS [Project1] ORDER BY [Project1].[DateTime] ASC 

How can I get the Entity Framework to work correctly and generate the correct SQL?

Answer 1

I did a test with the following code:

  using (TestEntities context = new TestEntities()) { var qry = from a in context.Adjustments join b in context.InvoiceReceivables on a.ID equals b.AdjustmentID join c in context.Invoices on b.ID equals c.InvoiceRecievableID select new { a.Name, IRName = b.Name, IName = c.Name}; var list = qry.ToList(); } 

He generated the following SQL:

 SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent2].[Name] AS [Name1], [Extent3].[Name] AS [Name2] FROM [dbo].[Adjustments] AS [Extent1] INNER JOIN [dbo].[InvoiceReceivable] AS [Extent2] ON [Extent1].[ID] = [Extent2].[AdjustmentID] INNER JOIN [dbo].[Invoice] AS [Extent3] ON [Extent2].[ID] = [Extent3].[InvoiceRecievableID] 

It looks like your model has 5 tables that have a 1: 1 ratio, and you used a table approach for each class. You might consider the table for heredity.