任何人都可以解释为什么通过实体加入而不是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路由.为什么要做左边的连接两次,不介意一次？
解决方法
我不能说ADO.NET团队的头脑或代码是什么.也就是说,我看到两个可能的问题：
>可能的是,ContractAddresses或者可能只在实体模型中的基础表中的Id字段不能被定义为主键.我有点怀疑这是问题,但值得双重检查.
> equals关键字可能没有一个很好的方法来比较连接中的两个对象之间的相等.在一个快速的网页搜索中,我没有找到什么是equals用于比较,但this MSDN how-to让我相信Equals和GetHashCode方法是相关的(即使不涉及复合键).如果您只是使用默认的object.Equals继承方法,Linq提供者必须以某种方式找出参考平等,我想象可能会导致一些奇怪的结果.
不过,我很喜欢@Craig Stuntz在解释他的评论.此外,您可能希望获得更长查询的执行计划,以查看它是否真的像看起来一样糟糕;查询优化器可能会比代码指示的更好.
