entity-framework-core - EF Core 多个左连接

Question

我在 asp.net mvc core 1.1.0 项目中使用 EF Core 并且有一个相当复杂的查询。

_context
.Profiles
.Include(p => p.Blog)
 .ThenInclude(b => b.Network)
.Include(p => p.Blog)
 .ThenInclude(i => i.AgeDistributions)
  .ThenInclude(i => i.AgeRange)
.Include(p => p.Blog)
 .ThenInclude(b => b.GenderDistributions)
.Include(p => p.Instagram)
 .ThenInclude(i => i.Network)
.Include(p => p.Instagram)
 .ThenInclude(i => i.AgeDistributions)
  .ThenInclude(i => i.AgeRange)
.Include(p => p.Instagram)
 .ThenInclude(b => b.GenderDistributions)
.Include(p => p.Youtube)
 .ThenInclude(y => y.Network)
.Include(p => p.Youtube)
 .ThenInclude(i => i.AgeDistributions)
  .ThenInclude(i => i.AgeRange)
.Include(p => p.Youtube)
 .ThenInclude(b => b.GenderDistributions)
.Include(p => p.Snapchat)
 .ThenInclude(s => s.Network)
.Include(p => p.Musically)
.Include(p => p.ProfileCategories)
 .ThenInclude(pc => pc.Category)
.Include(p => p.Tags)
 .ThenInclude(tag => tag.Tag)
.Where(p => !p.Deleted);

每个社交平台都可以有任何类型的统计数据。例如，AgeDistributions 使用具有 PlatformId 的基类建模，每个派生的 {Platform}AgeDistribution 指定导航属性，以便设置外部键正确。

public class AgeInterval {
  public int Id { get; set; }
  // At most five length. -18, 18-24, ..., 65-
  public string Interval { get; set; }
}

public class PlatformAgeStatistics {
  public int PlatformId { get; set; }
  public int IntervalId { get; set; } 
  public AgeInterval Interval { get; set; } 
  public decimal Distribution { get; set; }
}

public class InstagramAgeStatistics : PlatformAgeStatistics  {
  [ForeignKey("PlatformId")]
  public Instagram Platform { get; set; } // 
}

上述查询有时会花费很长时间(30 秒后数据库执行超时)并检查 sql 让我觉得我遇到了 EF 无法正确确定的建模问题，或者 EF 只是生成了次优的 SQL。结果集使用 skip 和 take 进行分页，目前获取十条记录需要时间。

这是执行的第一个SQL

SELECT -- Emitted
FROM [Profiles] AS [p]
LEFT JOIN [BlogChannels] AS [b] ON [b].[ProfileId] = [p].[Id]
LEFT JOIN [InstagramChannels] AS [i] ON [i].[ProfileId] = [p].[Id]
LEFT JOIN [YoutubeChannels] AS [y] ON [y].[ProfileId] = [p].[Id]
LEFT JOIN [BlogChannels] AS [b2] ON [b2].[ProfileId] = [p].[Id]
LEFT JOIN [InstagramChannels] AS [i2] ON [i2].[ProfileId] = [p].[Id]
LEFT JOIN [YoutubeChannels] AS [y2] ON [y2].[ProfileId] = [p].[Id]
LEFT JOIN [BlogChannels] AS [b4] ON [b4].[ProfileId] = [p].[Id]
LEFT JOIN [Networks] AS [n] ON [b4].[NetworkId] = [n].[Id]
LEFT JOIN [InstagramChannels] AS [i4] ON [i4].[ProfileId] = [p].[Id]
LEFT JOIN [Networks] AS [n0] ON [i4].[NetworkId] = [n0].[Id]
LEFT JOIN [YoutubeChannels] AS [y4] ON [y4].[ProfileId] = [p].[Id]
LEFT JOIN [Networks] AS [n1] ON [y4].[NetworkId] = [n1].[Id]
LEFT JOIN [SnapchatChannels] AS [s] ON [s].[ProfileId] = [p].[Id]
LEFT JOIN [Networks] AS [n2] ON [s].[NetworkId] = [n2].[Id]
LEFT JOIN [MusicallyChannels] AS [m] ON [m].[ProfileId] = [p].[Id]
WHERE [p].[Deleted] = 0
ORDER BY [p].[FullName], [p].[Id], [b].[Id], [i].[Id], [y].[Id], [b2].[Id], [i2].[Id], [y2].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY

然后是更多看起来不太正确的查询

