Link Many To Many entities with shadow join-table using Entity Framework Core

Aldo Hernandez — Sun, 06 Oct 2024 23:33:28 +0000

It is very common on applications to have entities that are related under Many to Many relationship concept, for example, several users that could have several roles, and for this relationship type works without issues is mandatory to have third table to join the previous ones, commonly named as “join table” o “cross table”.

Mapping this type of relationship with Entity Framework Core is pretty easy and is well documented by Microsoft. If you are using code-first approach you are covered on almost all cases of database design, but for this post I wanted to share a special case that I was asked to face, here the explanation:

One company that has several years on the market wanted to map a very old database (created 15 years ago) to Entity Framework Model but they did not want to map the “join tables”, instead, they only wanted to map the main entities using navigations and set the join table in shadow mode.

Here an image with a basic relationship to illustrate the case:

So, taking the previous picture the requirement was map “users” and “roles” but not “m2m_users_roles”, there are a couple of reasons of why this could be a bad idea but they finally insisted of doing like that. So these were the initial conditions:

We are mapping a database that already exists to EFCore.
The tables and columns did not follow the naming conventions established by EFCore.
The join table (m2m_users_roles) was required to be a shadow table.

Solution

This solution only apply on EFCore 7 or newer

Actually the solution looks trivial but it was not easy to see since is very hidden in the documentation site.

First, let’s map the “users” and “roles” tables to their respective entities (I will use Attributes for simplicity):

namespace Example.Models
{
    [Table("users")]
    public class User
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }
        [Column("username")]
        public string UserName { get; set; }
        [Column("created_at")]
        public DateTime CreatedAt { get; set }

        public virtual ICollection<Role> Roles { get; set; }
    }

    [Table("roles")]
    public class Rol
    {
        [Key]
        [Column("id")]
        public int Id { get; set; }
        [Column("title")]
        public string Title { get; set; }
        [Column("created_at")]
        public DateTime CreatedAt { get; set; }

        public virtual ICollection<User> Users { get; set; }
    }
}

Now in the DbContext the entities should be linked this:

namespace Example.Context
{
    public class DbCtx : DbContext
    {
        public virtual DbSet<Rol> Roles { get; set; }
        public virtual DbSet<User> Users { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            // Here are configured the entities' settings

            // Choose one of the entities, do not care about which one,
            // We are going to config both in the same instruction
            modelBuilder.Entity<Rol>()
                .HasMany(e => e.Users)
                .WithMany(e => e.Roles)
                // After setting the navigation, config the shadow join table
                // specifying the table name and the columns with the FKs
                .UsingEntity<Dictionary<object, string>>(
                    "m2m_users_roles*",
                    l => l.HasOne<User>().WithMany().HasForeignKey("uid"),
                    r => r.HasOne<Rol>().WithMany().HasForeignKey("rid")
                );
        }
    }
}

The *Dictionary<object, string>* is the type used by EFCore to map join tables without entity, we are not supposed to use it but since they uses it why not us too?😜

Just be aware about this could change in the future, quoting the official docs:

Currently, EF Core uses Dictionary<string, object> to represent join entity instances for which no .NET class has been configured. However, to improve performance, a different type may be used in a future EF Core release. Do not depend on the join type being Dictionary<string, object> unless this has been explicitly configured.

Reference: https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many#many-to-many-with-named-join-table

DEV Community: Aldo Hernandez

Link Many To Many entities with shadow join-table using Entity Framework Core

Solution