LINQ.Include () properties from subtypes in TPH inheritance

Question

I am using table inheritance per hierarchy (TPH) in the Entity Framework. Now I'm looking to get a list - in this example - of departments, where departments can be a subtype. I would like the elements in the collection to include their own custom properties , not just the properties of the base model.

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 even tried to return an IEnumerable<object> and add a polymorphic method for each subtype that looks like this:

 public class Sports : Department { public Coach Coach {get; set;} public object Export() { return new { this.Id, this.DepartmentType, this.Coach } } } 

and then follow these steps:

 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" } } ] 

Answer 1

On this path, you can find the departments of finance and sports 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(); 

Answer 2

A way to get all sections in one list, which can be serialized in JSON,

 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 cannot use Union both lists directly. You must overlay one of them with an IEnumerable<Department> in order to be able to Union other. But...

• .AsEnumerable() : If you make only a throw, EF will conclude that he is dealing with Depeartment s and he will not accept Include Manager . By AsEnumerble , you do a subsequent flush in memory, and EF never knows about it.

I think this is a pretty bunch of flimsy code just for the sake of serialization.

A completely different option is to make sure that serialization occurs while the context is alive, so lazy loading starts to load navigation properties. In this case, you can simply serialize the Department and you will find all the properties of the derived types in JSON. Perhaps (if the actual model is more complex than what you are showing), you should prevent circular references.
If the Department number is not too large, I think this is a viable option, although it will generate a few requests for lazy loading.