Linq to SQL, Return only users in a particular role

Question

Hi, I'm using linq to SQL along with asp.net membership provider. I have three roles set up but want to get only the users of a particular role. This is my query to return all users

  public IQueryable<aspnet_User> GetAllMembers()
  {
     return db.aspnet_Users.OrderByDescending(u => u.UserName);
  }

Can anyone point me in the right direction?

Thanks

Answer 1

hi there, why not use the built in functions that come along with the membership provider?

Have a look here...

Answer 2

It looks like you need the same thing that was discussed here. Tvanfosson's untested answer was the most upvoted:

var users = db.aspnet_Users
              .Where(u => u.Active)
              .Any(u => u.aspnet_UsersInRoles
                         .Any(r => r.aspnet_Roles.RoleName == "Auth Level 1" ));

According to the person who asked the question (John) this didn't quite work for him so he ended up doing this instead:

var AuthUsers = from aspnet_User in db.aspnet_Users
                join userinroles in db.aspnet_UsersInRoles on aspnet_User.UserId equals userinroles.UserId
                where aspnet_User.Active 
                && userinroles.aspnet_Role.RoleName == "Auth Level 1"
                select aspnet_User;

Hopefully one of those two will work (please let us know which works for you).

Answer 3

Another option is to do this operation in two steps:

1. first find the list of users in your role by querying the aspnet_UsersInRole table
2. then filter out the users from your aspnet_Users table that are in that list

Something like:

string roleName = "someRole";

// get the user id's in that role
var usersInRole = db.aspnet_UsersInRoles
                        .Where(ur => ur.RoleName == roleName)
                        .Select(ur => ur.UserId).ToList();

// then find the users that are in that list of users
var users = db.aspnet_Users.Where(u => usersInRole.Contains(u.UserId));

Of course, here in your concrete example, one of the direct approaches with a join will work - but sometimes that's just almost too tricky to express, and introducing a second step might make things easier in the end. You might even want to further limit down this list of users based on some other criteria, that might not be easily expressible with a join.

One way or another - you'll definitely be able to figure out those users! :-)