EF核心多个左连接

Question

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

_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秒后的db执行超时)和检查SQL使我觉得要么是EF无法正确确定的建模问题，要么是EF只是生成次优sql。结果集是分页的，使用跳转和抓取，目前获取十条记录需要时间。

这是第一个执行的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查询所有平台的原因，尽管您希望它只查询特定的平台，但原因在于查询是如何编码的。您已经将它们合并在同一个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仍然是相当新的，并不是所有的东西都会在数据库中执行。在某些情况下，它构建一组查询以独立执行，然后将它们组合成调用客户端上下文中的最终结果集。