Linq to Entities excessive joins in generated SQL

Question

Hi there,

I have a .NET Entity Data Model set up with relationships so that I don't need to manually join entities in my LINQ queries. The LINQ query below references another table CustomerUserField:

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 & neat in C#, however the generated SQL creates a separate left outer join for every column in the referenced 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, as only 1 left outer join is required. Any ideas how I can change my LINQ to only perform a single join?

Thanks in advance!

Anthony.

Answer 1

you could "eager load" your customer options 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
}

That should mean that your join is only executed once, as it loads the table at the same time