EF6 Self Referencing Table with multiple parent properties, single child collection

Question

I have a table that references itself, but I am struggling with getting my desired mapping. I want to be able to define Children to be a collection of people that have a given person as Mother, Father, and/or Guardian. A Guardian may be the father or mother.

I am wanting to have a tree view of people that is browsable where people are listed; the user can expand a person's node to show all that person's children, regardless off the child-defining relationship (Mother, Father, or Guardian).

public partial class Person
{
    [Key]
    public int ID { get; set; }

    [StringLength(100)]
    public string Name { get; set; }


    public int? GuardianID { get; set; }

    [Column("MotherID")]
    public int? MotherID { get; set; }


    [Column("FatherID")]
    public int? FatherID { get; set; }

    [ForeignKey("MotherID")]
    public virtual tblPerson Mother { get; set; }

    [ForeignKey("FatherID")]
    public virtual tblPerson Father { get; set; }

    [ForeignKey("GuardianID")]
    public virtual tblPerson Guardian { get; set; }

    [InverseProperty("Guardian")]
    [InverseProperty("Father")]
    [InverseProperty("Mother")]
    public virtual IEnumerable<tblPerson> children { get; set; }
}

Any help would be appreciated right now my view has to look like this:

    @using Person_MVC.Models
    @model IEnumerable<Person>
    @{
        IEnumerable<Person> children;
    }

    <ul>
        @foreach (Person person in Model.OrderBy(p => p.PersonNumber))
        {
            <li id="[email protected]" data-jstree='{"type":"Person"}' data-Personkey="@Person.ID.ToString()">
                @Person.Name
                @{
            PersonModel db = new PersonModel();
            children = (from p in db.Persons where p.GuardianID == Person.ID || p.Father == Person.ID || p.MotherID == Person.ID select p).ToList();
                }
                @if (children != null && children.Count() > 0)
                {
                    @Html.Partial("PersonTree", children)
                }
            </li>
        }
    </ul>

Answer 1

I guess the better solution is to make three navigation lists in your model and may have one method to join the objects to return all sons to you.

e.g.

public int? FatherId { get; set; }

public int? GrandFatherId { get; set; }

public int? MotherId { get; set; }

public virtual ICollection<Person> FatherForThose { get; set; }
public virtual Person Father { get; set; }

public virtual ICollection<Person> GrandFatherForThose { get; set; }
public virtual Person GrandFather { get; set; }

public virtual ICollection<Person> MotherForThose { get; set; }
public virtual Person Mother { get; set; }


public ICollection<Person> GetChildren()
{
    var list = FatherForThose.Concat(MotherForThose).ToList();
    foreach (var person in GrandFatherForThose)
    {
        if (list.All(i => i.Id != person.Id))
        {
            list.Add(person);

        }
    }
    return list;
}

but you should always take care to include them in your querying e.g.

var grand = context.Persons.Include(x => x.FatherForThose)
            .Include(x => x.GrandFatherForThose)
            .Include(x => x.MotherForThose)
            .FirstOrDefault(x => x.Id == 2);

var list = grand.GetChildren();

Answer 2

1. All the table data should be in hand (If not we might have multiple calls to database).
2. Find list of all the Persons who doesn't have parents(i.e; no guardianid, motherid, parentid for a person) and start the partial with them.