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

Answer 1

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

1. Remove the configuration you showed in the code snippet above

2. 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");
   

3. 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);
   

4. 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.