Many-to-many self referencing relationship

Question

I am new in EF. And I ran into a problem with creation many-to-many self referencing relation. I've tried to use solution from: Entity Framework Core: many-to-many relationship with same entity

my entities :

public class WordEntity
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Json { get; set; }

    public virtual List<WordSinonymEntity> Sinonyms { get; set; }
}


public class WordSinonymEntity
{
    public long WordId { get; set; }
    public virtual WordEntity Word { get; set; }

    public long SinonymId { get; set; }
    public virtual WordEntity Sinonym { get; set; }
}

and next configuration:

 modelBuilder.Entity<WordSinonymEntity>()
     .HasOne(pt => pt.Sinonym)
     .WithMany(p => p.Sinonyms)
     .HasForeignKey(pt => pt.SinonymId);

modelBuilder.Entity<WordSinonymEntity>()
    .HasOne(pt => pt.Word)
    .WithMany(t => t.Sinonyms)
    .HasForeignKey(pt => pt.WordId);`

but it leads to next exception.

System.InvalidOperationException: 'Cannot create a relationship between 'WordEntity.Sinonyms' and 'WordSinonymEntity.Word', because there already is a relationship between 'WordEntity.Sinonyms' and 'WordSinonymEntity.Sinonym'. Navigation properties can only participate in a single relationship.'

Does anyone can help me or may be suggest some examples to learn ? Thanks.

score 43 · Accepted Answer · answered Mar 11 '18 at 10:46

43

The post you are following is definitely wrong.

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.

One way to resolve the issue is to add a second collection navigation property:

public class WordEntity
{
    public long Id { get; set; }
    public string Name { get; set; }
    public string Json { get; set; }

    public virtual List<WordSinonymEntity> Sinonyms { get; set; }
    public virtual List<WordSinonymEntity> SinonymOf { get; set; } // <--
}

and specify the associations via fluent API:

modelBuilder.Entity<WordSinonymEntity>()
     .HasOne(pt => pt.Sinonym)
     .WithMany(p => p.SinonymOf) // <--
     .HasForeignKey(pt => pt.SinonymId)
     .OnDelete(DeleteBehavior.Restrict); // see the note at the end

modelBuilder.Entity<WordSinonymEntity>()
    .HasOne(pt => pt.Word)
    .WithMany(t => t.Sinonyms)
    .HasForeignKey(pt => pt.WordId);

Another way is to leave the model as is, but map the WordSinonymEntity.Sinonym to unidirectional association (with refeference navigation property and no corresponding collection navigation property):

modelBuilder.Entity<WordSinonymEntity>()
     .HasOne(pt => pt.Sinonym)
     .WithMany() // <--
     .HasForeignKey(pt => pt.SinonymId)
     .OnDelete(DeleteBehavior.Restrict); // see the note at the end

modelBuilder.Entity<WordSinonymEntity>()
    .HasOne(pt => pt.Word)
    .WithMany(t => t.Sinonyms)
    .HasForeignKey(pt => pt.WordId);

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

Note that in both cases you have to turn the delete cascade off for at least one of the relationships and manually delete the related join entities before deleting the main entity, because self referencing relationships always introduce possible cycles or multiple cascade path issue, preventing the usage of cascade delete.

answered Mar 11 '18 at 10:46

Ivan Stoev

195,425
15
312
343

1

Thanks so much. I've tried both variants of solutions and both variants works perfect. I wasted many time for looking for answer. I had to make the question earlier on the stackoverflow. – butek Mar 11 '18 at 11:51
Hey Mr. Ivan, what if I really need the cascades, I have a similar approach and I am needing the ```onUpdate: ReferantialAction.Cascade```, could you please enlighten me? :) – Burak Aug 18 '20 at 14:50
1

@Burak Hehe, sure. But... Unfortunately EF Core does not support updating (modifying) keys, hence there is no fluent configuration for that. You have to modify manually the generated migration and then perform updates through raw SQL commands, because as mentioned in the beginning, EF Core won`t let you change the entity PK through context APIs. – Ivan Stoev Aug 18 '20 at 15:08
So can you please check this question... https://stackoverflow.com/questions/63472076/self-referencing-in-many-to-many-with-cascades – Burak Aug 18 '20 at 15:43
Pretty late to ask an additional question but I'm working on a similar problem. In this example, how would one return from WordEntity a list of all synonyms that it is a part of? Ie. the concatenated list of both Synonyms and SynonymsOf. My first thought was to simply add another property with a getter that returns exactly that (Synonyms.Concat(SynonymsOf)), but is there an EF Core approach? – Sad-EyedLadyoftheLowlands Oct 31 '21 at 23:41
1

@Sad-EyedLadyoftheLowlands No EF Core solution so far. You are expected to do manual `Concat` anywhere needed. If you add such property, make sure it is not treated as separate relationship. Also note that it cannot be used inside LINQ to Entities queries. – Ivan Stoev Nov 01 '21 at 03:23
This is tricky thanks for the clarification. I have two additional things to confirm: (1) I suppose this solution would work for the situation where there is a hierarchy ,and multiple levels of assignments (using the same business model) are necessary. (2) if it's one-to-many prelateship, then can use a nullable reference to a single object instead of a List . – DavidY Sep 19 '22 at 22:07
1

@DavidY Tree like hierarchies are usually modelled with *single* one-to-many self referencing relationship (same table), so they need one optional (nullable) reference navigation property and one collection navigation property, and pairing them with `Has` / With` fluent API (and turning cascade delete off because of the mentioned SqlServer problem with possible cycles or multiple cascade paths). Is that what you are asking for? If not, consider posting own question with your specific requirements, since the answer addresses the specific OP case with many-to-many. – Ivan Stoev Sep 20 '22 at 06:02

Many-to-many self referencing relationship

1 Answers1

Linked

Related