Beyond the Basics with Modeling and Inheritance - Entity Framework 6 Recipes - page 194

Figure 6-6. Self-referencing Category table

To create our model, do the following:

1. Create a new class in your project that inherits from DbContext.

2. Add a Category POCO entity class using the code in Listing 6-12.

Listing 6-12. Creating the Category POCO Entity Class
[Table("Category", Schema = "Chapter6")]
public class Category
{
[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int CategoryId { get; set; }
public string Name { get; set; }

public virtual Category ParentCategory { get; set; }
public virtual ICollection<Category> SubCategories { get; set; }
}

Add an auto-property of type DbSet<Category> to the DbContext subclass. 3.

Override the OnModelCreating method of DbContext in your subclass, as shown in
Listing 6-13. In the override, we will create the ParentCategory and SubCategories
associations and configure the foreign key constraint.

4.

Listing 6-13. Overriding OnModelCreating in the DbContext Subclass

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);

modelBuilder.Entity<Category>()
.HasOptional(c => c.ParentCategory)
.WithMany(c => c.SubCategories)
.Map(m => m.MapKey("ParentCategoryId"));
}

In our model, the Category entity has a Subcategories navigation property that we can use to get the collection
of all of the immediate subcategories of the Category. However, to access them, we need to load them explicitly using
either the Load() or the Include() methods. The Load() method requires an additional round trip to the database,
while the Include() method provides only a predefined, limited depth.
We want to bring the entire hierarchy into the object graph as efficiently as possible. To do this, we use a
Common Table Expression in a stored procedure.