How to handle Many-to-Many Relationship in EF Core

There are a few steps that are instrumental in handling a Many-to-Many relationship in EF Core. It must be noted that EF Core 1.1 does not support Many-to-Many relationship right off the bat. However, with a junction table in hand, you can achieve this in just a few easy steps. Here’s how:

Let’s write a couple of POCO classes first. Below are the two main classes, namely Department and Employee respectively. Let’s just assume the relationship between the two is Many-to-Many.

    class Department
    {
        public int ID { get; set; }
        public string Name { get; set; }
        public string Location { get; set; }

        public List DepartmentEmployees { get; set; }
    }
    class Employee
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public int Age { get; set; }

        public List DepartmentEmployees  { get; set; }
    }

As indicated above; in order for it work, we need a join table such that it can contain foreign keys of Department and Employee Classes like so:

class DepartmentEmployee
    {
        public int DepartmentID { get; set; }
        public int EmployeeID { get; set; }

        //Navigation Properties
        public Department Department { get; set; }
        public Employee Employee { get; set; }
    }

Now, add a class which is to be derived from DbContext. For this, ensure that you have the necessary packages installed which are the following:

  1. Microsoft.EntityFrameworkCore.SqlServer
  2. Microsoft.EntityFrameworkCore.SqlServer.Design
  3. Microsoft.EntityFrameworkCore.Tools

Please note, I have installed  SqlServer.Design package only because, I am using SQL Server. I were using DB2, I would need to install the DB2.Design package and so on.

class DepartmentContext : DbContext
    {
        public DbSet Department { get; set; }
        public DbSet Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"server=obi-oberoi; database=demo.ManyToMany; trusted_connection=true;");
        }
    }

Now, let’s try and add our first migration using the Package Manager Console window and see what we get:
PM>add-migration initial

InitialMigrationNotice, the message that says “The entity type “DepartmentEmployee” requires a primary key to be defined”. This is because EF Core could not discover a primary key in the DeparmentEmployee model even though they are defined in the class.

One thing to keep in mind though is that in order for a Join table to work, you need composite primary key(s). However, the creation of a composite key cannot be achieved simply through Data Annotations as it only provides a scaled down version of Fluent API. For that you need a Fluent API.

In order to resolve this, let’s override the OnModelCreating() method in our context and configure the ModelBuilder API that’ll define the shape of our entity, the relationship and how it maps to the database.

Given that it’s a composite key, it’s made up of the DepartmentID and EmployeeID.

    class DepartmentContext : DbContext
    {
        public DbSet Department { get; set; }
        public DbSet Employee { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer(@"server=obi-oberoi; database=demo.ManyToMany; trusted_connection=true;");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity()
                .HasKey(d => new { d.DepartmentID, d.EmployeeID });
        }
    }

Now, let’s try adding a migration.CompositeKeyand it worked!!!

About Obi Oberoi

Obi Oberoi is a Microsoft MVP (Visual Studio & Development Technologies), developer and a life-long student of continuous learning. Obi enjoys to code, read, and hang out with techies and geeks!
This entry was posted in ORM. Bookmark the permalink.