Asp.Net MVC 5 Identity how to not list users which have Admin role

Question

There are three tables which are related to roles.

1. AspNetUsers - keeps the UserId data
2. AspNetRoles - keeps the RoleName and RoleId
3. AspNetUserRoles - makes a relation through AspNetUsers and AspNetRoles. It has only two cloumns which are UserId and RoleId. But for some reason I couldn't access to this table directly (like db.UserRoles).

So what I'am trying to do is avoid listing the users which are in the Admin role.

My code is like this:

return View(db.Users.OrderBy(e => e.UserName)
                    .ToPagedList(pageNumber, pageSize));

I think I should make something like this but I always get errors when I do it this way

return View(db.Users.Where(e => e.Roles.Where(r => r.RoleId != "1"))
                    .OrderBy(e => e.UserName)
                    .ToPagedList(pageNumber, pageSize));
                     // suppose that the RoleId of Admin is 1

Answer 1

return View(db.Users.Where(e => e.Roles.All(r => r.RoleId != "1"))
                  .OrderBy(e => e.UserName).ToPagedList(pageNumber, pageSize)); 
                    // Suppowse that the RoleId of Admin is 1

Use the .All extension

Or db.Users.Where(u=>!u.Roles.Any(r=>r.RoleId == "1"))

Answer 2

Try this:

const int adminRoleId = 1;
var query = db.Users.Where(user => !user.Roles
                                        .Any(role => role.Id 
                                                        == adminRoleId));
return View(query.OrderBy(e => e.UserName)
                 .ToPagedList(pageNumber, pageSize));

I think that in your case the problem is caused by r.RoleId != "1", I think that RoleId is of int type. What;s more, the expression Where(e => e.Roles.Where(r => r.RoleId != "1")) is invalid. Expression tree passed into first Where should return bool for specified r (it is user in your context) but you are returniing collection of roles different than admin (instead of mentioned bool).