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

ef-code-first ef-migrations entity-framework entity-framework-6

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

1
4
6/24/2015 4:49:59 PM

Accepted Answer

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.

8
5/23/2017 11:33:13 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow