EF Core 6 : many-to-many self-referencing entities

Question

I have an entity that can include other entities of the same type.

The entity looks like this:

public class PublicHolidayCalendar
{
    public Guid Id { get; set; }

    public virtual ICollection<PublicHolidayCalendarInclusion> IncludedCalendars { get; set; } = new HashSet<PublicHolidayCalendarInclusion>();

}

public class PublicHolidayCalendarInclusion
{
    public Guid IncludedInCalendarId { get; set; }

    public Guid IncludedCalendarId { get; set; }

    public virtual PublicHolidayCalendar IncludedInCalendar { get; set; }

    public virtual PublicHolidayCalendar IncludedCalendar { get; set; }
}

In the OnConfiguring of my DB context, I configure the relationship as follows:

modelBuilder.Entity<PublicHolidayCalendarInclusion>()
    .HasKey(x => new { x.IncludedCalendarId, x.IncludedInCalendarId });

modelBuilder.Entity<PublicHolidayCalendarInclusion>()
    .HasOne(x => x.IncludedCalendar)
    .WithMany(x => x.IncludedCalendars)
    .HasForeignKey(x => x.IncludedCalendarId)
    .OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity<PublicHolidayCalendarInclusion>()
    .ToTable("PublicHolidayCalendarInclusions");

If I now add an inclusion with this method:

public async Task AddIncludedCalendar(Guid id, Guid includedCalendarId)
{
    var calendar = await context.PublicHolidayCalendars
        .Include(x => x.IncludedCalendars)
        .FirstOrDefaultAsync(f => f.Id == id)
        .ConfigureAwait(false);
        
    calendar.IncludedCalendars.Add(new PublicHolidayCalendarInclusion
    {
        IncludedCalendarId = includedCalendarId, 
        IncludedInCalendarId = id, 
    });
    await context.SaveChangesAsync().ConfigureAwait(false);
}

Everything works out, but if I check the database, it now has an entry in the PublicHolidayCalendarInclusions table that has both IncludedInCalendarId and IncludedCalendarId properties set to the same value (the value of id in the AddIncludedCalendar method).

I've tried adding a second navigation property to PublicHolidayCalendar

public virtual ICollection<PublicHolidayCalendarInclusion> IncludedInCalendars { get; set; }

and configure the relationship as follows

modelBuilder.Entity<PublicHolidayCalendarInclusion>()
    .HasOne(x => x.IncludedInCalendar)
    .WithMany(x => x.IncludedInCalendars)
    .HasForeignKey(x => x.IncludedInCalendarId)
    .OnDelete(DeleteBehavior.Restrict);

but I get the same result. What am I missing?

Answer 1

If I refomulate the model like :

public class PublicHolidayCalendar
{
    public Guid Id { get; set; }
    public virtual ICollection<Relation> Childrend { get; set; } // IncludedCalendars
}

public class Relation
{
    public Guid ParentId { get; set; } // IncludedInCalendarId
    public Guid ChildId { get; set; } // IncludedCalendarId
    public virtual PublicHolidayCalendar Parent { get; set; } // IncludedInCalendar
    public virtual PublicHolidayCalendar Child { get; set; } // IncludedCalendar
}

Then the navigation definition is :

modelBuilder.Entity<Relation>() // a relation is
    .HasOne(x => x.Child)       // a child
    .WithMany(x => x.Childrend) // with children
    .HasForeignKey(x => x.ChildId)
    .OnDelete(DeleteBehavior.Restrict);

So, a child has children... Make more sense if parent has children, like :

modelBuilder.Entity<Relation>()
    .HasOne(x => x.Parent)
    .WithMany(x => x.Childrend)
    .HasForeignKey(x => x.ChildId)
    .OnDelete(DeleteBehavior.Restrict);

---

What IncludedInCalendarId and IncludedCalendarId has the value of the root id? The relation definition is :

modelBuilder.Entity<PublicHolidayCalendarInclusion>()
    .HasOne(x => x.IncludedCalendar)
    .WithMany(x => x.IncludedCalendars)
    .HasForeignKey(x => x.IncludedCalendarId)
    .OnDelete(DeleteBehavior.Restrict);

So when something is added IncludedCalendars, on SaveChange the relation properties (IncludedCalendar and IncludedCalendarId) are overrided with the calendar :

calendar.IncludedCalendars.Add(new PublicHolidayCalendarInclusion
{
    IncludedCalendarId = includedCalendarId, // SaveChange will override with calendar.Id, that equal id 
    IncludedInCalendarId = id,
});