LINQ .Include() properties from sub-types in TPH inheritance

Question

I am using table-per-hierarchy (TPH) inheritance in Entity Framework. Now I am looking to get a list of - in this example - Departments where departments can be a sub-type. I'd like the items in the collection to include their own custom properties and not only the Base Model's properties.

How can I achieve this?

public class Department
{
    public Department()
    {
        DepartmentType = this.GetType.Name;
    }
    public int Id {get; set;}
    public string DepartmentType {get; set;}
}

public class Finance : Department
{
    public virtual Manager Manager {get; set;}
}

public class Sports : Department
{
    public virtual Coach Coach {get; set;}
}


// This obviously crashes instantly
// How can I include Manager if type is Finance and Coach if type is Sports?
context.Departments
        .Include(c => (c is Finance) ? c.Manager : null)
        .Include(c => (c is Sports) ? c.Coach : null);

I have even tried to return IEnumerable<object> and add a polymorphic method to each sub-type that looked like this:

public class Sports : Department
{
    public Coach Coach {get; set;}

    public object Export()
    {
        return new 
        {
            this.Id,
            this.DepartmentType,
            this.Coach
        }
    }
}

and then do something like this:

context.Departments.Select(c => c.Export())

But that doesn't work either.

Desired JSON Usage

[
    { Id: 1, DepartmentType: "Finance", Manager: { Name: "John" } },
    { Id: 2, DepartmentType: "Finance", Manager: { Name: "Harold" } },
    { Id: 3, DepartmentType: "Sport", Coach: { Name: "Fred", SportType: "Soccer" } },
    { Id: 4, DepartmentType: "Finance", Manager: { Name: "Hank" } },
    { Id: 5, DepartmentType: "Sport", Coach: { Name: "Mark", SportType: "Football" } }
]

You want to be able to auto export only or do you need to import too? — Hogan, Dec 23 '14 at 15:55
then instead of `public object Export()` make a `public string NicePrint()` or overload ToString() — Hogan, Dec 23 '14 at 16:09
The main problem is that sub-type navigation properties are not eager loaded and I am getting only base-type properties — NaNerd, Dec 23 '14 at 16:14
this link could help you:http://weblogs.asp.net/manavi/inheritance-mapping-strategies-with-entity-framework-code-first-ctp5-part-1-table-per-hierarchy-tph — ocuenca, Dec 23 '14 at 16:18
did you "import" mean "include"? All I want is to load a list of Departments, BUT to keep the Sub-types & custom properties intact. I want to return that list to AngularJS as a JSON object as per my example. — NaNerd, Dec 23 '14 at 16:20

score 1 · Answer 1 · answered Dec 23 '14 at 16:48

At this way you can find the finance and sports departments and include their properties:

var financeDeparments = context.Departments.OfType<Finance>().Include(p => p.Manager).ToList();
var sportDepartments = context.Departments.OfType<Sports>().Include(p => p.Coach).ToList();

Gert Arnold · Answer 2 · 2014-12-25T12:17:29.793

A way to get all departments in one list that can be serialized into JSON is

var deparments = context.Departments.OfType<Finance>().Include(p => p.Manager)
                 .AsEnumerable()
                 .OfType<Department>()
                 .Union(
                 context.Departments.OfType<Sports>().Include(p => p.Coach)
                 ).ToList();

Explanation:

OfType<Department>(): You can't Union both lists directly. You have to cast one of them to IEnumerable<Department> to be able to Union the other. But...
.AsEnumerable(): If you only do the cast, EF will conclude it's dealing with Depeartments, and it won't accept the Include of the Manager. By including AsEnumerble you do the subsequent cast in memory and EF never knows about it.

I think this is quite a bunch of contrived code only for sake of serializing.

A totally different option is to make sure the serializing occurs while the context is alive, so lazy loading is triggered to populate the navigation properties. In that case, you could simply serialize Departments and you'll find all properties of derived types in the JSON. Maybe (if the actual model is more complex than what you show) you have to prevent circular references.
If the number of Department is not too large I think this is a viable option, even though it will generate a number of queries for lazy loading.

I can't test this out right now (dont think my gf will appreciate it if I start coding on christmas day), but the Union approach looks really interesting! I will let you know if this works out. — NaNerd, Dec 26 '14 at 14:07

LINQ .Include() properties from sub-types in TPH inheritance

Desired JSON Usage

2 Answers2

Linked