EF Core Many-to-many Relationships

Summary: in this tutorial, you will learn about EF Core’s many-to-many relationships and how to model them properly.

Introduction to the EF Core Many-to-many relationship

Many-to-many relationships allow you to associate multiple rows from a table with multiple rows in another table.

For example, an employee can have multiple skills while a skill is processed by multiple employees. Therefore, the relationship between employees and skills is a many-to-many relationship.

To model a many-to-many relationship between two tables, we often use a junction table and two one-to-many relationships:

In this diagram, the EmloyeeSkill is a junction table that has two columns:

• EmployeeId – the employee’s Id that references the Id column in the Employees table. the EmployeeId is a foreign key to the Employees table.
• SkillId – the skill’s Id that references the Id column in the Skills table. The SkillId column is a foreign key to the Skills table.
• Also, EmployeeId and SkillId columns form the primary key of the EmployeeSkill table. Since the primary key has two columns, it is called a composite key. It ensures that one employee possesses one skill at one time and vice versa.

In EF Core, a many-to-many relationship associates a number of entities of one entity type with any number of entities of the same or another entity type.

The easiest way to model a many-to-many relationship between two entities is to define a navigation property at both ends. For example:

public class Employee
{
    public int Id { get; set; }
    public required string FirstName {  get; set; } 
    public required string LastName {  get; set; }
    public required decimal Salary { get; set; }
    public required DateTime JoinedDate { get; set; }
    public int DepartmentId  {   get; set; }

    // Reference navigation to Department
    public Department Department { get; set; } = null!;

    // Reference navigation to EmployeeProfile
    public EmployeeProfile? Profile  { get; set; }

    // collection navigation to Employee
    public List<Skill> Skills { get; set; } = new();
}

public class Skill
{
    public int Id  { get; set; }
    public required string Title  {   get;set;   }

    // collection navigation to Employee
    public List<Employee> Employees { get; set; } = new();
}Code language: C# (cs)

In this example, the Employee class has a collection of Skills and the Skill class has a collection of Employees.

The HRContext class defines both Employee and Skill DbSet:

using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Logging;

namespace HR;

public class HRContext : DbContext
{
    public DbSet<Employee> Employees { get; set; }
    public DbSet<Department> Departments  { get; set; }
    public DbSet<EmployeeProfile> EmployeeProfiles  { get; set; }
    public DbSet<Skill> Skills { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {

        var configuration = new ConfigurationBuilder()
            .SetBasePath(Directory.GetCurrentDirectory())
            .AddJsonFile("appsettings.json")
            .Build();

        var connectionString = configuration.GetConnectionString("Local");


        optionsBuilder.UseSqlServer(connectionString)
                      .LogTo(Console.WriteLine,
                             new[] { DbLoggerCategory.Database.Command.Name },
                             LogLevel.Information)
                      .EnableSensitiveDataLogging();
    }

}Code language: C# (cs)

By convention, EF Core will create three tables: Employees, Skills, and EmployeeSkill tables.

Summary

• Use many-to-many relationships to associate any number of entities of an entity type with any number of entities of the same or another type.
• Define navigation collection in both entity types to model a many-to-many relationship.