简单的Linq查询与同一表重复连接？ [英] Simple Linq query has duplicated join against same table?

问题描述

（来自Julia Lerman的新实体框架书中的一个例子）我有一个包含两个表的联系人和地址的数据库。联系表有一个ContactID（int），以及名字，姓氏等。地址表有一个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

它与联系表连接两次！为什么？有没有一个简单的方法来防止这种情况？

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.

还有一个类似的问题这里：

There is also a similar question here:

实体框架中的外部连接太多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

发生时的关系是1：很多，0..1：很多很好。

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

谢谢，


Kati Iceva

实体框架开发人员

Microsoft

Thanks,

Kati Iceva
Entity Framework Developer
Microsoft

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