Rasmus Rajje Josefsson Rasmus Rajje Josefsson - 7 days ago 4
C# Question

Many to many with addition fields, Entity Framework

How could I map a custom many to many relationship with addition fields? In this case DateTime in Schedule.

Problem now is it creates a new table named StaffSchedules

public class Schedule
{
public int Id { get; set; }
public DateTime DateTime { get; set; }

public List<Staff> Staff { get; set; }
public int StaffId { get; set; }

public Patient Patient { get; set; }
public int PatientId { get; set; }
}

public class Staff
{
public int Id { get; set; }
public string FirstName { get; set; }

public List<Schedule> Schedules { get; set; }
}


...

public DbSet<Schedule> Schedules { get; set; }
public DbSet<Staff> Staff { get; set; }


Desired outcome:
enter image description here

Answer

Add a class like so:

public class StaffSchedule
{
    public int StaffId { get; set; }
    public int ScheduleId { get; set; }
    public virtual Staff Staff { get; set; }
    public virtual Schedule Schedule { get; set; }

    public DateTime Date { get; set; }
}

Modify your Staff and Schedule classes:

public class Schedule
{
    public int Id { get; set; }
    //Other Fields
    public ICollection<StaffSchedule> StaffSchedules { get; set; }
}

public class Staff
{
    public int Id { get; set; }
    //Other Fields
    public ICollection<StaffSchedule> StaffSchedules { get; set; }
}

And lastly, in your context file (OnModelCreating method):

modelBuilder.Entity<Staff>().HasKey(x => x.Id);
modelBuilder.Entity<Schedule>().HasKey(x => x.Id);

modelBuilder.Entity<StaffSchedule>().HasKey(x =>
    new
    {
        x.StaffId,
        x.ScheduleId
    });

modelBuilder.Entity<StaffSchedule>()
    .HasRequired(x => x.Staff)
    .WithMany(x => x.StaffSchedules)
    .HasForeignKey(x => x.StaffId);

modelBuilder.Entity<StaffSchedule>()
    .HasRequired(x => x.Schedule)
    .WithMany(x => x.StaffSchedules)
    .HasForeignKey(x => x.ScheduleId);

UPDATE:

Looks like you actually wanted this:

public class Patient
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Staff
{
    public int Id { get; set; }
    public string Name { get; set; }
}

public class Schedule
{
    public int Id { get; set; }
    public DateTime Date { get; set; }

    public int PatientId { get; set; }
    public int StaffId { get; set; }

    [ForeignKey("PatientId")]
    public virtual Patient Patient { get; set; }
    [ForeignKey("StaffId")]
    public virtual Staff Staff { get; set; }
}