Self Referencing Many-to-Many relations

Question

I have an Ticket entity:

    public class Ticket
    { 
        public int Id { get; set; }
        public string Title { get; set; }

        public virtual ICollection<Relation> RelatedTickets { get; set; }
    }

I want to setup many-to-many self-relations in Entity Framework Core, so i made two one-to-many relations:

public class Relation
{
    [Required, ForeignKey("TicketFrom")]
    public int FromId { get; set; }

    [Required, ForeignKey("TicketTo")]
    public int ToId { get; set; }

    public virtual Ticket TicketFrom { get; set; }
    public virtual Ticket TicketTo { get; set; }
}

I've tried to create the relationship using fluent API:

        builder.Entity<Relation>()
               .HasKey(uc => new { uc.FromId, uc.ToId });
        builder.Entity<Relation>()
           .HasOne(c => c.TicketFrom)
           .WithMany(p => p.RelatedTickets)
           .HasForeignKey(pc => pc.FromId);
        builder.Entity<Relation>()
           .HasOne(c => c.TicketTo)
           .WithMany(p => p.RelatedTickets)
           .HasForeignKey(pc => pc.ToId);

But in result i have an error:

Cannot create a relationship between 'Ticket.RelatedTickets' and 'Relation.TicketTo', because there already is a relationship between 'Ticket.RelatedTickets' and 'Relation.TicketForm'. Navigation properties can only participate in a single relationship.

The possible solution is to add Parent relation directly to TicketEntity:

public class Ticket
{ 
    public int Id { get; set; }

    [Required, ForeignKey("ParentRelation")]
    public Nullable<int> ParentRelationId { get; set; }

    public virtual Ticket ParentRelation {get;set;}

    public virtual ICollection<Ticket> RelatedTickets { get; set; }
    ...
}

With fluent api like this:

modelBuilder.Entity<Ticket> =>
{
    entity
        .HasMany(e => e.RelatedTickets)
        .WithOne(e => e.ParentRelation) 
        .HasForeignKey(e => e.ParentRelationId );
});

But it looks 'dirty' to store parent relation like this.
What is the right approach?

Answer 1

It's not possible to have just one collection with relations. You need two - one with relations the ticket equals TicketFrom and second with relations the ticket equals TicketTo.

Something like this:

Model:

public class Ticket
{ 
    public int Id { get; set; }
    public string Title { get; set; }

    public virtual ICollection<Relation> RelatedTo { get; set; }
    public virtual ICollection<Relation> RelatedFrom { get; set; }
}

public class Relation
{
    public int FromId { get; set; }
    public int ToId { get; set; }

    public virtual Ticket TicketFrom { get; set; }
    public virtual Ticket TicketTo { get; set; }
}

Configuration:

modelBuilder.Entity<Relation>()
    .HasKey(e => new { e.FromId, e.ToId });

modelBuilder.Entity<Relation>()
    .HasOne(e => e.TicketFrom)
    .WithMany(e => e.RelatedTo)
    .HasForeignKey(e => e.FromId);

modelBuilder.Entity<Relation>()
    .HasOne(e => e.TicketTo)
    .WithMany(e => e.RelatedFrom)
    .HasForeignKey(e => e.ToId);

Note that a solution using Parent is not equivalent, because it would create one-to-many association, while if I understand correctly you are seeking for many-to-many.

Answer 2

Here is very good explanation how to make many-to-many relationship in EF Core Many-to-many self referencing relationship

Every collection or reference navigation property can only be a part of a single relationship. While many to many relationship with explicit join entity is implemented with two one to many relationships. The join entity contains two reference navigation properties, but the main entity has only single collection navigation property, which has to be associated with one of them, but not with both.

   builder.Entity<Relation>()
           .HasKey(uc => new { uc.FromId, uc.ToId });

    builder.Entity<Relation>()
       .HasOne(c => c.TicketFrom)
       .WithMany() // <-- one of this must be empty
       .HasForeignKey(pc => pc.FromId)
       .OnDelete(DeleteBehavior.Restrict);

    builder.Entity<Relation>()
       .HasOne(c => c.TicketTo)
       .WithMany(p => p.RelatedTickets)
       .HasForeignKey(pc => pc.ToId);

Just make sure that WithMany exactly matches the presence/absence of the corresponding navigation property.

Note that you have to turn the delete cascade off.

Answer 3

@IvanStoev is correct. This is an example of a more general self referencing many to many relationship with many parents and many children.

public class Ticket
{
    [Key]
    public int Id { get; set; }
    public string Title { get; set; }
 
    public List<TicketTicket> TicketChildren { get; set; }
    public List<TicketTicket> TicketParents { get; set; }
}

public class TicketTicket
{
    public int TicketChildId { get; set; }

    public Ticket TicketChild { get; set; }
    
    public int TicketParentId { get; set; }
        
    public Ticket TicketParent { get; set; }
}

modelBuilder.Entity<TicketTicket>()
    .HasKey(tt => new {tt.TicketChildId, tt.TicketParentId});

modelBuilder.Entity<Ticket>()
    .HasMany(t => t.TicketChildren)
    .WithOne(tt => tt.ProductParent)
    .HasForeignKey(f => tt.ProductParentId);

modelBuilder.Entity<Ticket>()
    .HasMany(t => t.TicketParents)
    .WithOne(tt => tt.TicketChild)
    .HasForeignKey(tt => tt.TicketChildId);