Support self-referencing many-to-many with a single navigation

[Tiberriver256]

Hello,

What would be the recommended method for self-referencing many to many relationships?

For example in an eCommerce type environment handling 'Related Products'

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 24b2b50e-35f8-9e0b-8ce5-840e1ea816e6
• Version Independent ID: 9e0e6e2c-48fa-76c3-3bd9-813a6df22645
• Content: Relationships - EF Core
• Content Source: entity-framework/core/modeling/relationships.md
• Product: entity-framework
• Technology: entity-framework-core
• GitHub Login: @AndriySvyryd
• Microsoft Alias: avickers

[AndriySvyryd]

The approach should be the same as with other many-to-many relationships. Have you run into any issues?

[Tiberriver256]

Yes, my scenario simplified is basically:

class Product {
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Product> RelatedProducts { get; set; }
}

My OnModelCreating override throws a stackoverflow exception at this statement:

 modelBuilder.Entity<Product>()
                .HasMany(p => p.RelatedProducts)
                .WithMany(p => p.RelatedProducts)

[AndriySvyryd]

I see. EF only allows a navigation to be used once in a relationship. It might seem artificial for your scenario, but two navigations are needed to establish a relationship.

What you are looking for is more akin to a grouping, which isn't supported via a navigation.

[bart-degreed]

In case it helps anyone, the example below shows how to map a self-referencing many-to-many relationship using EF Core 5 syntax. I've tried to keep the mapping as minimal as possible, using defaults where possible.

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace ManyToMany
{
    internal class Program
    {
        private static void Main()
        {
            using var dbContext = new AppDbContext();

            dbContext.Database.EnsureDeleted();
            dbContext.Database.EnsureCreated();

            var workItem1 = new WorkItem {Description = "W1"};
            var workItem2 = new WorkItem {Description = "W2"};
            var workItem3 = new WorkItem {Description = "W3"};

            workItem1.RelatedTo = new List<WorkItem> {workItem2, workItem3};
            workItem3.RelatedTo = new List<WorkItem> {workItem1, workItem2};

            dbContext.AddRange(workItem1, workItem2, workItem3);
            dbContext.SaveChanges();

            var workItems = dbContext.WorkItems.ToArray();

            foreach (var workItem in workItems)
            {
                Console.Write(workItem.Description);
                Console.Write(" => ");

                if (workItem.RelatedTo != null)
                {
                    Console.Write(string.Join(", ", workItem.RelatedTo.Select(x => x.Description)));
                }

                Console.Write(" <= ");

                if (workItem.RelatedFrom != null)
                {
                    Console.Write(string.Join(", ", workItem.RelatedFrom.Select(x => x.Description)));
                }

                Console.WriteLine();
            }
        }
    }

    public class AppDbContext : DbContext
    {
        public DbSet<WorkItem> WorkItems { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.LogTo(Console.WriteLine, LogLevel.Information);
            optionsBuilder.UseNpgsql(
                "Host=localhost;Port=5432;Database=ExampleDB;User ID=postgres;Password=postgres");
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            builder.Entity<WorkItem>()
                .HasMany(workItem => workItem.RelatedFrom)
                .WithMany(workItem => workItem.RelatedTo)
                .UsingEntity<WorkItemToWorkItem>(
                    right => right
                        .HasOne(joinEntity => joinEntity.FromItem)
                        .WithMany(),
                    left => left
                        .HasOne(joinEntity => joinEntity.ToItem)
                        .WithMany());
        }
    }

    public class WorkItem
    {
        public int Id { get; set; }
        public string Description { get; set; }

        public IList<WorkItem> RelatedFrom { get; set; }
        public IList<WorkItem> RelatedTo { get; set; }
    }

    public class WorkItemToWorkItem
    {
        public WorkItem FromItem { get; set; }
        public WorkItem ToItem { get; set; }
    }
}

Output:

info: 14/07/2021 14:06:23.899 CoreEventId.ContextInitialized[10403] (Microsoft.EntityFrameworkCore.Infrastructure)
      Entity Framework Core 5.0.7 initialized 'AppDbContext' using provider 'Npgsql.EntityFrameworkCore.PostgreSQL' with options: None
info: 14/07/2021 14:06:24.367 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      REVOKE CONNECT ON DATABASE "ExampleDB" FROM PUBLIC;
      SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE datname = 'ExampleDB';
info: 14/07/2021 14:06:24.403 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (35ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      DROP DATABASE "ExampleDB";
info: 14/07/2021 14:06:24.572 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (145ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE DATABASE "ExampleDB";
info: 14/07/2021 14:06:24.683 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (15ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "WorkItems" (
          "Id" integer GENERATED BY DEFAULT AS IDENTITY,
          "Description" text NULL,
          CONSTRAINT "PK_WorkItems" PRIMARY KEY ("Id")
      );
info: 14/07/2021 14:06:24.693 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (8ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE TABLE "WorkItemToWorkItem" (
          "FromItemId" integer NOT NULL,
          "ToItemId" integer NOT NULL,
          CONSTRAINT "PK_WorkItemToWorkItem" PRIMARY KEY ("FromItemId", "ToItemId"),
          CONSTRAINT "FK_WorkItemToWorkItem_WorkItems_FromItemId" FOREIGN KEY ("FromItemId") REFERENCES "WorkItems" ("Id") ON DELETE CASCADE,
          CONSTRAINT "FK_WorkItemToWorkItem_WorkItems_ToItemId" FOREIGN KEY ("ToItemId") REFERENCES "WorkItems" ("Id") ON DELETE CASCADE
      );
info: 14/07/2021 14:06:24.700 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      CREATE INDEX "IX_WorkItemToWorkItem_ToItemId" ON "WorkItemToWorkItem" ("ToItemId");
info: 14/07/2021 14:06:24.841 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (5ms) [Parameters=[@p0='?', @p1='?', @p2='?'], CommandType='Text', CommandTimeout='30']
      INSERT INTO "WorkItems" ("Description")
      VALUES (@p0)
      RETURNING "Id";
      INSERT INTO "WorkItems" ("Description")
      VALUES (@p1)
      RETURNING "Id";
      INSERT INTO "WorkItems" ("Description")
      VALUES (@p2)
      RETURNING "Id";
info: 14/07/2021 14:06:24.864 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (3ms) [Parameters=[@p3='?' (DbType = Int32), @p4='?' (DbType = Int32), @p5='?' (DbType = Int32), @p6='?' (DbType = Int32), @p7='?' (DbType = Int32), @p8='?' (DbType = Int32), @p9='?' (DbType = Int32), @p10='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
      INSERT INTO "WorkItemToWorkItem" ("FromItemId", "ToItemId")
      VALUES (@p3, @p4);
      INSERT INTO "WorkItemToWorkItem" ("FromItemId", "ToItemId")
      VALUES (@p5, @p6);
      INSERT INTO "WorkItemToWorkItem" ("FromItemId", "ToItemId")
      VALUES (@p7, @p8);
      INSERT INTO "WorkItemToWorkItem" ("FromItemId", "ToItemId")
      VALUES (@p9, @p10);
info: 14/07/2021 14:06:24.997 RelationalEventId.CommandExecuted[20101] (Microsoft.EntityFrameworkCore.Database.Command)
      Executed DbCommand (1ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT w."Id", w."Description"
      FROM "WorkItems" AS w

W1 => W2, W3 <= W3
W2 =>  <= W1, W3
W3 => W1, W2 <= W1

[AndriySvyryd]

We don't plan to support this fully in EF Core. The best workaround would be to Map the navigation to a query