CODEWITHSUNDEEP
c#linq
LiamB
LiamB

Reputation: 18606

SQL Linq Many To Many

I have a database with a User's and Role's table. Each user can be in lots of roles, so there is a 'middle' table called UserRoles that simply contains a RoleId and UserId.

Now I need to get all the roles attached to a User. 

 public IEnumerable<Role> GetAllRoles(int UserId)
    {
        var query = from R in _db.Roles
                    where RolesTbl.UserRoles.UserId == UserId
                    select R;
        return query;
    }

Now the query above doesnt work, "RolesTbl.UserRoles.UserId" I cant referance it in this way because of the Many to Many.

Anyone have a suggestion on how to resolve?

Upvotes: 1

Views: 218

Answers (2)

ckknight
ckknight

Reputation: 6173

You need to properly use a join, otherwise you'd be left with a sequence of a sequence of roles, which although you could get around by using a SelectMany, a Join is more appropriate.

public IEnumerable<Role> GetAllRoles(int userID)
{
    return _db.Roles.Join(_db.UserRoles.Where(ur => ur.UserID == userID), r => r.ID, ur => ur.RoleID, (r, ur) => r);
}

Or, if you prefer the non-fluent syntax:

public IEnumerable<Role> GetAllRoles(int userID)
{
    return from r in _db.Roles
           join ur in _db.UserRoles on r.ID == ur.RoleID
           where ur.UserID == userID
           select r;
}

Upvotes: 0

Paddy
Paddy

Reputation: 33867

Does this do it? 

public IEnumerable<Role> GetAllRoles(int UserId)
    {
        var query = from ur IN UserRoles
                    where ur.UserId == UserId
                    select ur.Roles;
        return query;
    }

Upvotes: 1

Related Questions