LINQ to Entities加入实例而不是id会产生讨厌的SQL

按ID

from companyDirector in CompanyDirectors
join contactAddress in ContactAddresses
  on companyDirector.ContactAddress.Id equals contactAddress.Id
select new {companyDirector,contactAddress}

生成

FROM  [COMPANY] AS [Extent1]
    INNER JOIN [ADDRESS] AS [Extent2] ON [Extent1].[CONTACT_ADDRESS_ID] = [Extent2].[CONTACT_ADDRESS_ID]

例如

from companyDirector in CompanyDirectors
join contactAddress in ContactAddresses
  on companyDirector.ContactAddress equals contactAddress
select new {companyDirector,contactAddress}

生成

FROM  [COMPANY] AS [Extent1]
INNER JOIN [ADDRESS] AS [Extent2] ON  EXISTS (SELECT 
    1 AS [C1]
    FROM    ( SELECT 1 AS X ) AS [SingleRowTable1]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[CONTACT_ADDRESS_ID] AS [CONTACT_ADDRESS_ID]
        FROM [ADDRESS] AS [Extent3]
        WHERE [Extent1].[CONTACT_ADDRESS_ID] = [Extent3].[CONTACT_ADDRESS_ID] ) AS [Project1] ON 1 = 1
    LEFT OUTER JOIN  (SELECT 
        [Extent4].[CONTACT_ADDRESS_ID] AS [CONTACT_ADDRESS_ID]
        FROM [ADDRESS] AS [Extent4]
        WHERE [Extent1].[CONTACT_ADDRESS_ID] = [Extent4].[CONTACT_ADDRESS_ID] ) AS [Project2] ON 1 = 1
    WHERE [Project1].[CONTACT_ADDRESS_ID] = [Extent2].[CONTACT_ADDRESS_ID]
)

对我来说看起来效率很低,迫使你进入id路由.为什么要做左边的连接两次,不介意一次？

解决方法

>可能的是,ContractAddresses或者可能只在实体模型中的基础表中的Id字段不能被定义为主键.我有点怀疑这是问题,但值得双重检查.
> equals关键字可能没有一个很好的方法来比较连接中的两个对象之间的相等.在一个快速的网页搜索中,我没有找到什么是equals用于比较,但this MSDN how-to让我相信Equals和GetHashCode方法是相关的(即使不涉及复合键).如果您只是使用默认的object.Equals继承方法,Linq提供者必须以某种方式找出参考平等,我想象可能会导致一些奇怪的结果.

不过,我很喜欢@Craig Stuntz在解释他的评论.此外,您可能希望获得更长查询的执行计划,以查看它是否真的像看起来一样糟糕;查询优化器可能会比代码指示的更好.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!