c# - Entity Framework Database First many-to-many

Question

I've created an Entity Framework model from the database. I have many-to-many relationship: User - UserRole - Role.

EF created UserRole entity and UserRoles navigation property in the User entity and in Role entity, but I'd rather have Roles in User and Users in Role. Is that possible to be designed through the Model Designer? How can I configure manually many-to-many relationship with a table in the middle?

Answer 1

EF normally creates the intermediate model, if the UserRole table comprises of columns other than foreign keys for User and Role table.

So if you had just 2 columns in the UserRoles table, both FKs to the User and Role tables (not even a surrogate key), EF would create the Model as you wanted. (without any intermediate model) So that is one way to go, for automatic generation of the desired behavior. Have just 2 columns in the table.

But if you have other non-key columns (data) in this table, then what EF is doing is correct. You need the intermediate entity.

And in the case where you don't have any non-key columns, don't want to modify your DB anymore and don't need this middle table in your model, you could manually modify the OnModelCreating, to specify the Many-to-Many and hide the intermediate table.

Here are all the steps:

1. Remove the intermediate table definition C# class from the model layer, and its references in DbContext and User and Role classes.
2. Add a virtual Collection property in both User and Role class, for each other.

e.g. in the User class,

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

and in the User constructor

this.Roles = new HashSet<Role>();

// on the OnModelCreating method, add this snippet
modelBuilder.Entity<User>().HasMany<Role>(u => u.Roles)
                          .WithMany(r => r.Users)
                          .Map(ru => 
                                   {  
                                     ru.MapLeftKey("UserId");        
                                     ru.MapRightKey("RoleId"); 
                                     ru.ToTable("UserRole"); 
                                   });