Querying many-To-many relationship with a list of parameters

Question

I use EF core and i have the classes "User" and "Authority" which are connected with a many-to-many relationship ("Users" has a property "authorities" and "Authority" has a property "users"). The relationship is managed via middle class "UserAuthority".

I need to query all "Users" that have "Authorities" with certain names.

I tried this:

List<string> authorities = A list of authorities;
(from user in this.dbContext.user.Include("authorities.authority")
 where authorities.Any(x => user.authorities.Any(y => y.authority.authority == x))
 select new UserDto(user.id, user.firstname + " " + user.lastname)).ToList()

But the console says that LINQ cant translate

authorities.Any(x => user.authorities.Any(y => y.authority.authority == x))

and that it will be handled in memory.

What is the correct approach here?

Answer 1

You wrote:

i have the classes "User" and "Authority" which are connected with a many-to-many relationship ("Users" has a property "authorities" and "Authority" has a property "users").

Something like this:

class User
{
    public int Id {get; set;}
    ...

    // every User has zero or more Authorities (many-to-many)
    public virtual ICollection<Authority> Authorities {get; set;}
}
class Authority
{
    public int Id {get; set;}
    public string Name {get; set;}
    ...

    // every Authority has zero or more Users (many-to-many)
    public virtual ICollection<User> Users {get; set;}
}

I need to query all "Users" that have "Authorities" with certain names.

If I read this literally, you want all Users, that have at least one Authority that has a Name that is in the collection of certainNames. You want each User with ALL his Authorities, even those Authorities with names that are not in certainNames

It could also mean that you want all Users, each with only those of their Authorities that have a Name which is in certainNames, but only those Users that have at least one such Authority.

How about this:

IEnumerable<string> certainNames = ...
var UsersWithAuthoritiesThatAreInCertainNames = myDbContext.Users
    .Where (user => user.Authorities.Select(authority => authority.Name)
         .Intersect(certainNames)
         .Any())
    .Select(user => new
    {
         // select only the User properties you actually plan to use:
         Id = user.Id,
         Name = user.Name,
         ...

         Authorities = user.Authorities.Select(authority => new
         {
             // again select only the Authority properties you plan to use:
             Id = authority.Id,
             Name = authority.Name,
             ...
         })
         .ToList(),
    })

In words:

From the collection of all users, keep only those users, that have at least one authority with a name that is also in certainNames. From the remaining users, select several properties.

If you don't want ALL Authorities of the user, but only the ones that are in certain names:

var UsersWithOnlyTheirAuthoritiesThatAreInCertainNames = myDbContext.Users
    .Select(user => new
    {
         // select only the User properties you actually plan to use:
         Id = user.Id,
         Name = user.Name,
         ...

         Authorities = user.Authorities
         .Where(authority => certainNames.Contains(authority.Name))
         .Select(authority => new
         {
             // again select only the Authority properties you plan to use:
             Id = authority.Id,
             Name = authority.Name,
             ...
         })
         .ToList(),
    })
    // keep only the Users that have at least one such authority
    .Where(selectedUser => selectedUser.Authorities.Any());

In words:

from the collection of Users, select some properties of every user, inclusive some properties of only those Authorities of the user that have a name that is also in certainNames. From the remaining sequence of selected users keep only those users that have at least one authority left

Answer 2

Currently the only translatable in-memory collection method is Contains (for primitive type in-memory collection, translates to SQL IN(...)).

So instead of

authorities.Any(x => user.authorities.Any(y => y.authority.authority == x))

use

user.authorities.Any(ua => authorities.Contains(ua.authority.authority))