Linq to Entities excessive joins in generated SQL

linq-to-entities

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.

Best Answer

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

Related Solutions

Linq to Entity with multiple left outer joins

Solved it!

Final Output:

theCompany.id: 1  
theProduct.id: 14  
theCompany.id: 2  
theProduct.id: 14  
theCompany.id: 3

Here is the Scenario

1 - The Database

--Company Table
CREATE TABLE [theCompany](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [value] [nvarchar](50) NULL,
 CONSTRAINT [PK_theCompany] PRIMARY KEY CLUSTERED 
( [id] ASC ) WITH (
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO


--Products Table
CREATE TABLE [theProduct](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [value] [nvarchar](50) NULL,
 CONSTRAINT [PK_theProduct] PRIMARY KEY CLUSTERED 
( [id] ASC
) WITH (    
    PAD_INDEX  = OFF, 
    STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS  = ON, 
    ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY];
GO


--CompanyProduct Table
CREATE TABLE [dbo].[CompanyProduct](
    [fk_company] [int] NOT NULL,
    [fk_product] [int] NOT NULL
) ON [PRIMARY];    
GO

ALTER TABLE [CompanyProduct]  WITH CHECK ADD CONSTRAINT
    [FK_CompanyProduct_theCompany] FOREIGN KEY([fk_company]) 
    REFERENCES [theCompany] ([id]);
GO

ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT 
    [FK_CompanyProduct_theCompany];
GO

ALTER TABLE [CompanyProduct]  WITH CHECK ADD CONSTRAINT 
    [FK_CompanyProduct_theProduct] FOREIGN KEY([fk_product]) 
 REFERENCES [dbo].[theProduct] ([id]);
GO

ALTER TABLE [dbo].[CompanyProduct] CHECK CONSTRAINT 
    [FK_CompanyProduct_theProduct];

2 - The Data

SELECT [id] ,[value] FROM theCompany
id          value
----------- --------------------------------------------------
1           company1
2           company2
3           company3

SELECT [id] ,[value]  FROM theProduct
id          value
----------- --------------------------------------------------
14          Product 1


SELECT [fk_company],[fk_product] FROM CompanyProduct;
fk_company  fk_product
----------- -----------
1           14
2           14

3 - The Entity in VS.NET 2008

alt text http://i478.photobucket.com/albums/rr148/KyleLanser/companyproduct.png
The Entity Container Name is 'testEntities' (as seen in model Properties window)

4 - The Code (FINALLY!)

testEntities entity = new testEntities();

var theResultSet = from c in entity.theCompany
select new { company_id = c.id, product_id = c.theProduct.Select(e=>e) };

foreach(var oneCompany in theResultSet)
{
   Debug.WriteLine("theCompany.id: " + oneCompany.company_id);
    foreach(var allProducts in oneCompany.product_id)
    {
        Debug.WriteLine("theProduct.id: " + allProducts.id);
    }
}

5 - The Final Output

theCompany.id: 1  
theProduct.id: 14  
theCompany.id: 2  
theProduct.id: 14  
theCompany.id: 3

Linq to Entities How to return all records from Parent

You need to perform a left join by using the "Into" and "DefaultIfEmpty()" keywords:

Dim query = From emp In entities.Employee _
        Join sal In entities.Sales On emp.Employee_ID Equals sal.Employee_ID Into LeftJoinSal _
        From ljs In LeftJoinSal.DefaultIfEmpty() _
        Select _
            emp, _
            ljs

This site shows another simple example: GeeksWithBlogs. Though it is in C#.

The key is that you need to join into a new name to prevent it from filtering the results from your first table (which hides the rows that don't join), then select from that using the DefaultIfEmpty() function, which provides a default (null) value in the cases where there is no join.

Best Answer

Related Solutions

Linq to Entity with multiple left outer joins

Linq to Entities How to return all records from Parent

Related Topic