tags:

views:

76

answers:

2

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?

+1  A: 

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;
    }
Paddy
Fantasic, really simple.
Pino
I'm a little confused as to how this works. Wouldn't the type of ur.Roles be some collection of Roles? So shouldn't the type of `query` become some `IEnumerable<SomeCollectionOfRoles>`? OR does LINQ-TO-SQL do some magic here that is different from Linq-To-Objects?
AnthonyWJones
The result will be (I think) IQueryable<Role>, which is an IEnumerable type.
Paddy
So are you saying in this case LINQ-To-SQL spots that .Roles is a collection of roles an decides somehow that the output needs to be flattened to simple enumeration of Roles, doing the equivalen of a SelectMany in Linq-To-Object extensions? What governs this decision?
AnthonyWJones
Couldn't honestly tell you, sorry...
Paddy
A: 

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;
}
ckknight