Linq to Entities Relationships with Redundant Connection in Generated SQL

Question

I have a .NET Entity data model configured with relationships, so I don’t need to manually attach to the objects in my LINQ queries. The LINQ query provides a link to another CustomerUserField table:

from c in Customer.GetCustomer(this.ClientId, intRecordId) select new { c.TitleId, c.FirstName, c.LastName, c.Phone, c.MobilePhone, c.Fax, c.EmailAddress, c.CustomerUserField.Text1, c.CustomerUserField.Text2, c.CustomerUserField.Text3, c.CustomerUserField.Text4, c.CustomerUserField.Text5 }; 

It looks nice and neat in C #, however, the generated SQL creates a separate left outer join for each column in the reference table:

 SELECT [Limit1].[C1] AS [C1], [Limit1].[TitleId] AS [TitleId], ... FROM [dbo].[Customer] AS [Extent1] LEFT OUTER JOIN [dbo].[CustomerUserField] AS [Extent2] ON [Extent1].[CustomerId] = [Extent2].[CustomerId] LEFT OUTER JOIN [dbo].[CustomerUserField] AS [Extent9] ON [Extent2].[CustomerUserFieldId] = [Extent9].[CustomerUserFieldId] LEFT OUTER JOIN [dbo].[CustomerUserField] AS [Extent10] ON [Extent2].[CustomerUserFieldId] = [Extent10].[CustomerUserFieldId] LEFT OUTER JOIN [dbo].[CustomerUserField] AS [Extent11] ON [Extent2].[CustomerUserFieldId] = [Extent11].[CustomerUserFieldId] LEFT OUTER JOIN [dbo].[CustomerUserField] AS [Extent12] ON [Extent2].[CustomerUserFieldId] = [Extent12].[CustomerUserFieldId] LEFT OUTER JOIN [dbo].[CustomerUserField] AS [Extent13] ON [Extent2].[CustomerUserFieldId] = [Extent13].[CustomerUserFieldId]... 

This SQL is very slow since only one left outer join is required. Any ideas how I can change my LINQ to make only one connection?

Thanks in advance!

Anthony.

Answer 1

you can “upload” your customers table using the following code:

 DataLoadOptions options = new DataLoadOptions(); options.LoadWith<Customer>(c => c.CustomerUserField); using (ToDoDataContext context = new ToDoDataContext()) { context.LoadOptions = options; //Your code goes here } 

This means that your connection is only done once, since it simultaneously loads the table.