Creating record for entity framework with relationships to other records : ASP.NET Core 6 Minimal API (SQL / Postgres)

Question

I have two tables, Participant and Meeting, in an ASP.NET Core 6 minimal API project set up like this:

record Participant(int id)
{
    public string name { get; set; } = default!;
    public string title { get; set; } = default!;
}

record Meeting(int id)
{
    public string subject { get; set; } = default!;
    public string organizer { get; set; } = default!;
    public DateTime starttime { get; set; } = default!;
    public DateTime endtime { get; set; } = default!;
}

class MyDb: DbContext 
{
    public MyDb(DbContextOptions<MyDb> options): base(options) 
    {
    }

    public DbSet<Participant> Participants => Set<Participant>();

    public DbSet<Meeting> Meetings => Set<Meeting>();
}

Now I need to create a combining table / record which connects these two tables / records

In SQL this would be this table

CREATE TABLE meetings_participants
(
    meeting_id int NOT NULL,
    participant_id int NOT NULL,
    PRIMARY KEY (meeting_id, participant_id),
    CONSTRAINT fk_meeting_id 
        FOREIGN KEY (meeting_id) REFERENCES meetings(id),
    CONSTRAINT fk_participant_id 
        FOREIGN KEY (participant_id) REFERENCES participants(id)
);

But how do I write it in EF as a record with foreign keys ?

Answer 1

You can create a MeetingParticipant class, like this:

public class MeetingParticipant
{
    public int  ParticipantID { get; set; }
    [ForeignKey("ParticipantID")]
    public virtual Participant Participant { get; set; }
    public int MeetingID { get; set; }
    [ForeignKey("MeetingID")]
    public virtual Meeting Meeting { get; set; }
}

And update the Participant and Meeting class as below:

public record Participant(int id)
{
    public string name { get; set; } = default!;
    public string title { get; set; } = default!;

    public List<MeetingParticipant> MeetingParticipant { get; set; }
}

public record Meeting(int id)
{
    public string subject { get; set; } = default!;
    public string organizer { get; set; } = default!;
    public DateTime starttime { get; set; } = default!;
    public DateTime endtime { get; set; } = default!;
    public List<MeetingParticipant> MeetingParticipant { get; set; }
}

Then, update the ApplicationDbContext (you can change it to yours) as below:

public class ApplicationDbContext : IdentityDbContext
{ 
    public DbSet<Participant> Participants => Set<Participant>();

    public DbSet<Meeting> Meetings => Set<Meeting>();

    public DbSet<MeetingParticipant> MeetingParticipant { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    { 
        base.OnModelCreating(modelBuilder);
        //set the primary key
        modelBuilder.Entity<MeetingParticipant> ().HasKey(mp=> new { mp.MeetingID, mp.ParticipantID}); 
    }

Then, enable migration using EF migration command, like this (if using Visual Studio):

Add-Migration AddMeetingParticipant
Update-Database

Then, it will configure many-to-many between the Participant and Meeting table, and the MeetingParticipant table is used to store the relationships.

More detail information about EF core relationship, refer to the following articles:

Many-to-many Relationships

Configure Many-to-Many Relationships in Entity Framework Core