Clever Geek Handbook

EF Code First - Many-Many Relationship Mapping Table With Additional Columns

I have a user model and a group model. User and group share many relationships. When I translate this to a table, I want to have a mapping table. I use the following for 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 problems

I would like to add an Identity column to this table and some audit columns (eg: created, date created) to the table. I'm not sure how to do this.

Can anyone help me here?

thank

+3


source to share


1 answer


I think it will work if you do the following:

  • Remove the configuration mentioned in the code snippet above

  • Add a mapping table and customize 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 properties of the many-to-many collection 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 Package Manager for Add-Migration

    and remove anything from shimming migration that might try to drop the old table and create a new table. The migration would require at least (I could skip a few here):

    • DropPrimaryKey

      for original key columns
    • AddColumn

      for new columns (with Int(identity:true, nullable: false)

      for new primary key column)
    • AddPrimaryKey

      for new key column


Then you can use the methods described in this answer to retrieve objects.

+6


source






More articles:


All Articles