SELECT [y3].[Gender], [y3].[ChannelId], [y3].[Distribution]
FROM [YoutubeGenderDistribution] AS [y3]
INNER JOIN (
    SELECT DISTINCT [t7].*
    FROM (
        SELECT [p].[FullName], [p].[Id], [b].[Id] AS [Id0], [i].[Id] AS [Id1], [y].[Id] AS [Id2], [b2].[Id] AS [Id3], [i2].[Id] AS [Id4], [y2].[Id] AS [Id5]
        FROM [Profiles] AS [p]
        LEFT JOIN [BlogChannels] AS [b] ON [b].[ProfileId] = [p].[Id]
        LEFT JOIN [InstagramChannels] AS [i] ON [i].[ProfileId] = [p].[Id]
        LEFT JOIN [YoutubeChannels] AS [y] ON [y].[ProfileId] = [p].[Id]
        LEFT JOIN [BlogChannels] AS [b2] ON [b2].[ProfileId] = [p].[Id]
        LEFT JOIN [InstagramChannels] AS [i2] ON [i2].[ProfileId] = [p].[Id]
        LEFT JOIN [YoutubeChannels] AS [y2] ON [y2].[ProfileId] = [p].[Id]
        WHERE [p].[Deleted] = 0
        ORDER BY [p].[FullName], [p].[Id], [b].[Id], [i].[Id], [y].[Id], [b2].[Id], [i2].[Id], [y2].[Id]
        OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
    ) AS [t7]
) AS [y20] ON [y3].[ChannelId] = [y20].[Id5]
ORDER BY [y20].[FullName], [y20].[Id], [y20].[Id0], [y20].[Id1], [y20].[Id2], [y20].[Id3], [y20].[Id4], [y20].[Id5]

另一个“看起来”更正确的

SELECT [b0].[AgeRangeId], [b0].[ChannelId], [b0].[Distribution], [a].[Id], [a].[Range]
FROM [BlogAgeDistribution] AS [b0]
INNER JOIN (
    SELECT DISTINCT [t2].*
    FROM (
        SELECT [p].[FullName], [p].[Id], [b].[Id] AS [Id0]
        FROM [Profiles] AS [p]
        LEFT JOIN [BlogChannels] AS [b] ON [b].[ProfileId] = [p].[Id]
        WHERE [p].[Deleted] = 0
        ORDER BY [p].[FullName], [p].[Id], [b].[Id]
        OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
    ) AS [t2]
) AS [b1] ON [b0].[ChannelId] = [b1].[Id0]
LEFT JOIN [AgeRanges] AS [a] ON [b0].[AgeRangeId] = [a].[Id]
ORDER BY [b1].[FullName], [b1].[Id], [b1].[Id0]

想知道为什么 EF 在请求统计数据时加入所有其他平台吗？ Instagram。

谢谢!

编辑:

有趣的是，Age 的第一个查询生成了所有三个的连接

SELECT [y0].[AgeRangeId], [y0].[ChannelId], [y0].[Distribution], [a1].[Id], [a1].[Range]
FROM [YoutubeAgeDistribution] AS [y0]
INNER JOIN (
    SELECT DISTINCT [t4].*
    FROM (
        SELECT [p].[FullName], [p].[Id], [b].[Id] AS [Id0], [i].[Id] AS [Id1], [y].[Id] AS [Id2]
        FROM [Profiles] AS [p]
        LEFT JOIN [BlogChannels] AS [b] ON [b].[ProfileId] = [p].[Id]
        LEFT JOIN [InstagramChannels] AS [i] ON [i].[ProfileId] = [p].[Id]
        LEFT JOIN [YoutubeChannels] AS [y] ON [y].[ProfileId] = [p].[Id]
        WHERE [p].[Deleted] = 0
        ORDER BY [p].[FullName], [p].[Id], [b].[Id], [i].[Id], [y].[Id]
        OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY
    ) AS [t4]
) AS [y1] ON [y0].[ChannelId] = [y1].[Id2]
LEFT JOIN [AgeRanges] AS [a1] ON [y0].[AgeRangeId] = [a1].[Id]
ORDER BY [y1].[FullName], [y1].[Id], [y1].[Id0], [y1].[Id1], [y1].[Id2]

Answer 1

尽管您希望它只查询特定平台，但 EF Core 查询所有平台的原因是查询的编码方式。您已将所有这些组合在同一个 IQueryable 中。在执行 IQueryable 之前，利用 C# 中的多个步骤构建 IQueryable。

var query = _context
.Profiles.Where(p => searching.Contains(p.Name) && !p.Deleted)

if(searching.Contains("Blog"))
{
  query.Include(p => p.Blog)
   .ThenInclude(b => b.Network)
  .Include(p => p.Blog)
   .ThenInclude(i => i.AgeDistributions)
    .ThenInclude(i => i.AgeRange)
  .Include(p => p.Blog)
   .ThenInclude(b => b.GenderDistributions)
}

if(searching.Contains("Instagram"))
{
 .Include(p => p.Instagram)
  .ThenInclude(i => i.Network)
 .Include(p => p.Instagram)
  .ThenInclude(i => i.AgeDistributions)
   .ThenInclude(i => i.AgeRange)
 .Include(p => p.Instagram)
  .ThenInclude(b => b.GenderDistributions)
}

...

var results = query.ToList();

最后要记住的是尽早过滤。这就是为什么我在最开始放了一个“searching.Contains(p.Name)”。您的查询需要执行的内存占用空间越小。它应该执行得越快。

我要补充的最后一点是，EF Core 仍然相当新，并非所有内容都将完全在数据库中执行。在某些情况下，它会构建一组独立执行的查询，然后在调用客户端上下文中将它们组合成最终结果集。