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

entity-framework entity-framework-core linq many-to-many

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?

1
1
1/1/2019 2:23:40 PM

Popular Answer

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))
1
1/1/2019 2:22:57 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