You need to introduce another collection inside the widget, something like.
public virtual ICollection<Widget> AdjacentFrom { get; set; }
public virtual ICollection<Widget> AdjacentTo { get; set; }
By default with no fluent-api configuration, this code only will create a container table of WidgetWidgets
in the database that contains two columns Widget_Id
and Widget_Id1
.
But you need to be consistent to only use one of the collection to make an adjacent relationship. If you use AdjacentTo
collection to make an adjacent relationship.
widget1.AdjacentTo.Add(widget2);
After being saved widget1.AdjacentTo
will have widget2
and widget2.AdjacentFrom
will have widget1
.
Widget_Id Widget_Id1
2 1
But if you input again with AdjacentFrom
collection to make an adjacent relationship.
widget1.AdjacentFrom.Add(widget2);
After being saved widget1.AdjacentFrom
and widget1.AdjacentTo
will have widget2
. Same thing happens with widget2
.
Widget_Id Widget_Id1
2 1
1 2
Composite unique key can't prevent second record to be inserted, because the second record is not considered as duplicate row. But there is a workaround by adding a check constraint, you can add this constraint in the migration.
Sql("alter table WidgetWidgets add constraint CK_Duplicate_Widget check (Widget_Id > Widget_Id1)");
To select all adjacent 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 check constraint, then you can use this extension to add or remove adjacent.
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);
}
}
}