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