LINQ Many-To-Many Where

c# entity-framework-core linq

Accepted Answer

If I understand it right, you want to find people who are fluent in all of the listed languages.

The LINQ query that best expresses your need is:

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

Sadly, it is not SQL friendly. In reality, EF Core will load the data into memory and do the filtering there since it is presently unable to convert it to SQL.

You may thus substitute this:

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

This reads something along the lines of:

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 really want buddies who can communicate in any of the listed languages, then theWhere is easier:

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

and this is the 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]
3
11/7/2016 9:22:23 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow