Linq to Entities在生成的SQL中过度联接 [英] Linq to Entities excessive joins in generated SQL

问题描述

我有一个设置有关系的.NET实体数据模型，因此不需要在LINQ查询中手动加入实体.下面的LINQ查询引用了另一个表 CustomerUserField :

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
};

看起来不错& C#中的代码整洁，但是生成的SQL为引用表中的每一列创建了一个单独的左外部联接:

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]...

此SQL非常慢，因为仅需要1个左外部联接.有什么想法可以将LINQ更改为仅执行单个联接吗?

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?

提前谢谢！

安东尼.

推荐答案

您可以使用以下代码渴望加载"您的客户选择表:

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

这篇关于Linq to Entities在生成的SQL中过度联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！