EF Core Many to Many relationship with join table and multiple referenced tables

Question

I have a following DB schema which can't be changed:

MainTable
- Id
- ...

JoinTable
- MainTableId
- RefTableId (points to a record in one of the ref tables)
- RefTableName (decides which table to do join with)

RefTableA
- Id
- ...

RefTableB
- Id
- ...

This way I have many-to-many relationship between MainTable and any of the referenced tables.

I have searched as much as I could and tried a different approaches (including TPC configuration and discriminators) to map this relationship in EF Core but nothing has worked so far. Is it even possible? Any help?

Answer 1

EF requires Foreign Keys, and you cannot have a Foreign Key on JoinTable.RefTableId. But when mapping to an existing database you can make this work. Just tell EF there's a Foreign Key when there's not really one there, and use TPH inheritance and introduce JoinTableA, JoinTableB, linking entities, like this:

using Microsoft.EntityFrameworkCore;

using var db= new Db();
//db.Database.EnsureDeleted();
//db.Database.EnsureCreated();

var m = new MainTable();
m.Name = "MainTable";
m.RefTableAs.Add(new RefTableA());
m.RefTableAs.Add(new RefTableA());
m.RefTableAs.Add(new RefTableA());

m.RefTableBs.Add(new RefTableB());
m.RefTableBs.Add(new RefTableB());

db.Add(m);

db.SaveChanges();

class Db : DbContext
{ 

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"Server=.;Database=EfTest;Trusted_Connection=True;TrustServerCertificate=true").LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<MainTable>();
        modelBuilder.Entity<JoinTable>()
                    .ToTable("JoinTable")
                    .HasKey(e => new { e.MainTableId, e.RefTableId, e.RefTableName });

        modelBuilder.Entity<JoinTable>().HasDiscriminator(e => e.RefTableName);

        modelBuilder.Entity<JoinTableA>()
            .ToTable("JoinTable")
            .HasDiscriminator()
            .HasValue("JoinTableA");

        modelBuilder.Entity<JoinTableB>()
            .ToTable("JoinTable")
            .HasDiscriminator()
            .HasValue("JoinTableB");

        modelBuilder.Entity<MainTable>()
            .HasMany(e => e.RefTableAs)
            .WithMany(e => e.MainTables)
            .UsingEntity<JoinTableA>(JoinTableA =>
            {
                JoinTableA.HasOne(e => e.RefTableA)
                    .WithMany()
                    .HasForeignKey(e => e.RefTableId);
                JoinTableA.HasOne(e => e.MainTable)
                    .WithMany()
                    .HasForeignKey(e => e.MainTableId);
            });

       modelBuilder.Entity<MainTable>()
            .HasMany(e => e.RefTableBs)
            .WithMany(e => e.MainTables)
            .UsingEntity<JoinTableB>(JoinTableB =>
            {
                JoinTableB.HasOne(e => e.RefTableB)
                    .WithMany()
                    .HasForeignKey(e => e.RefTableId);
                JoinTableB.HasOne(e => e.MainTable)
                    .WithMany()
                    .HasForeignKey(e => e.MainTableId);
            });


    }
}

public class MainTable
{
    public int Id { get; set; }
    public string? Name { get; set; }

    public ICollection<RefTableA> RefTableAs { get; set; } = new List<RefTableA>();
    public ICollection<RefTableB> RefTableBs { get; set; } = new List<RefTableB>();



}
public class RefTableA
{
    public int Id { get; set; }
    public ICollection<MainTable> MainTables { get; set; } = new List<MainTable>();
}


public class RefTableB
{
    public int Id { get; set; }
    public ICollection<MainTable> MainTables { get; set; } = new List<MainTable>();
}


public class JoinTable
{
    public int MainTableId { get; set; }
    public int RefTableId { get; set; }
    public string? RefTableName { get; set; }
}

public class JoinTableA : JoinTable
{
    public RefTableA RefTableA {get; set; }
    public MainTable MainTable { get; set; }

}

public class JoinTableB: JoinTable
{
    public RefTableB RefTableB  {get; set; }
    public MainTable MainTable { get; set; }


}