c# - 是否可以暗示 EntityFramework Core 使用内部连接而不是 EXISTS 子查询来获取导航属性集合条件？

Question

我有四个表:Users、Songs、Genres 和 FavoriteGenres。

用户可以选择最喜欢的流派，当他们导航到他们的仪表板时，他们会看到一个属于他们最喜欢的流派的歌曲列表。

我有以下 EF 核心代码来完成此任务:

dbContext
    .Songs
    .Include(song => song.Genre)
    .Where(song => song.Genre.FavoriteGenres.Any(favoriteGenre => favoriteGenre.UserId == userId))
    .OrderBy(song => song.CreatedDateTimeOffset)
    .ToListAsync(cancellationToken);

输出的查询(工作正常)是:

SELECT [s].[Id], [s].[Title], [s].[CreatedDateTimeOffset], [g].[Id], [g].[Name]
FROM [Songs] AS [s]
INNER JOIN [Genres] AS [g] ON [s].[GenreId] = [g].[Id]
WHERE EXISTS (
  SELECT 1
  FROM [FavoriteGenres] AS [f]
  WHERE ([g].[Id] = [f].[GenreId]) AND ([f].[UserId] = @__userId_0))
ORDER BY [s].[CreatedDateTimeOffset] DESC

我只是想知道是否可以提示 EF 核心生成以下查询，我认为这可能是更优化的(尽管我很乐意被告知我错了!):

SELECT [s].[Id], [s].[Title], [s].[CreatedDateTimeOffset], [g].[Id], [g].[Name]
FROM [Songs] AS [s]
INNER JOIN [Genres] AS [g] ON [s].[GenreId] = [g].[Id]
INNER JOIN [FavoriteGenres] AS [f] ON [f].[GenreId] = [g].[Id]
WHERE [f].[UserId] = @__userId_0
ORDER BY [s].[CreatedDateTimeOffset] DESC

执行计划看起来非常相似，但老实说，我不是阅读它们的专家。任何形式的帮助将不胜感激:)

Answer 1

虽然 @IvanStoev 的评论是正确的并且没有必要重写查询，如果您仍然关心不同的查询生成，一个简单的方法是重新排序您的表包括。由于您最终不会将歌曲作为根实体，而是喜欢的流派，因此您可能希望重新排列返回的实体:

var result = context.FavoriteGenres
    .Include(favoriteGenre => favoriteGenre.Genre)
    .ThenInclude(genre => genre.Songs)
    .Where(favoriteGenre => favoriteGenre.UserId == userId)
    .AsEnumerable() // <-- switch to client-evaluation to return songs instead of favorite genres
    .SelectMany(favoriteGenre => favoriteGenre.Genre.Songs)
    .OrderBy(song => song.CreatedDateTimeOffset)
    .ToList();

这将导致生成以下 SQL:

SELECT [f].[UserId], [f].[GenreId], [g].[Id], [g].[Name], [s].[Id], [s].[CreatedDateTimeOffset], [s].[GenreId], [s].[Title]
FROM [FavoriteGenres] AS [f]
INNER JOIN [Genres] AS [g] ON [f].[GenreId] = [g].[Id]
LEFT JOIN [Songs] AS [s] ON [g].[Id] = [s].[GenreId]
WHERE [f].[UserId] = @__userId_0
ORDER BY [f].[UserId], [f].[GenreId], [g].[Id], [s].[Id]