Fluent API Many to Many Mapping Error

Question

I have the following mapping to support a many to many table (User_Role)

     modelBuilder.Entity<Role>()
        .HasMany<User>(u => u.users)
        .WithMany(r => r.roles)
        .Map(m => 
           m.MapLeftKey("role_id")
           m.MapRightKey("user_id")
           m.ToTable("User_Role"));

This works great from mapping many roles to a user and many users to a role. The problem I am having is when I need to add a new row to the table User_Role via an entity as follows:

public class User_Role
{
   [Key Column(Order=1)]   
   public int role_id {get;set;)
   [Key Column(Order=1)]
   public int user_id {get;set;)
}

Whenever I try to access this entity as follows:

 dbContext.User_Role.Add(new User_Role {user_id ....

EF looks for a non existent table called User_Role1 ... its adding a '1' to the table name.

I then tried to add:

  [Table("User_Role")] 

This takes care of the adding a '1' but I now get this error:

"The EntitySet 'RoleUser' with schema 'dbo' and table 'User_Role' was already defined. Each EntitySet must refer to a unique schema and table"

I was able to confirm that the following lines together are causing the problem but I kind of need them both

 m.ToTable("User_Role") and public class User_Role..

Any suggestions would be great...I am stumped.

Answer 1

You cannot represent the join table in a many-to-many relationship by an entity class in your model. This join table is managed by EF and you cannot directly access this table. If you want to create a relationship between an existing user and an existing role you must do this using these two entities:

var user = dbContext.Users.Single(u => u.id == user_id);
var role = dbContext.Roles.Single(r => r.id == role_id);

// if you don't have lazy loading and don't instantiate the collection
// in the constructor, add this: user.roles = new List<Role>(); 

user.roles.Add(role);

dbContext.SaveChanges();

This will write an entry for the new relationship (the (user_id,role_id) pair) into the join table.

Edit

If you only have the two key properties at hand and don't want to load the user and role from the database you can work with attached "stub entities":

var user = new User { id = user_id, roles = new List<Role>() };
var role = new Role { id = role_id };

dbContext.Users.Attach(user);
dbContext.Roles.Attach(role);

user.roles.Add(role);

dbContext.SaveChanges();