CODEWITHSUNDEEP
c#entity-framework
mawburn
mawburn

Reputation: 2336

Many-to-Many relationship query using entities

I'm trying to do this query, using the Entity Framework:

SELECT uir.roleid
FROM UsersInRoles uir
WHERE uir.userid = @userid

UsersInRoles contains: roleid and userid only.

The problem is, UsersInRoles is a many-to-many relationship table. It connects my Roles and Users tables based on their PKs. The Entity Framework treats this very strangely by removing the UsersInRoles entity, and I'm having trouble understanding how to form the query.

This is what I have, but it's returning 27 rows when it should be returning only 2. 

 var user_roles = admin_db.Roles
                    .Where(u => u.User.userid == db_userid)
                    .Select(r => r.roleid).ToList();

To tell the truth, I honestly can't even figure out how it's returning 27. That number of results makes no sense.

Upvotes: 0

Views: 94

Answers (1)

Slauma
Slauma

Reputation: 177133

You can use Any to find all roles that have any (at least one) user with the given user id:

var user_roles = admin_db.Roles
    .Where(r => r.Users.Any(u => u.userid == db_userid))
    .Select(r => r.roleid)
    .ToList();

Or - if you have a Roles collection in User:

var user_roles = admin_db.Users
    .Where(u => u.userid == db_userid)
    .SelectMany(u => u.Roles.Select(r => r.roleid))
    .ToList();

Upvotes: 2

Related Questions