Configure Many-to-Many relationship using Fluent API in Entity Framework Core

Entity Framework Core Many-to-Many Relationship is configured through Fluent API. We first add collection navigation property on both the entities and then add the join entity type with the UsingEntity() method.

In Entity Framework Core the Many-to-many relationships are created differently than one-to-many and one-to-one relationships. The difference is that they cannot be represented in a simple way using just a foreign key. Instead, here an additional entity is needed to “join” the two sides of the relationship. This new entity is known as the “join entity type” and maps to a “join table” in a relational database. The join entity contain pairs of foreign key values, where one of each pair points to an entity on one side of the relationship, and the other points to an entity on the other side of the relationship. So each row of the join table represents this Many-to-Many relationship.

Note that it is optional to define the Join Entity in our app because EF core will automatically create it for us.

This tutorial is a part of Entity Framework Core series.

• 1. Introduction to Entity Framework Core
• 2. Installation of Entity Framework Core
• 3. Database-First approach in Entity Framework Core
• 4. DbContext Class in Entity Framework Core
• 5. Code-First Approach in Entity Framework Core
• 6. Migrations in Entity Framework Core
• 7. Insert Records in Entity Framework Core
• 8. Read Records in Entity Framework Core
• 9. Update Records in Entity Framework Core
• 10. Delete Records in Entity Framework Core
• 11. Conventions in Entity Framework Core
• 12. Configurations in Entity Framework Core
• 13. Fluent API in Entity Framework Core
• 14. Configure One-to-Many relationship using Fluent API in Entity Framework Core
• 15. Configure One-to-One relationship using Fluent API in Entity Framework Core
• 16. Configure Many-to-Many relationship using Fluent API in Entity Framework Core
• 17. Execute Raw SQL Queries using FromSqlRaw() method in Entity Framework Core
• 18. Execute SQL Stored Procedures using FromSqlRaw() & ExecuteSqlRawAsync() methods in Entity Framework Core

Example: Many-to-Many Relationship with Fluent API

Consider the 2 entity classes – Student & Teacher.

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

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

We first add collection navigation properties on both these entities towards one another.

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

    public List<Teacher> Teacher { get; } = []; //collection navigation property
}

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

    public List<Student> Student { get; } = []; //collection navigation property
}

Next on the DbContext OnModelCreating method we add join entity type with the UsingEntity method. In the below code we have given the name for this join entity as TeacherStudent.

public class SchoolContext : DbContext
{
    public DbSet<Teacher> Teacher { get; set; }
    public DbSet<Student> Student { get; set; }

    public SchoolContext(DbContextOptions<SchoolContext> options) : base(options)
    {
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        //Write Fluent API configurations here

        modelBuilder.Entity<Teacher>()
                    .HasMany(t => t.Student)
                    .WithMany(t => t.Teacher)
                    .UsingEntity("TeacherStudent");
    }
}

When we perform the migrations, the Entity Framework Core will create the a join table by the name of TeacherStudent that will contains foreign keys to both Teacher and Student table records. The foreign key names will be “TeacherId” & “StudentId”. Check the below image which explains this.

We can also define the relationship more explicitly as shown below. Note that we changed the name of the join table as “TeacherStudentJoin”. The names of foreign key are defined as “StudentFrId” & “TeacherFrId”.

modelBuilder.Entity<Student>()
            .HasMany(e => e.Student)
            .WithMany(e => e.Teacher)
            .UsingEntity(
                "TeacherStudentJoin",
                l =>  l.HasOne(typeof(Student)).WithMany().HasForeignKey("StudentFrId").HasPrincipalKey(nameof(Student.Id)),
                r => r.HasOne(typeof(Teacher)).WithMany().HasForeignKey("TeacherFrId").HasPrincipalKey(nameof(Teacher.Id)),
                j => j.HasKey("StudentFrId", "TeacherFrId"));

Many-to-many with Join Entity Type Class

We can also define the Join Entity Type Class in our app. This join entity class will contain Many-to-Many relationship. Here we named this joining entity as – Teacher Student. You can name it anything you like.

This joining entity will contain the foreign keys for both the other entities. These foreign keys will form the composite primary key for this joining entity.

The code for this is shown below.

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
 
    public List<Teacher> Teacher { get; } = []; //collection navigation property
}
 
public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
 
    public List<Student> Student { get; } = []; //collection navigation property
}

public class TeacherStudent
{
    public int StudentId { get; set; }  //foreign key property
    public int TeacherId { get; set; }  //foreign key property

    public Student Student_R { get; set; } = null!; //Reference navigation property
    public Teacher Teacher_R { get; set; } = null!; //Reference navigation property
}

Next on DBContext’s OnModelCreating() method configure both the foreign keys in the joining entity as a composite key using Fluent API. See the below code.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<TeacherStudent>().HasKey(t => new { t.StudentId, t.TeacherId}); // composite primary key
    modelBuilder.Entity<Student>()
                .HasMany(e => e.Teacher)
                .WithMany(e => e.Student)
                .UsingEntity<TeacherStudent>();
}

On performing EF Core Migrations we will get the Many-to-Many Relationship created as shown in the below image:

Many-to-many and Join Table with Payload

A payload is extra data on the Join Table, example for the payload can be the created time when the relationship is created. We add the payload property on the Join entity. Then add navigations properties that allow the join entity to be easily referenced from the code, thus helping in reading and changing the payload data.

See the below code where we added List<TeacherStudent> TeacherStudent { get; set; } on both the Student and Teacher entities. This is the navigation property to the Join Entity. The payload data is public DateTime CreatedOn { get; set; } which is given on the “TeacherStudent” entity.

public class Student
{
    public int Id { get; set; }
    public string Name { get; set; }
  
    public List<Teacher> Teacher { get; set; } //collection navigation property

    public List<TeacherStudent> TeacherStudent { get; set; }
}
  
public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }
  
    public List<Student> Student { get; set; } //collection navigation property

    public List<TeacherStudent> TeacherStudent { get; set; }
}
 
public class TeacherStudent
{
    public Student StudentId { get; set; }
    public Teacher TeacherId { get; set; }
    public DateTime CreatedOn { get; set; }
}

The Fluent API configuration code is given below. The payload with is the timestamp is set automatically when a row is inserted. We are using HasDefaultValueSql() method of it.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .HasMany(e => e.Teacher)
        .WithMany(e => e.Student)
        .UsingEntity<TeacherStudent>(
            j => j.Property(e => e.CreatedOn).HasDefaultValueSql("GETUTCDATE()"));
}

Many-to-many with Alternate Keys

If alternate keys are present then each foreign key can be constrained to the alternate keys. The below example shows this:

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

    public int AlternateKey { get; set; }

    public List<Teacher> Teacher { get; set; } //collection navigation property
}
 
public class Teacher
{
    public int Id { get; set; }
    public string Name { get; set; }

    public int AlternateKey { get; set; }

    public List<Student> Student { get; set; } //collection navigation property
}

The configuration is given below.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>()
        .HasMany(e => e.Teacher)
        .WithMany(e => e.Student)
        .UsingEntity(
            l => l.HasOne(typeof(Student)).WithMany().HasPrincipalKey(nameof(Student.AlternateKey)),
            r => r.HasOne(typeof(Teacher)).WithMany().HasPrincipalKey(nameof(Teacher.AlternateKey)));
}

Download the source code:

DOWNLOAD