如何消除.NET Core LINQ查询生成SQL中的嵌套连接？

我在.NET Core应用中编写了LINQ查询获取数据，检查生成的SQL时发现存在嵌套查询，希望优化LINQ查询来消除这些嵌套。

原LINQ查询 #

from se in Context.SportEvents
where se.EventId == eventId
from eventMarket in se.EventMarkets
join marketOdds in Context.EventMarketOdds on eventMarket.Id equals marketOdds.EventMarketId into marketOddsGroup
from marketOdds in marketOddsGroup.DefaultIfEmpty()
join marketSpecifiers in Context.EventMarketSpecifiers on eventMarket.Id equals marketSpecifiers.EventMarketId into marketSpecifiersGroup
from marketSpecifiers in marketSpecifiersGroup.DefaultIfEmpty()
select new SportEvent()
{
    Id = se.Id,
    EventId = se.EventId,
    EventType = se.EventType,
    Name = se.Name,
    EventStatus = se.EventStatus,
    ScheduledTime = se.ScheduledTime,
    ScheduledEndTime = se.ScheduledEndTime,
    EndedOn = se.EndedOn,
    IsHighlightedCard = se.IsHighlightedCard,
    IsHighlightedTopMatch = se.IsHighlightedTopMatch,
    IsOnline = se.IsOnline,
    SportId = se.SportId,
    TournamentId = se.TournamentId,
    EventMarkets = se.EventMarkets.Select(em => new EventMarket
    {
        Id = em.Id,
        EventId = em.EventId,
        MarketThirdPartyId = em.MarketThirdPartyId,
        MarketSportId = em.MarketSportId,
        MarketName = em.MarketName,
        IsActive = em.IsActive,
        IsFavorite = em.IsFavorite,
        EventMarketStatus = em.EventMarketStatus,
        EventMarketOdds = em.EventMarketOdds.Select(emo => new EventMarketOdds
        {
            Id = emo.Id,
            Odds = emo.Odds,
            OutcomeThirdPartyId = emo.OutcomeThirdPartyId
        }).ToList(),
        EventMarketSpecifiers = em.EventMarketSpecifiers.Select(ems => new EventMarketSpecifier
        {
            SpecifierName = ems.SpecifierName,
            SpecifierValue = ems.SpecifierValue
        }).ToList()
    }).ToList()
};

生成的嵌套SQL #

SELECT [s].[Id], [s].[EventId], [s].[EventType], [s].[Name], [s].[EventStatus], [s].[ScheduledTime], [s].[ScheduledEndTime], [s].[EndedOn], [s].[IsHighlightedCard], [s].[IsHighlightedTopMatch], [s].[IsOnline], [s].[SportId], [s].[TournamentId], [t].[Id], [t].[EventId], [t].[MarketThirdPartyId], [t].[MarketSportId], [t].[MarketName], [t].[IsActive], [t].[IsFavorite], [t].[EventMarketStatus], [t].[Id0], [t].[Odds], [t].[OutcomeThirdPartyId], [t].[SpecifierName], [t].[SpecifierValue], [t].[Id1]
FROM [Events].[SportEvents] AS [s]
LEFT JOIN (
    SELECT [e].[Id], [e].[EventId], [e].[MarketThirdPartyId], [e].[MarketSportId], [e].[MarketName], [e].[IsActive], [e].[IsFavorite], [e].[EventMarketStatus], [e0].[Id] AS [Id0], [e0].[Odds], [e0].[OutcomeThirdPartyId], [e1].[SpecifierName], [e1].[SpecifierValue], [e1].[Id] AS [Id1], [e].[SportEventId]
    FROM [Events].[EventMarkets] AS [e]
    LEFT JOIN [Events].[EventMarketOdds] AS [e0] ON [e].[Id] = [e0].[EventMarketId]
    LEFT JOIN [Events].[EventMarketSpecifiers] AS [e1] ON [e].[Id] = [e1].[EventMarketId]
) AS [t] ON [s].[Id] = [t].[SportEventId]
WHERE [s].[EventId] = @__eventId_0
ORDER BY [s].[Id], [t].[Id], [t].[Id0]

