Many to Many Relationships in Entity Framework

A many-to-many (N) relationship occurs when multiple records in one table can be associated with multiple records in another table.

This type of relationship is more complex due to the need for an intermediate table to store the associations (called a bridge table or join table).

Let’s see it by modeling a course with students (sorry for the cliché), where,

public class Student
{
    public int StudentId { get; set; }
    public string Name { get; set; }
    
    // Navigation property
    public ICollection<Course> Courses { get; set; } = new List<Course>();
}

public class Course
{
    public int CourseId { get; set; }
    public string CourseName { get; set; }
    
    // Navigation property
    public ICollection<Student> Students { get; set; } = new List<Student>();
}

Configuring the NRelationship

In relational databases, many-to-many (N) relationships cannot exist directly between two tables.

To implement them, an intermediate table is required (called a bridge table or join table) that stores the valid combinations between both entities.

We can let Entity Framework create this table automatically for us, or we can create the corresponding Entity ourselves and perform the associations manually.

Implicit Join Table (Automatic)

Entity Framework Core (since version 5) can automatically generate this table without you having to create a class for it.

Configuration with Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .HasMany(e => e.Courses)
        .WithMany(c => c.Students)
        .UsingEntity(j => j.ToTable("StudentCourses"));
}

This would be the structure generated in the database

CREATE TABLE StudentCourses (
    StudentsStudentId INT NOT NULL,
    CoursesCourseId INT NOT NULL,
    PRIMARY KEY (StudentsStudentId, CoursesCourseId),
    FOREIGN KEY (StudentsStudentId) REFERENCES Students(StudentId),
    FOREIGN KEY (CoursesCourseId) REFERENCES Courses(CourseId)
);

Explicit Join Table (Manual)

When you need to store additional information about the relationship (like metadata), you must create an explicit entity for the join table.

In the previous example, this explicit table could be Enrollment,

public class Enrollment
{
    public int StudentId { get; set; }
    public int CourseId { get; set; }
    public DateTime EnrollmentDate { get; set; } // Additional data
    public decimal? Grade { get; set; }         // Additional data
    
    // Navigation properties
    public Student Student { get; set; }
    public Course Course { get; set; }
}

Here’s how we would configure it in Fluent API:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Enrollment>()
        .HasKey(i => new { i.StudentId, i.CourseId }); // Composite key
        
    modelBuilder.Entity<Enrollment>()
        .HasOne(i => i.Student)
        .WithMany(e => e.Enrollments)
        .HasForeignKey(i => i.StudentId);
        
    modelBuilder.Entity<Enrollment>()
        .HasOne(i => i.Course)
        .WithMany(c => c.Enrollments)
        .HasForeignKey(i => i.CourseId);
}

Advanced Configurations

Custom options to adjust the behavior of the data model in Entity Framework Core.

Naming Columns in the Implicit Join Table

The names of the columns in an automatically generated join table for a many-to-many relationship are defined.

modelBuilder.Entity<Student>()
    .HasMany(e => e.Courses)
    .WithMany(c => c.Students)
    .UsingEntity<Dictionary<string, object>>(
        "StudentCourses",
        j => j.HasOne<Course>().WithMany().HasForeignKey("CourseId"),
        j => j.HasOne<Student>().WithMany().HasForeignKey("StudentId")
    );

Configuring Cascade Deletion

It is established that when a student is deleted, their associated enrollments are also automatically deleted.

modelBuilder.Entity<Enrollment>()
    .HasOne(i => i.Student)
    .WithMany(e => e.Enrollments)
    .HasForeignKey(i => i.StudentId)
    .OnDelete(DeleteBehavior.Cascade);

Practical Examples

// Get all courses for a student
var student = context.Students
    .Include(e => e.Courses)
    .FirstOrDefault(e => e.StudentId == 1);

// Get all students in a course
var course = context.Courses
    .Include(c => c.Students)
    .FirstOrDefault(c => c.CourseId == 101);

var history = context.Enrollments
    .Where(i => i.StudentId == 1)
    .Include(i => i.Course)
    .OrderBy(i => i.EnrollmentDate)
    .ToList();

// Get existing student and course
var student = context.Students.Find(1);
var course = context.Courses.Find(101);

// Establish relationship
student.Courses.Add(course);
context.SaveChanges();

var newEnrollment = new Enrollment
{
    StudentId = 1,
    CourseId = 101,
    EnrollmentDate = DateTime.Now,
    Grade = null
};

context.Enrollments.Add(newEnrollment);
context.SaveChanges();