Database Reference
In-Depth Information
context.Categories.Add(novel);
context.Categories.Add(history);
context.SaveChanges();
}
using (var context = new EF6RecipesContext())
{
var root = context.Categories.Where(o => o.Name == "Books").First();
Console.WriteLine("Parent category is {0}, subcategories are:", root.Name);
foreach (var sub in context.GetSubCategories(root.CategoryId))
{
Console.WriteLine("\t{0}", sub.Name);
}
}
The output from the code in Listing 6-16 is as follows:
Parent category is Books, subcategories are:
Fiction
Non-Fiction
History
Novel
How It Works
Entity Framework supports self-referencing associations, as we have seen in Recipe 6.2 and Recipe 6.3. In these
recipes, we directly loaded the entity references and collections using the Load() method. We cautioned, however,
that each Load() results in a round trip to the database to retrieve an entity or entity collection. For larger object
graphs, this database traffic may consume too many resources.
In this recipe, we demonstrated a slightly different approach. Rather than explicitly using Load() to materialize
each entity or entity collection, we pushed the work off to the storage layer by using a stored procedure to enumerate
recursively all of the subcategories and return the collection. We used a Common Table Expression in our stored
procedure to implement the recursive query. In our example, we chose to enumerate all of the subcategories. You
could, of course, modify the stored procedure to enumerate elements of the hierarchy selectively.
To use our stored procedure, we added a method to our DbContext subclass that calls the stored procedure
through DbContext.Database.SqlQuery<T>() and called the method within our code. We use the SqlQuery<T>()
method rather than the ExecuteSqlCommand() method because our stored procedure returns a result set.
6-6. Mapping Null Conditions in Derived Entities
Problem
You have a column in a table that allows null. You want to create a model using Table per Hierarchy inheritance with
one derived type representing instances in which the column has a value and another derived type representing
instances in which the column is null.
 
Search WWH ::




Custom Search