locked
EF how to insert 3 keys junction table? RRS feed

  • Question

  • User1506260647 posted

    In EF code first, how do I do insert for a 3 keys Junction table [LNK_UserRoleAssoc]. A User has many associations. For a given Association, a user can only have one Role.

    [User] 
    UserId
    
    [Association]
    AssocId
    
    [Role]
    RoleId
    
    [LNK_UserRoleAssoc]
    UserId   [PK]
    AssocId  [PK]
    RoleId   [FK]


    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<LNK_UserRoleAssoc>()
            .HasKey(c => new { c.UserId, c.AssocId, c.RoleId });
    
        modelBuilder.Entity<LNK_UserRoleAssoc>()
           .HasRequired(p => p.User)
           .WithMany(u => u.UserRoleAssocs)
           .HasForeignKey(p => p.UserId);
    
        modelBuilder.Entity<LNK_UserRoleAssoc>()
           .HasRequired(p => p.Association)
           .WithMany(a => a.UserRoleAssocs)
           .HasForeignKey(p => p.AssocId);
    
        modelBuilder.Entity<LNK_UserRoleAssoc>()
           .HasOptional(p => p.Role)
           .WithMany(r => r.UserRoleAssocs)
           .HasForeignKey(p => p.RoleId);
    }


    Monday, July 27, 2015 2:19 PM

Answers

  • User-84896714 posted

    Hi skyblue789,

    Thank you for your post. According to your description, I suggest you create entity like below.

        public class Junction3TablesContext : DbContext
        {
            public Junction3TablesContext()
                : base()
            { 
            }
    
            public virtual DbSet<User> Users { get; set; }
    
            public virtual DbSet<Association> Associations { get; set; }
    
            public virtual DbSet<Role> Roles { get; set; }
    
            public virtual DbSet<LNK_UserRoleAssoc> LNK_UserRoleAssocs { get; set; }
        }
    
        public class User
        {
            [Key]
            public int UserId { get; set; }
    
            public string UserName { get; set; }
    
            public virtual ICollection<LNK_UserRoleAssoc> LNK_UserRoleAssocs { get; set; }
        }
    
        public class Association
        {
            [Key]
            public int AssocId { get; set; }
    
            public string AssocName { get; set; }
    
            public virtual ICollection<LNK_UserRoleAssoc> LNK_UserRoleAssocs { get; set; }
        }
    
        public class Role
        {
            [Key]
            public int RoleId { get; set; }
    
            public string RoleName { get; set; }
    
            public virtual ICollection<LNK_UserRoleAssoc> LNK_UserRoleAssocs { get; set; }
        }
    
        public class LNK_UserRoleAssoc
        {
            [Key]
            [Column(Order = 0)]
            public int UserId { get; set; }
            [Key]
            [Column(Order = 1)]
            public int AssocId { get; set; }
            [Key]
            [Column(Order = 2)]
            public int RoleId { get; set; }
            [ForeignKey("UserId")]
            public User User { get; set; }
            [ForeignKey("AssocId")]
            public Association Association { get; set; }
            [ForeignKey("RoleId")]
            public Role Role { get; set; }
        }

    Then you could operate database like below.

    Junction3TablesContext db = new Junction3TablesContext();
    var user = db.Users.Add(new User { UserId = 1, UserName = "Jhon" });
    var assoc = db.Associations.Add(new Association { AssocId = 1, AssocName = "AN" });
    var role = db.Roles.Add(new Role { RoleId = 1, RoleName = "RN" });
    var lnk = db.LNK_UserRoleAssocs.Add(new LNK_UserRoleAssoc { User = user, Role = role, Association = assoc });
    db.SaveChanges();

    Best Regards,
    Wang Li

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 28, 2015 1:39 AM