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?

I'm not sure your `Relation` class is necessary because it describes only the relation and no other meaningful data. I would model it like this: `public virtual ICollection RelatedTickets { get; set; }`, and EF will implement the relation under the covers. — Mr Anderson, Sep 27 '16 at 15:41
@Mr Anderson it might have other data, with your solution I have no control over 'undercover' structures and FK names — Ice2burn, Sep 27 '16 at 17:02

score 18 · Accepted Answer · answered Sep 27 '16 at 15:29

18

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.

answered Sep 27 '16 at 15:29

Ivan Stoev

195,425
15
312
343

Actually i was thinking about it, but my attentions was on single many-to-many collection. I guess two one-to-many relations fits best in my scenario. Should take a break, thanks! – Ice2burn Sep 27 '16 at 15:37
I'm building something similar to this and having issues when trying to structure a LINQ query to access the data. Using the above data if I do something like: _context.Ticket.Include(i => i.TicketFrom).include(i => i.TicketTo) And then try to access the data like: Ticket.TicketFrom.Ticket.Title A lot of the time (but not always) Ticket.TicketFrom.Ticket is null. I'm not sure why. – nick Jun 07 '18 at 19:28
1

@nick When you `Include` a navigation property, the *inverse* navigation property is loaded automatically. But not the *other*, which you need to opt in with `ThenInclude`. In the above example, the query should be `db.Set().Include(e => e.RelatedTo).ThenInclude(e => e.TicketTo).Include(e => e.RelatedFrom).ThenInclude(e => e.TicketFrom)`. – Ivan Stoev Jun 07 '18 at 19:37
I'm having the same situation, but till with this solution, adding a migration always fails with the error : `Introducing FOREIGN KEY constraint 'FK_Relations_Tickets_ToId' on table 'Relations' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints. Could not create constraint or index. See previous errors.` – AymenDaoudi Jan 24 '19 at 18:32
1

@AymenDaoudi Something I forgot to mention. Such relationships always create the "multiple cascade paths" problem you are experiencing - not for all databases, but SqlServer for sure. So you need to turn the cascade delete off for at least one of the relationships and handle the deletion manually or via database (instead of delete) trigger. – Ivan Stoev Jan 24 '19 at 18:57
1

@IvanStoev : Thanks for answering. Is it also applicable for Update ? As the error show ? Would the `.OnDelete(DeleteBehavior.Restrict);` turn the cascade delete off ? – AymenDaoudi Jan 24 '19 at 19:12
1

@AymenDaoudi Correct (and that's the only way to do that). I usually do that for the second relationship FK (assuming the first is the "owner" and the second is "reference"), but that's arbitrary decision - any or both will do. And you are welcome :) – Ivan Stoev Jan 24 '19 at 19:16

Здравко Здравков · Answer 2 · 2019-01-28T12:35:20.087

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.

Please mention relevant part of an important link, in case the target site is unreachable or goes permanently offline.read this [How do I write a good answer?](https://stackoverflow.com/help/how-to-answer) — Lakmi, Jan 28 '19 at 11:38
This answer [my question](https://stackoverflow.com/questions/60608252/stack-overflow-when-adding-an-entity-with-multiple-one-to-one-relationship-with/60618796) as well. Thank you! — joacoleza, Mar 10 '20 at 13:38

score 3 · Answer 3 · edited Apr 19 '23 at 06:12

@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.TicketParent)
    .HasForeignKey(f => tt.TicketParentId);

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

Self Referencing Many-to-Many relations

3 Answers3

Linked