Mapping Parent and Children relationship collections to a single table in EF6

Question

I'm using EF6 code-first to try to map to a desired schema

The requirement is to have an organisation table. Each organisation can have zero to many parents and zero to many children

And to have a single relationship table which holds the parent and child relationships

So I'm hoping to have a POCO like this:

public class Organisation
{
    public Organisation()
    {
        Children = new Collection<Organisation>();
        Parents = new Collection<Organisation>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Organisation> Parents { get; set; }
    public virtual ICollection<Organisation> Children { get; set; }
}

and mapping like this:

public class OrganisationMap : EntityTypeConfiguration<Organisation>
{
    public OrganisationMap()
    {
        HasMany(n => n.Children)
            .WithMany()
            .Map(m => m.ToTable("OrganisationRelationship").MapLeftKey("ParentId").MapRightKey("ChildId"));
        HasMany(n => n.Parents)
            .WithMany()
            .Map(m => m.ToTable("OrganisationRelationship").MapLeftKey("ChildId").MapRightKey("ParentId"));

    }
}

but if I try to add a migration after setting that up I receive error message:

OrganisationOrganisation1: Name: The EntitySet 'OrganisationOrganisation1' with schema 'dbo' and table 'OrganisationRelationship' was already defined. Each EntitySet must refer to a unique schema and table.

Is there a way to achieve this goal?

---

For additional info. The reason I am holding the parent relationship is that when loading the data I need to find all nodes that have no parent and treat them as a collection of roots.

If there's a better way to do that than holding a parents collection I'd be totally happy!

If I add a migration with just the children collections then all works

---

Thanks for the input. I'm still trying to find out if it's possible to have one or more known roots to simplify building the graph but am interested if this mapping is possible...

---

The data sort of looks like this:

Answer 1

It's kind of hard to visualize an item having multiple parents at the same level.

Since you mentioned the word root in your question I think the ideal model would have a single parent and multiple children, now the parent of the item can have a parent, this way creating a hierarchy. You can implement the idea of an optional parent for root level elements.

If you want to implement the idea of multiple parents for a single item follow @Moho answer.
If you want hierarchy try the following:

public class Organisation
{
    public Organisation()
    {
        Children = new Collection<Organisation>();
    }

    public int Id { get; set; }
    public string Name { get; set; }

    public virtual Organisation Parent { get; set; }
    public virtual ICollection<Organisation> Children { get; set; }

    public IEnumerable<Organisation> Ancestors
    {
        get
        {
            var item = this;
            while (item.Parent != null)
            {
                yield return item.Parent;
                item = item.Parent;
            }
        }
    }
}

and your map:

public class OrganisationMap : EntityTypeConfiguration<Organisation>
{
    public OrganisationMap()
    {
        HasOptional(n => n.Parent)
            .WithMany(n => n.Children)
            .Map(m => m.MapKey("ParentId"));

        Ignore(n => n.Ancestors);
    }
}

UPDATE:
you can make all the parents be loaded from the DB in a single pull by calling Include. This loads the parent for every item that is pulled.

db.Organisations.Include(x => x.Parent).Include(x => x.Children).Where(...your condition)

Answer 2

You're defining the mappings twice - try only once, something similar to the below

public OrganisationMap()
{
    HasMany(n => n.Children)
        .WithMany( n => n.Parents )
        .Map(m =>
            m.ToTable("OrganisationRelationship")
            .MapLeftKey("ParentId")
            .MapRightKey("ChildId"));
}