简单的 Linq 查询对同一个表进行了重复连接? [英] Simple Linq query has duplicated join against same table?

问题描述

(来自 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 查询对同一个表进行了重复连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！