EF Code first - many to many relation mapping table with extra columns

Question

I am having a User Model and and a Group Model. User and Group share a many to many relationship. In when I translate this to table, I want to have a mapping table. I am using the following to achieve this.

modelBuilder.Entity<UserGroup>()
        .HasMany(a => a.Users)
         .WithMany(b => b.UserGroup)

         .Map(mc =>
         {

             mc.ToTable("UserUserGroupMapping");
             mc.MapLeftKey("UserId");
             mc.MapRightKey("UserGroupId");

         });

This creates a table with UserId and UserGroupId as columns. However I have few challenges,

I would like to be able to add an Identity column to this table and some audit columns (ex: Created by, created date) to the table. I am not sure how to do this.

Can any one help me here?

Thanks

possible duplicate of [Create code first, many to many, with additional fields in association table](http://stackoverflow.com/questions/7050404/create-code-first-many-to-many-with-additional-fields-in-association-table) — jjj, Jun 17 '15 at 18:57
http://stackoverflow.com/questions/29831729/asp-net-mvc-many-to-many-relationship-using-my-own-table and http://stackoverflow.com/questions/30577738/how-to-define-many-to-many-with-attributes-in-entity-framework — jjj, Jun 17 '15 at 19:01
or do you mean you already have that table and you want to create a model for it? — jjj, Jun 17 '15 at 19:03

score 8 · Accepted Answer · edited May 23 '17 at 11:33

I think it'll work if you do the following:

Remove the configuration you showed in the code snippet above

Add a mapping table and configure its table name to match the original table name.

// name this whatever you want
class UserUserGroupMapping
{
    public UserUserGroupMappingId { get; set; }
    public int UserId { get; set; }
    public virtual User User { get; set; } 
    public int UserGroupId { get; set; }
    public virtual UserGroup UserGroup { get; set; } 
    // other properties
}

modelBuilder.Entity<UserUserGroupMapping>()
    .HasKey(um => um.UserUserGroupMappingId)
    .ToTable("UserUserGroupMapping");

Replace the many-to-many collection properties from User and UserGroup and replace it with one-to-many associations

class User
{
    // other properties
    // remove this:
    // public virtual ICollection<UserGroup> UserGroup { get; set; }
    public virtual ICollection<UserUserGroupMapping> UserGroupMappings { get; set; }
}

class UserGroup
{
    // other properties
    // remove this:
    // public virtual ICollection<User> Users { get; set; }
    public virtual ICollection<UserUserGroupMapping> UserMappings { get; set; }
}

modelBuilder.Entity<UserUserGroupMapping>()
    .HasRequired(um => um.UserGroup).WithMany(g => g.UserMappings)
    .HasForeignKey(um => um.UserGroupId);

modelBuilder.Entity<UserUserGroupMapping>()
    .HasRequired(um => um.User).WithMany(g => g.UserGroupMappings)
    .HasForeignKey(um => um.UserId);

Use the package manager to Add-Migration and remove anything from the scaffolded migration that might attempt to drop the old table and create a new table. The migration will need to at least (I might be missing some here):
- DropPrimaryKey for the original key columns
- AddColumn for the new columns (with Int(identity:true, nullable: false) for the new primary key column)
- AddPrimaryKey for the new key column

Then you can use the methods outlined in this answer to retrieve entities.

EF Code first - many to many relation mapping table with extra columns

1 Answers1