CODEWITHSUNDEEP
c#entity-frameworklinq
barteloma
barteloma

Reputation: 6845

Many to many linq query Entity Framework

I have three tables Users, UserRoles, UserInRole.

Users     UserInRole    UserRole
------    ----------    --------
Id        UserId        RoleId
Name      RoleId        Name
Email

I want to select user emails who is in "admin" or "editor" role.

var emails = userUnitOfWork.Repository.Select()
     .Where(u => u.UserRoles
     .All(r=>r.Name=="admin" || r.Name=="editor")).Select(t=>t.Email);

but that only returns one email that has no role.

Upvotes: 0

Views: 45

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205539

Either use and of two Any conditions like this

var emails = userUnitOfWork.Repository.Select()
    .Where(u => u.UserRoles.Any(r => r.Name == "admin")
         && u.UserRoles.Any(r => r.Name == "editor"))
    .Select(t=>t.Email);

or a single Count condition like this

var emails = userUnitOfWork.Repository.Select()
    .Where(u => u.UserRoles.Count(r => r.Name == "admin" || r.Name == "editor") == 2)
    .Select(t=>t.Email);

UPDATE: Your update (changing and to or) makes it trivial - just change All to Any in your original query.

Upvotes: 1

Gilgamesh
Gilgamesh

Reputation: 668

The issue is that .All() requires all items to match, this means that if the user has any roles other than "admin" or "editor" the result will be false.

a solution to this is to use .Any()

var emails = userUnitOfWork.Repository.Select()
.Where(
    u => u.UserRoles.Any(r=>r.Name=="admin")
    && u.UserRoles.Any(r =>r.Name =="editor")
)
.Select(t=>t.Email

Upvotes: 0

Related Questions