使用实体框架生成SQL中的多个连接 [英] Multiple joins in SQL generated with Entity Framework

问题描述

我有具有两个导航属性的Tasks表 - VersionReported和VersionResolved，都存储在表版本中。当我尝试获得包含两个属性的任务列表时，我在SQL中获得了太多的连接（这只是从profiler的sql的一部分）：

  LEFT OUTER JOIN [dbo]。[版本] AS [Extent4] ON [Extent1]。[IDVersionReported] = [Extent4]。[ID] 
 LEFT OUTER JOIN [dbo]。[Versions] AS [ Extent5] ON [Extent1]。[IDVersionReported] = [Extent5]。[ID] 
 LEFT OUTER JOIN [dbo]。[版本] AS [Extent6] ON [Extent1] [IDVersionReported] = [Extent6] ID] 
 LEFT OUTER JOIN [dbo]。[版本] AS [Extent7] ON [Extent1]。[IDVersionReported] = [Extent7]。[ID] 
 LEFT OUTER JOIN [dbo] AS [Extent8] ON [Extent1]。[IDVersionResolved] = [Extent8]。[ID] 
 LEFT OUTER JOIN [dbo]。[版本] AS [Extent9] ON [Extent1] [IDVersionResolved] = [Extent9] 。[ID] 
 LEFT OUTER JOIN [dbo]。[版本] AS [Extent10] ON [Extent1]。[IDVersionResolved] = [Extent10]。[ID] 
 LEFT OUTER JOIN [dbo]版本] AS [Extent11] ON [Extent1]。[IDVersionResolved] = [Extent11]。[ID] 
  

分机ent1是Tasks表。我知道当两个或更多的导航属性导致同一个表时，EntityFramework有问题，但有没有人找到解决方案？

解决方案

我在大约2个月前加入了一个EF项目，我们也注意到了这个问题。

我想最简单（最好的）解决方案是创建一个全部的视图JOIN魔术，并在EF中映射该视图。

另一方面，要求每个性能问题的视图可能不是我们预期的，当我们开始使用EF。 / p>

I have Tasks table with two navigation properties - VersionReported and VersionResolved, both stored in table Versions. When I try to get Task list with included both properties, I get too many joins in SQL (this is only part of sql from profiler):

LEFT OUTER JOIN [dbo].[Versions] AS [Extent4] ON [Extent1].[IDVersionReported] = [Extent4].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent5] ON [Extent1].[IDVersionReported] = [Extent5].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent6] ON [Extent1].[IDVersionReported] = [Extent6].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent7] ON [Extent1].[IDVersionReported] = [Extent7].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent8] ON [Extent1].[IDVersionResolved] = [Extent8].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent9] ON [Extent1].[IDVersionResolved] = [Extent9].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent10] ON [Extent1].[IDVersionResolved] = [Extent10].[ID]
LEFT OUTER JOIN [dbo].[Versions] AS [Extent11] ON [Extent1].[IDVersionResolved] = [Extent11].[ID]

Extent1 is Tasks table. I know that EntityFramework has problem when two or more navigation properties lead to the same table, but did anyone find a solution?

解决方案

I joined an EF project about 2 months ago, and we noticed this problem too.

I guess the simplest (and best performing) solution is to create a view which does all the JOIN magic, and map that view in EF.

On the other hand, requiring views for every performance problem is probably not what we expected when we started with EF.

这篇关于使用实体框架生成SQL中的多个连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！