Map Parent-Child in Hierarchy with Entity Framework

Question

We are using Entity Framework and We have one unique requirement, and after trying many possible options, I couldn't figure out how I can do that, below is the problem summary.

I have following Entity

public class SuperParent
{
     [Key]
     public int SupoerParentId {get;set;}
     public virtual ICollection<Parent> Intermediates {get;set;}
}

public class Parent
{
     [Key]
     public int ParentId {get;set;}
     [ForeignKey("SuperParentId")]
     public virtual SuperParent Ancestor {get;set;}
     public int SuperParentId {get;set;}
     public virtual ICollection<Child> Children {get;set;}
}

public class Child
{
     [Key]
     public int ChildId {get;set;}
     [ForeignKey("ParentId")]
     public virtual Parent Ancestor {get;set;}
     public int ParentId {get;set;}

     /// Area of guidance required here..............
     /// I just want to some what denormalize table and add SuperParentId also in
     /// Child and in Database. As most of time its child we query and its very
     /// efficient for us to directly query based on SuperParentId, I want to do 
     /// something like below:
     [ForeignKey("SuperParentId")]
     public virtual SuperParent Ancestor {get;set;}
     public int SuperParentId SuperAncestorId {get;set;}
}

We have 1:N:N relationship, and many times we just want to bypass Parent and from SuperParent to directly want to reach to Child... Currently multi level joins are having problem and our query are not efficient, we store large amount of data and each table has 20+ columns.

Questions:

1. Is it possible with EF? then how I can write modelBinder OnModelCreating to support this?
2. Any other alternative?

Answer 1

If you want to have that kind of design, then by default Child will have cascade delete from Parent and SuperParent, which is not allowed in sql.

public int SuperParentId { get; set; } // -> non nullable

If a foreign key on the dependent entity is not nullable, then Code First sets cascade delete on the relationship. Source

You can simply remove the default cascade delete from SuperParent.

modelBuilder.Entity<Child>()
   .HasRequired(c => c.Ancestor)
   .WithMany()
   .WillCascadeOnDelete(false);

If you have Child collection on SuperParent, mention it when calling WithMany.

public DbSet<Child> Children { get; set; }

Change above WithMany with

    .WithMany(sp => sp.Children)