How to add a role to use when EF doesn't bring in UserRoles Table?

Question

I have a UserRoles table with just two columns userId and RoleId and entityframework doesn't bring that class in. How can save the association without the userroles class?

my users and roles classes in EntityFramework are like this.

public partial class aspnet_Users
{
    public aspnet_Users()
    {
        this.aspnet_Roles = new HashSet<aspnet_Roles>();
    }

    public System.Guid ApplicationId { get; set; }
    public System.Guid UserId { get; set; }
    public string UserName { get; set; }
    public string LoweredUserName { get; set; }
    public string MobileAlias { get; set; }
    public bool IsAnonymous { get; set; }
    public System.DateTime LastActivityDate { get; set; }

    public virtual aspnet_Applications aspnet_Applications { get; set; }
    public virtual aspnet_Membership aspnet_Membership { get; set; }
    public virtual ICollection<aspnet_Roles> aspnet_Roles { get; set; }
}

    public partial class aspnet_Roles
    {
        public aspnet_Roles()
        {
            this.aspnet_Users = new HashSet<aspnet_Users>();
        }

        public System.Guid ApplicationId { get; set; }
        public System.Guid RoleId { get; set; }
        public string RoleName { get; set; }
        public string LoweredRoleName { get; set; }
        public string Description { get; set; }

        public virtual aspnet_Applications aspnet_Applications { get; set; }
        public virtual ICollection<aspnet_Users> aspnet_Users { get; set; }
    }

EF doesn't give me UserRoles class in model but it shows up in XML.

when i try to add a new user, i am accessing like this

                    var role = new Data.aspnet_Roles();
                    role.RoleId = userItem.roles.First().RoleId;
                    role.RoleName = userItem.roles.First().RoleName;
                    user.aspnet_Roles.Add(role);

This gives a error

InnerException {"Cannot insert duplicate key row in object 'dbo.aspnet_Roles' with unique index 'aspnet_Roles_index1'.\r\nThe statement has been terminated."} System.Exception {System.Data.SqlClient.SqlException}

the answer here suggest that it should work. i cant figure out what i am doing wrong. Entity Framework Add User to Role

It also makes sense that i get this error. But I am clueless on how else i can store the userroles relationship without having userroles EF class. I am really stuck. Any help is appreciated. Thank you.

Answer 1

The way you are doing this job, you are creating a new role with the same id and name that exists in database now, because you are using an instance of role that doesn't exists in context and this way, context thinks it is a new role and should be added.

You should inform the context that this is not a new role, to do so:

• you can load the role you need, using a query, into the same context that you will use to save user

• or you can attach (entry) the role object to the same context that you will use to save user, and set its state to unchanged.

For example you can first find the role yo want:

 var db= new YourContext();
 var role = db.aspnet_Roles.Where(....).FirstOrDefault();

then using that instnace of role and that instance of context:

 user.aspnet_Roles.Add(role);
 db.SaveChanges();