EF Core query where Were clause is a collection?

Question

I am trying to build a sane query in EF Core that returns a collection of things that are, in turn derived from a collection of things. Basically in raw SQL one would do a JOIN.

Its in ASP.NET Core so the initial collection is the list of Roles on the SecurityPrincipal object:

var roles = User.FindAll(ClaimTypes.Role).Select(r=>r.Value);

These roles are then mapped to Groups in our Database, so I can look those up:

var groupsQuery = dbContext.Groups.Where(g=>roles.Any(r=>r==g.GroupName));
var groups = await groupsQuery.ToListAsync();

This query is quite happy and returns a collection of groups as expected. The groups however have access to another resource, which is what I really want and because its a many to many relationship there is a bridging table.

This is me trying to query the AssetGroup joining table so I can get all the Assets referenced by all the Groups that map to a Role on the SecurityPrincipal.

var assetGroupsQuery = dbContext.AssetsGroups.Where(ag => groupsQuery.Any(ag => ag.Id == a.GroupId));
var assetGroups = await assetGroupsQuery.ToListAsync();

When I perform the second query I get a lot of spam in my output window:

  The LINQ expression 'where ([ag].Id == [ag].GroupId)' could not be translated and will be evaluated locally.
  The LINQ expression 'Any()' could not be translated and will be evaluated locally.
  The LINQ expression 'where {from Group g in __groups_0 where ([ag].Id == [ag].GroupId) select [ag] => Any()}' could not be translated and will be evaluated locally.
  The LINQ expression 'where ([ag].Id == [ag].GroupId)' could not be translated and will be evaluated locally.
  The LINQ expression 'Any()' could not be translated and will be evaluated locally.

Any clues on how one should phrase a nested query like this so EF Core can compose a single SQL query properly?

Answer 1

In general avoid using Any or any LINQ operator other than Contains on in memory collection like your roles (which according to the code should be of type IEnumerable<string>).

In other words, instead of

.Where(g => roles.Any(r => r == g.GroupName))

use the functionally equivalent

.Where(g => roles.Contains(g.GroupName))

The later is guaranteed to be translated to SQL IN, while the former isn't.

Interestingly and at the same time misleading is that EF Core tries to be smart and translate the former the same way as Contains, and succeeds when the containing query is executed, but not when used as part of another query.

It could be considered a current EF Core implementation defect. But the workaround/solution is (as mentioned in the beginning) to not rely on it and always use Contains.