C# EF6: Two navigation properties of the same type

Question

Simply, in C# EF6, how do you map two navigation properties to the same table while keeping their result sets separate? In plain English, I have a class that I want two collections of in another class. In other words, I want two collections of the same type but with different elements. Unfortunately, EF6 seems to treat both collections the same and gives them both the same elements (every record in the table).

The best I found from dozens of StackOverflow answers was this, but it has the problem described. In this example, a Father has many Sons and many Daughters, and they each have the one Father. Ideally, both Sons and Daughters can be stored in the same table Child.

class Father
{
    [Key]
    public long Id { get; set; }

    public virtual ICollection<Child> Sons { get; set; }

    public virtual ICollection<Child> Daughters { get; set; }
}

class Child
{
    [Key]
    public long Id { get; set; }

    public long FatherId_1 { get; set; }  

    public Father Father_1 { get; set; }

    public long FatherId_2 { get; set; }  // One for each collection???

    public Father Father_2 { get; set; }
}

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Child>()
            .HasRequired(e => e.Father_1)
            .WithMany(e => e.Sons)
            .HasForeignKey(e => e.FatherId_1);
    modelBuilder.Entity<Child>()
            .HasRequired(e => e.Father_2)
            .WithMany(e => e.Daughters)
            .HasForeignKey(e => e.FatherId_2);
}

The problem with this is, when reading the data back from the Child table, it doesn't discriminate between Sons and Daughters. That is, the Sons collection will not only contain Sons but also Daughters, and so will the Daughters collection. I might have expected EF6 to try to use a discriminator column, but it doesn't.

Question: How do you map two navigation properties to the same table and still be able to read its records back into their corresponding navigation properties? Or, is the example correct, and my problem is elsewhere? Or, is this not possible, and they need to be mapped to their own tables (with identical schemas).

Answer 1

I am a little confused, with your explanation. But I found some code for what I understand:

For "It's one model "Child" that needs to be split into two collections" :

 modelBuilder.Entity<Child>()
.Map(m =>
  {
    m.Properties(t => new { t.Id /*other props*/ });
    m.ToTable("Sons");
  })
.Map(m =>
  {
    m.Properties(t => new { t.Id /*other props*/});
    m.ToTable("Daughters");
  });

Answer 2

I found a way to solve this problem by using a domain object backed by a state object. Basically, you use a state object to store the data the way EF likes, and your domain object is what exposes that data to the rest of your application. For example:

public class Father
{
    //--- Constructor ---

    internal Father(FatherState state)
    {
        State = state;
    }

    //--- Properties ---

    public long Id => State.Id;

    public IList<Child> Sons => Children.Where(child => child.Type == ChildType.Son).ToList().AsReadOnly();

    public IList<Child> Daughters => Children.Where(child => child.Type == ChildType.Daughter).ToList().AsReadOnly();

    //--- Methods ---

    public void AddChild(Child child)
    {
        State.Children.Add(child);
    }

    public void RemoveChild(Child child)
    {
        State.Children.Remove(child);
    }
}

internal class FatherState
{
    [Key]
    public long Id { get; set; }

    public virtual ICollection<Child> Children { get; set; }
}

public class Child
{
    [Key]
    public long Id { get; set; }

    public long FatherId { get; set; }  

    public Father Father { get; set; }

    public ChildType Type { get; set; }
}

public enum ChildType
{
    Son,
    Daughter
}

Of course, this can only be used with the repository pattern because it has to translate the FatherState object provided by EF into the Father object consumed by the application.

I think the real solution is to switch to a fully featured ORM like NHibernate. EF is far too underdeveloped, and that's only gotten worse with .NET Core. I don't even see how it's possible to do proper DDD with EF. A lot of developers must be compromising on their models. NHibernate has a lot of advantages, and the only caveat I've found is it doesn't provide async methods, but there's one or two arguments to made against it, and it can be done anyway. There's also forks of NHibernate that provide them as a last resort.

Answer 3

I think you don't require separate mappings altogether to bifurcate same FatherId for Son and daughter. The better solution is to make a unique identifier in Child Table like enum as previous answer suggests or add a Gender field in your Child model and keep only one mapping of Father model. (I don't know your exact requirements, but Father, Child can also be generalized in one super class human or person in strict Object Oriented terms).

But since I am not sure of your requirements, if you really want to continue with your mapping then the solution I propose is this.

  class Father
    {
        [Key]
        public long Id { get; set; }

        public int SonId{get;set;}
        public int DaughterId{get;set;}


        [ForeignKey("SonId")]
        public virtual Child Child_Son{get;set;}

        [ForeignKey("DaughterId")]
        public virtual Child Child_Son{get;set;}
    }

    class Child
    {
        [Key]
        public long Id { get; set; }

        public string Gender{get;set;}
    }

Explanation:

Two foreign keys each for Son and Daughter of same Child class in Father class will easily help you achieve separate collections using basic Linq or any sql query. Moreover, This preserves the rule "If parent exists only then child exists".

Answer 4

What I could see in your modeling is that, the child entity needs to have property fatherId and gender. Check the code below:

public class Child
{
   [Key]
   public long Id {get; set;}
   public string Name {get; set;}
   public Gender Gender{get; set;} //Usually use Enum containing Female and Male
   public long FatherId{get; set;}

   public virtual Father Father {get; set;}
}

public class Father
{
  public Father()
  {
     Children = new HashSet<Child>();
  }  

  [Key]
  public long Id {get; set;}
  public string Name {get; set;}

  public virtual ICollection<Child> Children{get; set;}
}

Getting child base on gender will work for what you want to do.