SQL Linq Many To Many

Question

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?

Paddy · Accepted Answer · 2009-11-30 10:38:38Z

1

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

answered Nov 30, 2009 at 10:38

Paddy

34k15 gold badges82 silver badges121 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

AnthonyWJones Over a year ago

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?

Paddy Over a year ago

The result will be (I think) IQueryable<Role>, which is an IEnumerable type.

AnthonyWJones Over a year ago

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?

ckknight · Accepted Answer · 2009-11-30 15:07:33Z

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

Collectives™ on Stack Overflow

SQL Linq Many To Many

2 Answers 2

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Related