Как устранить вложенные соединения в сгенерированном SQL-запросе? ⇐ C#

[Anonymous]

Я создал запрос LINQ в приложении .NET Core для получения некоторых данных. При проверке сгенерированного SQL для LINQ я заметил в нем вложенные запросы. Я хочу оптимизировать свой запрос 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 .[Id], .[EventId], .[EventType], .[Name], .[EventStatus], .[ScheduledTime], .[ScheduledEndTime], .[EndedOn], .[IsHighlightedCard], .[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 EventMarkets { get; set; }
}

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


Подробнее здесь: https://stackoverflow.com/questions/785 ... -sql-query