AspNetUserRoles Table is not populated using Entity Framework Database First in MVC 5

Question

In my project I am trying to retrieve all the users and their roles in MVC 5 using Entity Framework.

The DBContext is set up using Database first. But it is wearied that the AspNetUserRoles Table is not populated automatically and the relationship between AspNetUser and AspNetRole is many-to-many.

Below is the link to the screenshot.

Can anyone give me some hints on this? Many thanks.

Answer 1

If you want to manage the AspNetUsersRoles, you must make the following modification.

1. Remove the primary key, actually is conformed by UserId and RoleId

2. Add a new Id field in the table and check like primary key.

3. Update your model.

Now you can scaffolding this entity and create the controller and the view for manage this.

Note: Remember that by changing the primary key of this table on the DB side means you have to control how AspNetUserRoles records are created on the Application Side, considering you now could have record redundancy on the table, for instance: MyRoles=1 (PK), UserID=1, RoleID=1, Comment= User1 is Admin MyRoles=2 (PK), UserID=1, RoleID=2, Comment= User1 is Admin again

Which means the same, so manage this logic upon AspNetUserRoles creation!

Answer 2

I assume that there is no problem that you cannot see AspNetUserRoles table under EDMX at all.

You have access to this table via code and in most cases is enough.

Normally under ASP.NET MVC 5 you have something like

   public class AccountController : Controller
    {
        private ApplicationUserManager _userManager;
 ...
 public AccountController(ApplicationUserManager userManager, ApplicationSignInManager signInManager)
        {

            UserManager = userManager;
...
 public ApplicationUserManager UserManager
        {
            get
            {
                return _userManager ?? HttpContext.GetOwinContext().GetUserManager<ApplicationUserManager>();
            }
            private set
            {
                _userManager = value;
            }
        }

So under Login method you can get user roles like this

  [System.Web.Mvc.HttpPost]
        [System.Web.Mvc.AllowAnonymous]
        [ValidateAntiForgeryToken]
        public async Task<ActionResult> Login(LoginViewModel model, string returnUrl)
        {
...
 var result = await SignInManager.PasswordSignInAsync(model.Email, model.Password, false, shouldLockout: false);
            switch (result)
            {
                case SignInStatus.Success:
                    {     
                        ApplicationUser user = UserManager.FindByName(model.Email);

                        var roles = (AppUserRoles[])Enum.GetValues(typeof(AppUserRoles));
                        foreach (var role in roles)
                        {
                            if (UserManager.IsInRole(user.Id, role.ToString()) && role == AppUserRoles.Administrators)
                            {
                                 // Do what you need here                                

                                // logger.Info("Login attempt is success.");

                                return RedirectToAction("Index", "Admin");
                            }

You need also to have this enum in your code

public enum AppUserRoles
    {
        Administrators = 1,
        Managers = 2,
        Users = 3,
        Others = 4
    }

Another approach is to use STORED PROCEDURE that you can use in EDMX.

CREATE PROCEDURE GetUserRoleByUserId
    @UserId nvarchar(128) 
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    SELECT [UserId],[RoleId] FROM [AspNetUserRoles] WHERE [UserId] LIKE @UserId
END
GO

Hope this will help you.