简单的 Linq 查询对同一个表进行了重复连接?

问题描述

(来自 Julia Lerman 新的实体框架书中的一个例子.)我有一个包含两个表的数据库，联系人和地址.Contact 表有一个 ContactID (int)，还有名字、姓氏等.Address 表有一个 ContactID，还有城市、州、邮编等.

(From an example in the new Entity Framework book by Julia Lerman.) I have a database with two tables, Contact and Address. The Contact table has a ContactID (int), as well as first name, last name, etc. The Address table has a ContactID, as well as city, state, zip, etc.

这是一个简单的 LINQ 查询:

Here is a simple LINQ query:

var addressGraphQuery = from a in context.Addresses.Include("Contact")
                        orderby a.Contact.LastName, a.Contact.FirstName
                        select a;

从 SQL Profiler，我看到以下内容:

From SQL Profiler, I see the following:

SELECT
    [Extent1].[addressID] AS [addressID],
    [Extent1].[City] AS [City],
    [Extent1].[StateProvince] AS [StateProvince],
    -- etc
    [Extent3].[ContactID] AS [ContactID1],
    [Extent3].[FirstName] AS [FirstName],
    [Extent3].[LastName] AS [LastName],
    -- etc
FROM   [dbo].[Address] AS [Extent1]
INNER JOIN [dbo].[Contact] AS [Extent2] ON [Extent1].[ContactID] = [Extent2].[ContactID]
LEFT OUTER JOIN [dbo].[Contact] AS [Extent3] ON [Extent1].[ContactID] = [Extent3].[ContactID]
ORDER BY [Extent2].[LastName] ASC, [Extent3].[FirstName] ASC

它与 Contact 表连接了两次！为什么?有没有简单的方法可以防止这种情况发生?

It joins against the Contact table twice! Why? Is there an easy way to prevent this?

谜团越来越深.当我删除 orderby 时，连接消失了.当我设置 context.ContextOptions.LazyLoadingEnabled = false 时，连接不会消失.

The mystery deepens. The joins disappear when I remove the orderby's. The joins do not disappear when I set context.ContextOptions.LazyLoadingEnabled = false.

这里也有类似的问题:

实体框架 4 中的左外联接过多?

我会看看是否有更高的权力我可以问...

I'll see if there's a Higher Power I can ask...

推荐答案

感谢您报告此问题.是的，预计不会看到两个连接.这是一个已在当前(尚未发布)位中修复的错误.在当前位中，相同的查询产生:

Thank you for reporting this issue. Yes, it is not expected to see both joins. This is a bug that has been fixed in the current (not yet released) bits. In the current bits the same query produces:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Name] AS [Name],
[Extent1].[ContactId] AS [ContactId],
...
[Extent2].[Id] AS [Id1],
..
[Extent2].[FirstName] AS [FirstName],
[Extent2].[LastName] AS [LastName]
FROM  [dbo].[Address] AS [Extent1]
INNER JOIN [dbo].[Contact] AS [Extent2] ON [Extent1].[ContactId] = [Extent2].[Id] ORDER BY [Extent2].[LastName] ASC, [Extent2].[FirstName] ASC

我的一点玩意表明，即使在 4.0 上，这种情况也只会在关系为 1:many 时发生，0..1:many 似乎没问题.

I bit of playing shows that even on 4.0 this only happens when the relationship is 1:many, 0..1:many seems fine.

谢谢，

卡蒂·伊斯瓦
实体框架开发人员
微软

Thanks,

Kati Iceva
Entity Framework Developer
Microsoft

这篇关于简单的 Linq 查询对同一个表进行了重复连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！