CODEWITHSUNDEEP
c#linqentity-framework-core
Matthew Layton
Matthew Layton

Reputation: 42390

LINQ Many-To-Many Where

How do I write an EF7 (Core) / SQL Friendly Many-To-Many LINQ Query?

Say for example I have many Friends that speak Many Languages, and I want to find all friends for a given set of languages.

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; }
}

Given I have a set of language IDs IEnumerable<Guid>, I want to get back all the friends that speak those languages.

I tried this...

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

...but this only returns a reduced set of Guids...not entirely sure where to go from here, or even if I'm on the right path.

Upvotes: 3

Views: 1762

Answers (1)

Ivan Stoev
Ivan Stoev

Reputation: 205849

If I understand correctly, you want to get the friends that speak all the languages from the list.

The most natural LINQ query expressing your requirement would be:

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

Unfortunately it's not SQL friendly. In fact EF Core currently cannot translate it to SQL and will read the data in memory and do the filtering there.

So you can use this instead:

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

which translates to something like this:

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]

If you indeed want the friends that speak any of the languages from the list, then the Where is simpler:

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

and the SQL is:

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]

Upvotes: 3

Related Questions