Many-to-many collection of same entity, with two-way relationship

.net c# entity-framework entity-framework-6

Question

Let's say I want to keep track of widget entities that are close to one another. The inverse is likewise true—the second is adjacent to the first—if the first widget and the second widget are next to one another.

In a perfect world, I would just need one collection for the entity and be able to configure it quickly for this kind of interaction.

public class Widget
{
    // ...

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

Nevertheless, when I do that...

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

Entity Framework is not at all fond of it.

The navigation property 'Adjacent' declared on type 'Widget' cannot be the inverse of itself.

Is it possible to set up the entity in a way that accomplishes this, or will I have to create distinct relationship containers or parent/child collection navigation attributes instead?

1
6
8/12/2014 7:08:22 PM

Accepted Answer

You should add something similar to another collection inside the widget.

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

This code will only produce a container table of data by default if fluent-api configuration is not made.WidgetWidgets there are two columns in the database.Widget_Id and Widget_Id1 .


The collection should only be used once to create an adjacent relationship, thus you must be consistent. If you employAdjacentTo collection to create a close connection.

widget1.AdjacentTo.Add(widget2);

Having been rescuedwidget1.AdjacentTo should havewidget2 and widget2.AdjacentFrom should havewidget1 .

Widget_Id   Widget_Id1
    2           1

However, if you enter again withAdjacentFrom collection to create a close connection.

widget1.AdjacentFrom.Add(widget2);

Having been rescuedwidget1.AdjacentFrom and widget1.AdjacentTo should havewidget2 The same thing occurs whenwidget2 .

Widget_Id   Widget_Id1
    2           1
    1           2

Because the second record is not regarded as a duplicate row, the composite unique key cannot stop an insert of a second record. However, there is a fix that involves introducing a check constraint, which you may do in the migration.

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

You can add another collection, e.g., to choose everything adjacent.

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

You can add or delete nearby using this extension after applying the check constraint.

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);
        }
    }
}
8
8/13/2014 1:06:37 PM


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