In this article, we will learn how to Configure Many to Many Relationship in Entity Framework core. You can visit the Tutorial Relationships in Entity Framework to learn how to configure one-to-one or one-to-many relationships between entities.
Table of Contents
Configure Many-to-Many relationship
Consider the following Model of Employee and Project.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | public class Employee { public int EmployeeID { get; set; } public string Name { get; set; } //Navigational Property public virtual ICollection<Project> Projects { get; set; } } public class Project { public int ProjectID { get; set; } public string Name { get; set; } //Navigational Property public virtual ICollection<Employee> Employees { get; set; } } |
The relationship between employee and projects is many to many. The employee can be part of more than one project. The Projects can have many employees.
Recomended Book :Entity Framework Core in Action
Many to Many Relationship in EF Core Convention
The EF Core Convention does create the Many to Many Relationship
Many to Many Relationship Using Data Annotations
Many to Many Relationship Using Data Annotations requires you to create the Join Table in the model
The Join Table EmployeesInProject will have properties for the Foreign key from both tables. It will have two navigational properties one each for employee and Project class.
1 2 3 4 5 6 7 8 9 10 11 12 | public class EmployeesInProject { public int EmployeeID { get; set; } public int ProjectID { get; set; } [ForeignKey("EmployeeID")] public Employee Employee { get; set; } [ForeignKey("ProjectID")] public Project Project { get; set; } } |
The EmployeesInProject needs a Primary Key. Currently the data annotations in EF Core does not have the option of creating Composite Primary Key.
Hence, we may have to fall back to Fluent API to create the Composite Key. In the onModelCreating method add the following code to add primary key to the EmployeesInProject
1 2 3 4 | modelBuilder.Entity<EmployeesInProject>() .HasKey(e => new { e.EmployeeID, e.ProjectID }); |
The Employee and Project entity will have the navigational property which maps to the Join Table EmployeesInProject.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | public class Employee { public int EmployeeID { get; set; } public string Name { get; set; } public virtual ICollection<EmployeesInProject> EmployeesInProject { get; set; } } public class Project { public int ProjectID { get; set; } public string Name { get; set; } public virtual ICollection<EmployeesInProject> EmployeesInProject { get; set; } } |
The above model is mapped to the following database as shown in the image below
Many to Many Relationship Using Fluent API
In the older versions of Entity Framework automatically created join table. This feature is not supported in EF Core.
In EF Core, we must create joining entity class and then setup two one to many relationship with the joining entity.
The join table is similar to the one created in the previous section. Only change is that we are not using the ForeignKey attribute as we are creating the keys in Fluent API
1 2 3 4 5 6 7 8 9 10 11 | public class EmployeesInProject { public int EmployeeID { get; set; } public Employee Employee { get; set; } public int ProjectID { get; set; } public Project Project { get; set; } } |
In the Employee & Project models create collection navigational property EmployeesInProject as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | public class Employee { public int EmployeeID { get; set; } public string Name { get; set; } public virtual ICollection<EmployeesInProject> EmployeesInProject { get; set; } } public class Project { public int ProjectID { get; set; } public string Name { get; set; } public virtual ICollection<EmployeesInProject> EmployeesInProject { get; set; } } |
Make EmployeeID & ProjectID as the primary key of the join tableEmployeesInProject
1 2 3 4 | modelBuilder.Entity<EmployeesInProject>() .HasKey(e => new { e.EmployeeID, e.ProjectID }); |
Create one to many relationship between EmployeesInProject & Employee
1 2 3 4 5 | modelBuilder.Entity<EmployeesInProject>() .HasOne<Employee>(e => e.Employee) .WithMany(p => p.EmployeesInProject); |
Finally, Create the one to many relationship between EmployeesInProject & Project
1 2 3 4 5 | modelBuilder.Entity<EmployeesInProject>() .HasOne<Project>(e => e.Project) .WithMany(p => p.EmployeesInProject); |
Thats it.
Conclusion
In this tutorial, we looked at How to create Many to Many Relationship in Entity Framework Core using Default Convention, Data Annotations, and Fluent API
I’ve tried this, but the properties Employee and Project in EmployeesInProject are empty. Both Id’s are filled. How do I fill those two properties?
Same
Kindly give an example of m-to-m, how we can get employees if we have projectId. A LINQ query please.
var employees = (from p in _context.Projects
join ep in _context.EmployeesInProjects
on p.ProjectId equal ep.ProjectId
join e in _context.Employees
on ep.EmployeeId equal e.EmployeeId
where p.ProjectId=your_ProjectId
select new {
EmployeeId=e.EmployeeId,
EmployeeName=e.EmployeeName,
…………………………..
}).ToList();