closure tables with entity framework 6

Question

I want to implement hierarchical data structure (e.g Product --> Product 2 ----> Product3, Product 2----> Product4) using entity framework 6 code first approach. There are several approaches available but i think closure table approach is one that can fulfill all of my requirements. Can some one guide me how to implement closure table approach in entity framework 6 efficiently or any other alternatives?

Answer 1

what you need is a many-to-many relationship with an entity itself: For instance:

public class SelfReferencingEntity
{
    public SelfReferencingEntity()
    {
        RelatedSelfReferencingEntitys = new HashSet<SelfReferencingEntity>();
        OtherRelatedSelfReferencingEntitys = new HashSet<SelfReferencingEntity>();
    }

    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]

    public int SelfReferencingEntityId { get; set; }

    public string Name { get; set; }

    public decimal Cost { get; set; }

    public virtual ICollection<SelfReferencingEntity> RelatedSelfReferencingEntitys { get; set; }

    public virtual ICollection<SelfReferencingEntity> OtherRelatedSelfReferencingEntitys { get; set; }
}

and you need to override OnModelCreating method of DbContext to support the many-many self referencing similar to the following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    base.OnModelCreating(modelBuilder);
    modelBuilder.Entity<SelfReferencingEntity>()
    .HasMany(p => p.RelatedSelfReferencingEntitys)
    .WithMany(p => p.OtherRelatedSelfReferencingEntitys)
    .Map(m =>
    {
        m.MapLeftKey("SelfReferencingEntityId");
        m.MapRightKey("RelatedSelfReferencingEntityId");
        m.ToTable("RelatedSelfReferencingEntity", "");
    });
}

There is a very nice complete example fully described in chapter 6.3 of the book Entity Framework 6 Recipes which addresses this issue and transitive relationship (relationship spanning multiple levels) and its code is available to download through the link I mentioned.