Updating many to many relationships in Entity Framework

Question

I'm able to create a user successfully. I'm now trying to assign some roles to the user during the creation process.

I'm using a database-first approach with Entity Framework. I've tried following some tutorials and looking at other peoples solutions, but can't seem to get it working.

I have 3 tables: User, Role, and UserRole (contains UserId and RoleId).

The relationship between User and Role is many-to-many.

Entity Diagram Showing Relationships

The Create view, which contains checkboxes

Database context class:

public partial class MyDBEntities : DbContext
{
    public MyDBEntities() : base("name=MyDBEntities")
    {
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        throw new UnintentionalCodeFirstException();
    }

    public virtual DbSet<User> Users { get; set; }
    public virtual DbSet<Role> Roles { get; set; }
}

User.cs:

public partial class User
{
    public User()
    {
        this.Roles = new HashSet<Role>();
    }

    public int Id { get; set; }
    public string Username { get; set; }
    public string Email { get; set; }
    public string Password_hash { get; set; }

    public virtual ICollection<Role> Roles { get; set; }
}

Role.cs:

public partial class Role
{
    public Role()
    {
        this.Users = new HashSet<User>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<User> Users { get; set; }
}

[HttpGet] Create():

public ActionResult Create()
{
        return View(new UserCreateVM
        {
            Roles = db.Roles.Select(x => new RoleCheckboxVM
            {
                Id = x.Id,
                Name = x.Name,
                IsChecked = false
            }).ToList()
        });
    }

[HttpPost] Create():

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create(UserCreateVM vm)
{
        User user = new User();

        var selectedRoles = new List<Role>();

        foreach (var r in db.Roles)   // we query all the roles in the database
        {
            var checkbox = vm.Roles.Single(x => x.Id == r.Id);
            checkbox.Name = r.Name; //updates the name on all our checkboxes

            if (checkbox.IsChecked) //if checkbox is checked
            {
                selectedRoles.Add(r); //then we add the role to our selectedRoles
            }
        }

        IList<Role> rolesList = user.Roles.ToList();

        foreach (var toAdd in selectedRoles.Where(x => !rolesList.Contains(x))) //if roles is not already added
        {
            rolesList.Add(toAdd); //then we add them to the user roles
        }

        if (ModelState.IsValid)
        {
            //We update the data on the object
            user.Username = vm.Username;
            user.Email = vm.Email;

            user.SetPassword(vm.Password_hash); //we hash the password

            db.Users.Add(user);
            db.SaveChanges();

            return RedirectToAction("Index");
        }

        return View(vm);
    }

Answer 1

I finally figured out how to solve this.

1. The Many-to-Many table UserRole didn't have any primary key. Therefore I added a PK consistent of both the UserId and the RoleId.

2. I removed the rolesList as I didn't need it

   [HttpPost]
   [ValidateAntiForgeryToken]
   public ActionResult Create(UserCreateVM vm)
   
   {
   
      User user = new User();
   
      var selectedRoles = new List<Role>();
   
      foreach (var r in db.Roles)   // we query all the roles in the database
      {
          var checkbox = vm.Roles.Single(x => x.Id == r.Id);
          checkbox.Name = r.Name; //updates the name on all our checkboxes
   
          if (checkbox.IsChecked) //if checkbox is checked
          {
              selectedRoles.Add(r); //then we add the role to our selectedRoles
          }
      }
   
      foreach (var r in selectedRoles.Where(x => !user.Roles.Contains(x))) //if roles is not already added
      {
          user.Roles.Add(r); //then we add them to the user roles
      }
   
      if (ModelState.IsValid)
      {
          //We update the data on the object
          user.Username = vm.Username;
          user.Email = vm.Email;
   
          user.SetPassword(vm.Password_hash); //we hash the password
   
          db.Users.Add(user);
          db.SaveChanges();
   
          return RedirectToAction("Index");
      }
   
      return View(vm);
   }
   

Then after updating both the database table and the database model (edmx) everything worked fine.