Self Referencing Many-to-Many relations

asp.net-core entity-framework-core

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?

1
7
9/27/2016 4:03:23 PM

Accepted Answer

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.

6
9/27/2016 3:29:48 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow