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.