LINQ多對多在哪裡

c# entity-framework-core linq

如何編寫EF7(核心)/ SQL友好的多對多LINQ查詢?

比方說,我有很多會說多種語言的朋友,我想找到一組特定語言的朋友。

class Friend
{
    Guid Id { get; set; }
    ICollection<FriendLanguage> Languages { get; set; }
}

class Language
{
    Guid { get; set; }
    ICollection<FriendLanguage> Friends { get; set; }
}

class FriendLanguage
{
    Friend { get; set; }
    Language { get; set; }
}

鑑於我有一組語言ID IEnumerable<Guid> ,我想找回所有說這些語言的朋友。

我試過這個......

friends
    .Include(o => o.Languages).ThenInclude(o => o.Language)
    .SelectMany(o => o.Languages).Select(o => o.Language.Id)
    .Intersect(languages);

......但這只會返回一套簡化的Guids ...不完全確定從哪裡開始,或者即使我走在正確的道路上。

一般承認的答案

如果我理解正確,你想要從列表中找到說出所有語言的朋友。

表達您的要求的最自然的LINQ查詢將是:

var friends = db.Friends
    .Include(o => o.Languages).ThenInclude(o => o.Language)
    .Where(o => languages.All(id => o.Languages.Any(fl => fl.Language.Id == id)));

不幸的是,它不是SQL友好的。事實上,EF Core目前無法將其轉換為SQL,並將讀取內存中的數據並在那裡進行過濾。

所以你可以用它代替:

var friends = db.Friends
    .Include(o => o.Languages).ThenInclude(o => o.Language)
    .Where(o => o.Languages.Count(fl => languages.Contains(fl.Language.Id)) == languages.Count);

這意味著:

SELECT [o].[Id]
FROM [Friends] AS [o]
WHERE (
    SELECT COUNT(*)
    FROM [FriendLanguages] AS [fl]
    WHERE [fl].[LanguageId] IN ('6e64302f-24db-4717-a5fe-2cc61985ca3a', '2c216a63-1f6a-4fad-9105-d5f8ece3fa3c') AND ([o].[Id] = [fl].[FriendId])
) = @__languages_Count_1
ORDER BY [o].[Id]

如果你確實想要從列表中說出任何語言的朋友,那麼Where更簡單:

.Where(o => o.Languages.Any(fl => languages.Contains(fl.Language.Id)))

而SQL是:

SELECT [o].[Id]
FROM [Friends] AS [o]
WHERE EXISTS (
    SELECT 1
    FROM [FriendLanguages] AS [fl]
    WHERE [fl].[LanguageId] IN ('ed3f85a7-e122-45dd-b0af-2020052d55a7', '4819cb7d-ad43-41a0-a3a1-979b7abc6265') AND ([o].[Id] = [fl].[FriendId]))
ORDER BY [o].[Id]


Related

許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因