EF Core多個左連接

entity-framework-core

我在asp.net mvc核心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秒後db執行超時)並檢查sql讓我覺得我有一個建模問題,EF無法正確確定或EF只是生成次優SQL。結果集使用skip和take分頁,當前獲取10條記錄需要時間。

這是第一個執行的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]

熱門答案

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();

要記住的最後一件事是儘早過濾。這就是為什麼我在一開始就設置了“search.Contains(p.Name)”。查詢需要執行的內存佔用量越小。它應該執行得越快。

我可以補充的最後一點是,EF Core仍然相當新,並不是所有內容都將在數據庫中完全執行。在某些情況下,它構建一組查詢以獨立執行,然後將它們組合到調用客戶端上下文中的最終結果集中。



Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow