EF4.2额外的左外部联接到同一表 [英] EF4.2 extra left outer join to same table

问题描述

我知道已经有一些问题，大多数与已解决的旧问题或多个表有关.我看到的任何其他左外部联接"问题都未涉及该问题，我在同一查询的同一张表中得到了一个INNER JOIN和LEFT OUTER JOIN.

I know that there are some questions about this already, most relate to either old issues which were resolved or multiple tables. This question is not covered in any of the other 'left outer join' issues I saw, I get an INNER JOIN and LEFT OUTER JOIN to the same table at the same query.

表大纲:

Users: id (PK)
       Name (VARCHAR) 
       ProfileImageUri (VARCHAR)
Locations: id (PK)
LocationBPNTips: id (PK)
                 TipText (VARCHAR)
                 CreatedAt (Datetime)
                 UserId (int) (FK to User.id, navigation property is called User)
                 LocationId (int) (FK to Location.id)

(还有更多，但并不相关:))

(there is more, but it is not relevant :) )

在我的场景中，我通过投影执行对引用表的查询，并且得到了一个额外的左外部联接，这就是我运行的(注释部分与该问题无关，已注释为了获得更整洁的SQL，EF进行了正确的排序(甚至比我想象的还要好:)))):

In my scenario, I am performing a query to a referenced table via projection and I get an extra left outer join, this is what I run (the commented parts are irrelevant to the problem, commented out for cleaner SQL, EF does the sorting right (even better than I imagined :) ) ):

LocationBPNTips
     .Where(t => t.LocationId == 33)
     //.OrderByDescending(t => intList.Contains(t.UserId))
     //.ThenByDescending(t => t.CreatedAt)
     .Select(tip => new LocationTipOutput
     {
             CreatedAt = tip.CreatedAt,
             Text = tip.TipText,
             LocationId = tip.LocationId,
             OwnerName = tip.User.Name,
             OwnerPhoto = tip.User.ProfileImageUri
     }).ToList();

这是生成的SQL

SELECT 
[Extent1].[LocationId] AS [LocationId], 
[Extent1].[CreatedAt] AS [CreatedAt], 
[Extent1].[TipText] AS [TipText], 
[Extent2].[Name] AS [Name], 
[Extent3].[ProfileImageUri] AS [ProfileImageUri]
FROM   [dbo].[LocationBPNTips] AS [Extent1]
INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[Id]
LEFT OUTER JOIN [dbo].[Users] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id]
WHERE 33 = [Extent1].[LocationId]

如您所见，LEFT OUTER JOIN是在INNER JOIN

我认为最佳代码将是(注意，我手动将Extent3重命名为Extent2，并添加了注释.这不是EF生成的！)-使用我的当前数据，运行速度提高了约22％(通过排序) ，则无需排序，该％应该更高)，因为不需要额外的联接.

I think the optimal code will be (note, I renamed Extent3 to Extent2 manually, and added the comment. this was not generated by EF!!) - with my current data, this runs about 22% faster (with the sorting, this % should be higher without the sort) as no need for an extra join..

SELECT 
[Extent1].[LocationId] AS [LocationId], 
[Extent1].[CreatedAt] AS [CreatedAt], 
[Extent1].[TipText] AS [TipText], 
[Extent2].[Name] AS [Name], 
[Extent2].[ProfileImageUri] AS [ProfileImageUri]
FROM   [dbo].[LocationBPNTips] AS [Extent1]
INNER JOIN [dbo].[Users] AS [Extent2] ON [Extent1].[UserId] = [Extent2].[Id]
--LEFT OUTER JOIN [dbo].[Users] AS [Extent3] ON [Extent1].[UserId] = [Extent3].[Id]
WHERE 33 = [Extent1].[LocationId]

我尝试过的其他查询(在这些查询中，投影变成了匿名类型):

The different queries I have tried (the projection is into an anonymous type in these):

LocationBPNTips
     .Where(t => t.LocationId == 33)
     //.OrderByDescending(t => intList.Contains(t.UserId))
     //.ThenByDescending(t => t.CreatedAt)
     .Select(tip => new 
     {
             CreatedAt = tip.CreatedAt,
             Text = tip.TipText,
             LocationId = tip.LocationId,
             OwnerName = tip.User,
             OwnerPhoto = tip.User
     }).ToList()

SQL输出混乱，它以与上面相同的格式两次选择了整个用户表，先是内部，然后是外部.我认为我可以从理论上理解为什么会发生这种情况，因为我两次请求数据-尽管它可以在内存中完成，而不是通过SQL进行额外的联接-但在我的情况下，我并没有要求数据两次，我只要求一次不同的列.我进行了此测试，以查看双重联接是否一致.

SQL output was messed up, it selected the entire user table twice in the same format as above, inner then left outer. I think that I can see in theory why this happens for this case, because I asked for the data twice - although it could have been done in memory and not by the SQL with an extra join - but in my case I did not ask for data twice, I asked for different columns only once. I did this test to see if the double join is consistent.

我也尝试跑步:

LocationBPNTips
    .Where(t => t.LocationId == 33)
    .Select(tip => new 
    {
            CreatedAt = tip.CreatedAt,
            Text = tip.TipText,
            LocationId = tip.LocationId,
            OwnerName = tip.User.Name
    }).ToList()

这一次返回了干净的单个内部联接，但不是我想要的

This one returned clean, single inner join as expected, but it is not what I am trying to do

问题是:这是一个错误吗?我是否错误地使用了EF?

So the question is: Is this a bug? am I using the EF incorrectly?

推荐答案

我已经看到了类似的问题.我们可以称其为错误或功能.仅仅EF查询生成远非理想. ADO.NET团队修复了每个主要发行版中的一些问题.我目前没有安装June CTP 2011来验证它是否也会在下一个版本的第一个预览版中发生.

I've seen similar problem already. We can call it bug or feature. Simply EF query generation is far from ideal. ADO.NET team fixes some problems with every major release. I don't have June CTP 2011 currently installed to verify if it also happens in the first preview of the next version.

根据此答案在2011年6月的CTP中修复了类似的问题.

According to this answer similar issue was fixed in June CTP 2011.

这篇关于EF4.2额外的左外部联接到同一表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！