c# - 使用 Sub Select 而不是 INNER JOIN 的 Entity Framework

Question

我有以下类(class)，例如

public class Team
{
    [Key]
    public virtual Int32 TeamId { get; set; }
    [Required]
    public virtual String Name { get; set; }
    public virtual String Description { get; set; }
    public virtual ICollection<TeamFeed> TeamFeeds { get; set; }
}

public class TeamFeed
{       
    public Int32 TeamFeedId { get; set; }
    [Required]
    public Int32 TeamId { get; set; }
    public virtual bool IsEnabled { get; set; }
    public virtual Team Team { get; set; }
}

我有 LazyLoadingEnabled = false 和 ProxyCreationEnabled = false

当我做的时候

var team = db.Teams.Where(x => x.TeamId == 1).Include(x=>x.TeamFeeds);

EF 生成的 SQL 如下所示:

SELECT 
[Project1].[TeamId] AS [TeamId], 
[Project1].[Name] AS [Name], 
[Project1].[Description] AS [Description], 
[Project1].[C1] AS [C1], 
[Project1].[TeamFeedId] AS [TeamFeedId], 
[Project1].[TeamId1] AS [TeamId1], 
[Project1].[IsEnabled] AS [IsEnabled], 
FROM ( SELECT 
    [Extent1].[TeamId] AS [TeamId], 
    [Extent1].[Name] AS [Name], 
    [Extent1].[Description] AS [Description],  
    [Extent2].[TeamFeedId] AS [TeamFeedId], 
    [Extent2].[TeamId] AS [TeamId1], 
    [Extent2].[IsEnabled] AS [IsEnabled],  
    CASE WHEN ([Extent2].[TeamFeedId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]
    FROM  [dbo].[Teams] AS [Extent1]
    LEFT OUTER JOIN [dbo].[TeamFeeds] AS [Extent2] ON [Extent1].[TeamId] = [Extent2].[TeamId]
    WHERE 1 = [Extent1].[TeamId]
)  AS [Project1]
ORDER BY [Project1].[TeamId] ASC, [Project1].[C1] ASC

我不明白为什么它没有使用我会使用的 SQL，而是创建了冗长的 SQL 语句。

SELECT 
* --use all just for clarity in the example
FROM TEAMS T

INNER JOIN TEAMFEEDS TF
ON T.TEAMID = TF.TEAMID

ORDER BY 
T.TEAMID

我是 EF 的新手，所以我可能没有理解某些东西。

谢谢

Answer 1

首先，当 TeamID = 1 的团队没有订阅时，您使用 INNER JOIN 的查询将不会返回任何内容。 LEFT JOIN 必须在这里使用，这就是 EF 在 ineer SELECT 中所做的:

SELECT 
    *
    FROM  [dbo].[Teams] AS [Extent1]
    LEFT OUTER JOIN [dbo].[TeamFeeds] AS [Extent2] ON [Extent1].[TeamId] = [Extent2].[TeamId]
    WHERE 1 = [Extent1].[TeamId]

下面的 scary 行只是让 EF 知道，如果指定了 JOIN 的左侧站点 - 如果 Team 没有，它返回 0 Feed 和 1(如果至少找到一个 Feed)。

CASE WHEN ([Extent2].[TeamFeedId] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C1]

并且外部 SELECT 语句只是将数据库列名称映射到 EF 模型属性名称并执行 ORDER BY。

希望这足以解释为什么 EF 生成了这样一个冗长的 SQL 语句。或者更确切地说，为什么这个语句看起来很可怕，但实际上并不比您的简单 SELECT * FROM Teams JOIN Feeds 多多少。