AspNetUserRoles not in EDMX when generating from database

Question

I have did some searching around on this issue and have come across a few questions in regards to AspNetUserRoles not being in the EDMX designer when generating from the database. However its in the ModelBrowser and I can't get this table to show up so I can use Roles Authorization.

When I hit this method in my Roles class

public override string[] GetRolesForUser(string username)
    {
        DTE = new DatabaseTestingEntities();
        string userID = DTE.AspNetUsers.Where(w => w.Email == username).Select(s => s.Id).FirstOrDefault();
        string roleID = DTE.AspNetUsers.Include("AspNetRoles").Where(s => s.Id == userID).FirstOrDefault().ToString();//.AspNetUserRoles.Where(w => w.UserId == userID).Select(s => s.RoleId).FirstOrDefault();
        string roleName = DTE.AspNetRoles.Where(w => w.Id == roleID).Select(s => s.Name).FirstOrDefault();
        string[] results = { roleName };
        return results;
    }

The results always come back as null..

However it should look like this instead

public override string[] GetRolesForUser(string username)
    {
        DTE = new DatabaseTestingEntities();
        string userID = DTE.AspNetUsers.Where(w => w.Email == username).Select(s => s.Id).FirstOrDefault();
        string roleID = DTE.AspNetUserRoles.Where(w => w.UserId == userID).Select(s => s.RoleId).FirstOrDefault();
        string roleName = DTE.AspNetRoles.Where(w => w.Id == roleID).Select(s => s.Name).FirstOrDefault();
        string[] results = { roleName };
        return results;
    }

But that way throws an error because the AspNetUserRoles isn't in the EDMX designer when I generate the EF from the database.

How can I get this table to appear so I can continue on with what I need to do?

I have tried updating the EDMX and that doesn't work either.

Answer 1

I just had this question myself more or less... "Where is the AspNetUserRoles table in the model?"

My understanding is that the AspNetUserRoles table is created and consists of two foreign keys, one to the AspNetUsers table for it's Id value, and one to the AspNetRoles table, also for its Id value. When you assign a role to a user, it adds a row into the AspNetUserRoles table so as to give you what is called a "Navigation Property" on the AspNetUsers table. Look at your edmx and find the AspNetUsers table, at the bottom you'll see a Navigation Property of "AspNetRoles" and this collection is available to you in code on an AspNetUser object.

As a user can belong to many roles, this Navigation Property is a collection that can be assigned to a List something like this:

AspNetUser selectedUser = dbContext.AspNetUsers.FirstOrDefault(u => u.UserName == "foo");

if (selectedUser == null) return;

List<AspNetRole> selectedUsersRoles = selectedUser.AspNetRoles.ToList();

For the original poster's I would return the List and work with that...

public override List<AspNetRoles> GetRolesForUser(string username)
{
    DTE = new DatabaseTestEntities();
    AspNetUser selectedUser = DTE.AspNetUsers.FirstOrDefault(u => u.UserName == username);
    if (selectedUser == null) return null; //User not found - return null

    return List<AspNetRole> selectedUsersRoles = selectedUser.AspNetRoles.ToList();
}

This basically means you don't "need" the AspNetUserRoles table explicitly. You should be able to work with a user's roles as noted above. I'm not sure if it's recommended or not, but I would not directly insert into the AspNetUserRoles table either. You should just add a role to the user object and let the UserRoles table update automatically.