LINQ Many-To-Many Where

Question

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.

`friends.Include(o => o.Languages).ThenInclude(o => o.Language).Where(f => f.Languages.SelectMany(l => l.Language).Any(l => listOfLanguages.Contains(l.Guid)))` — haim770, Nov 07 '16 at 20:52
@haim770 - That query will have severe performance issues, if it even executes. — Travis J, Nov 07 '16 at 20:58
@TravisJ, In EF 6 it generates a very reasonable SQL and it *does* execute. — haim770, Nov 07 '16 at 21:00
Probably you can just select from `FriendLanguage` directly instead of usage `Friend` and `Language` (see [the answer](http://stackoverflow.com/a/23398605/315935)) ? One should get clear INNER JOIN instead of sub-queries. I didn't verified that, but it seems so. — Oleg, Nov 07 '16 at 23:46

Ivan Stoev · Accepted Answer · 2016-11-07T21:22:23.740

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]

And if I wanted to check any language rather than all languages, then presumably I replace == languages.Count with > 0 ? — Matthew Layton, Nov 07 '16 at 21:18
Answer updated. `Any` + `Contains` is pretty standard way of handling conditions on `many` side, the same was in EF6. — Ivan Stoev, Nov 07 '16 at 21:28

LINQ Many-To-Many Where

1 Answers1