Join table in many-to-many with 3-column primary key

Question

I want to create 3 tables based on 3 entities using code-first and fluent API. I am using Entity Framework version 6. The join table needs a 3-column primary key and additional columns.

My question: how can I use code-first with C# Fluent API to create/map the 3-column primary key for the PatientTreatment table? Thank you.

Details of the 3-column primary key for the join table { PatentId, TreatmentId , TreatmentDate }. The values of PatentId and TreatmentId are fetched from the other 2 entities (tables) while the value of TreatmentDate is entered manually (e.g. C# code or T-SQL script like calling getdate() function).

Details of the 3 entities:

public class Patient {
  public long PatentId {get; set;} // database created using Identity
  ...
}

public class Treatment {
  public long TreatmentId {get; set;}  // database created using Identity
  ... 
}

And the join table (entity)

public class PatientTreatment
{
   public long PatentId {get; set;} // part of the primary key from the Patient entity
   public long TreatmentId {get; set;} // part of the primary key from the Treatment entity
   public DateTime TreatmentDate {get; set;} // part of the primary key but its value is from C# code or from T-SQL script, not from other entity (table)
   // other fields ...
}

score 1 · Accepted Answer · answered Nov 04 '16 at 22:22

You can't model this as a many-to-many association in which the PatientTreatment class is hidden, which is what is usually referred to as many-to-many in Entity Framework mapping.

But you didn't intend to do that, as is apparent from the explicit PatientTreatment class you show. So it's just a matter of modeling it correctly.

In the OnModelCreating override of your DbContext subclass, setup the mapping like so:

protected override void OnModelCreating(DbModelBuilder mb)
{
    mb.Entity<PatientTreatment>()
      .HasKey(x => new { x.PatientId, x.TreatmentId, x.TreatmentDate });
    mb.Entity<Patient>().HasMany(p => p.PatientTreatments)
      .WithRequired().HasForeignKey(x => x.PatientId);
    mb.Entity<Treatment>().HasMany(t => t.PatientTreatments)
      .WithRequired().HasForeignKey(x => x.TreatmentId);

    base.OnModelCreating(mb);
}

I think this line HasKey(x => new { x.PatientId, x.TreatmentId, x.TreatmentDate }) is what you were looking for mainly.

score 0 · Answer 2 · edited May 23 '17 at 12:01

I Just Serch it in stackoverflow

It's not possible to create a many-to-many relationship with a customized join table. In a many-to-many relationship EF manages the join table internally and hidden. It's a table without an Entity class in your model. To work with such a join table with additional properties you will have to create actually two one-to-many relationships check this many to many with extra column

Join table in many-to-many with 3-column primary key

2 Answers2