模型类定义 #

class SportEvent { 
    public int Id {get; set;} 
    public string Name { get; set; } 
    public List<EventMarket> EventMarkets { get; set; } 
} 

class EventMarket { 
    public int Id { get; set; } 
    public string Name { get; set; } 
    public List<EventMarketOdds>? EventMarketOdds { get; set; } 
    public List<EventMarketSpecifier>? EventMarketSpecifiers { get;set;} 
} 

优化方案 #

原查询的问题在于同时手动执行join操作和使用导航属性投影，EF Core无法识别这是同一关联关系，因此会将手动join的部分打包成嵌套子查询。只需去掉手动join，完全依赖EF Core的导航属性来关联数据，即可生成无嵌套的SQL。

优化后的LINQ查询 #

from se in Context.SportEvents
where se.EventId == eventId
select new SportEvent()
{
    Id = se.Id,
    EventId = se.EventId,
    EventType = se.EventType,
    Name = se.Name,
    EventStatus = se.EventStatus,
    ScheduledTime = se.ScheduledTime,
    ScheduledEndTime = se.ScheduledEndTime,
    EndedOn = se.EndedOn,
    IsHighlightedCard = se.IsHighlightedCard,
    IsHighlightedTopMatch = se.IsHighlightedTopMatch,
    IsOnline = se.IsOnline,
    SportId = se.SportId,
    TournamentId = se.TournamentId,
    EventMarkets = se.EventMarkets.Select(em => new EventMarket
    {
        Id = em.Id,
        EventId = em.EventId,
        MarketThirdPartyId = em.MarketThirdPartyId,
        MarketSportId = em.MarketSportId,
        MarketName = em.MarketName,
        IsActive = em.IsActive,
        IsFavorite = em.IsFavorite,
        EventMarketStatus = em.EventMarketStatus,
        EventMarketOdds = em.EventMarketOdds.Select(emo => new EventMarketOdds
        {
            Id = emo.Id,
            Odds = emo.Odds,
            OutcomeThirdPartyId = emo.OutcomeThirdPartyId
        }).ToList(),
        EventMarketSpecifiers = em.EventMarketSpecifiers.Select(ems => new EventMarketSpecifier
        {
            SpecifierName = ems.SpecifierName,
            SpecifierValue = ems.SpecifierValue
        }).ToList()
    }).ToList()
};

优化后生成的SQL #

优化后EF Core会直接生成多表左连接的SQL，不再有嵌套子查询：

SELECT [s].[Id], [s].[EventId], [s].[EventType], [s].[Name], [s].[EventStatus], [s].[ScheduledTime], [s].[ScheduledEndTime], [s].[EndedOn], [s].[IsHighlightedCard], [s].[IsHighlightedTopMatch], [s].[IsOnline], [s].[SportId], [s].[TournamentId], [em].[Id], [em].[EventId], [em].[MarketThirdPartyId], [em].[MarketSportId], [em].[MarketName], [em].[IsActive], [em].[IsFavorite], [em].[EventMarketStatus], [emo].[Id], [emo].[Odds], [emo].[OutcomeThirdPartyId], [ems].[SpecifierName], [ems].[SpecifierValue]
FROM [Events].[SportEvents] AS [s]
LEFT JOIN [Events].[EventMarkets] AS [em] ON [s].[Id] = [em].[SportEventId]
LEFT JOIN [Events].[EventMarketOdds] AS [emo] ON [em].[Id] = [emo].[EventMarketId]
LEFT JOIN [Events].[EventMarketSpecifiers] AS [ems] ON [em].[Id] = [ems].[EventMarketId]
WHERE [s].[EventId] = @__eventId_0
ORDER BY [s].[Id], [em].[Id], [emo].[Id]

核心原因 #

EF Core的导航属性已经映射了表间关联关系，手动join会让EF Core认为是额外的关联操作，从而生成嵌套查询。去掉手动join后，EF Core能正确识别投影中使用的导航属性对应的关联，直接生成扁平的多表连接SQL。

内容的提问来源于stack exchange，提问作者user23507681