I am using Asp.Net .Net5 with Entity framework 5. I'm trying to work out the number of basic users. The table aspnetuser is connected to aspnetRoles through a many to many relationship by aspnetuserRoles link table.

I have 3 tables

1. aspnetuser

2. aspnetroles

3. aspnetuserRoles = link table

   public int GetNumberOfActiveBasicUsers()
   {
       var users = _context.Users
           .Where(u => u.IsEnabled == true)
           .Where(u => u.UserName != "AdminUser")
           .Include(r => r.UserRoles)
           .ThenInclude(r => r.Role)
   
           //not sure what to put here
   
   
           .Count();  
   
        return users;
    }
   

aspnetuser table

id | username
--------------
1  | Jim
2  | Harry
3  | James
3  | Susan

aspnetRoles

id | name
----------
1  | admin
2  | standard
3  | Basic

aspnetuserRoles // link table

userId | roleId
----------------
   1   |   1
   2   |   2
   3   |   3
   4   |   3

The code should return back with a value of 2 as there are 2 basic users.

CodePudding user response：

try this

return _context.UserRoles
        .Where(ur => ur.User.IsEnabled && ur.Role.Name== "Basic")
        .Count();