A many-to-many collection of the same object with bilateral relations

Question

Suppose I have a widget element and I want to track other widgets that are adjacent to each. If the first widget is adjacent to the second widget, then the inversion is also true, and the second is next to the first.

Ideally, I will have a single collection on the entity and can freely configure the entity for this kind of relationship.

public class Widget { // ... public virtual ICollection<Widget> Adjacent { get; set; } } 

However, when I try to do this ...

 modelBuilder.Entity<Widget> .HasMany(w => w.Adjacent) .WithMany(w => w.Adjacent); 

... Entity Framework does not like at all.

The adjoining navigation property declared in the Widget type cannot be inverse to itself.

Is there a way to set up an object that accomplishes this, or am I going to get stuck in creating navigation properties for parent / child elements or separate relationship containers?

Answer 1

You need to enter another collection inside the widget, something like.

 public virtual ICollection<Widget> AdjacentFrom { get; set; } public virtual ICollection<Widget> AdjacentTo { get; set; } 

By default, without the fluent-api configuration, this code will create the WidgetWidgets container WidgetWidgets in the database, which contains two columns Widget_Id and Widget_Id1 .

---

But you need to be consistent in order to use only one collection to create related relationships. If you use the AdjacentTo collection to create an adjacent relationship.

 widget1.AdjacentTo.Add(widget2); 

After saving widget1.AdjacentTo will be widget2 and widget2.AdjacentFrom will be widget1 .

 Widget_Id Widget_Id1 2 1 

But if you re-enter the AdjacentFrom collection to make an adjoining ratio.

 widget1.AdjacentFrom.Add(widget2); 

After saving widget1.AdjacentFrom and widget1.AdjacentTo will be widget2 . The same thing happens with widget2 .

 Widget_Id Widget_Id1 2 1 1 2 

A composite unique key cannot prevent the insertion of a second record because the second record is not considered a duplicate row. But there is a workaround, by adding a validation constraint, you can add this constraint to the migration.

 Sql("alter table WidgetWidgets add constraint CK_Duplicate_Widget check (Widget_Id > Widget_Id1)"); 

---

To select all the neighboring ones, you can add another collection, something like.

 [NotMapped] public ICollection<Widget> Adjacent { get { return (AdjacentFrom ?? new Widget[0]).Union((AdjacentTo ?? new Widget[0])).Distinct().ToArray(); } } 

---

After adding a check constraint, you can use this extension to add or remove contiguous ones.

 public static class WidgetDbExtension { public static void AddAdjacent(this Widget widget1, Widget widget2) { if (widget1.Id < widget2.Id) { widget1.AdjacentTo.Add(widget2); } else { widget2.AdjacentTo.Add(widget1); } } public static void RemoveAdjacent(this Widget widget1, Widget widget2) { if (widget1.Id < widget2.Id) { widget1.AdjacentTo.Remove(widget2); } else { widget2.AdjacentTo.Remove(widget1); } } }