asp.net core - 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.