Include Relations on Table per Hierarchy (TPH)

[IgorWolbers]

Feature Request

Add the ability to Include the relationships of properties on a collection where the collection type is of a Table per Hierarchy design and the relationships are defined on the derived types with lazy loading disabled.

The OfType extension could be used to specify the derived type so the relations on that type could be specified.

There is a similar request already for Entity Framework Core - 3910.

Example:

var materialized = dbContext.ParentDbSet.Where(x => optionalCondition)
  .Include(x => x.AbstractTypeEntitRelations.OfType<DerivedType>().Select(y => y.RelationsOnDerivedType))
  .SingleOrDefault();

This is currently not possible.

Use Case

This should avoid having to declare the relationships on the base type or having to execute multiple DB calls to retrieve the relationships.

Complete MCVE

Structure

• A school in a University has Persons
• A Person is either Student or Teacher
• Student has zero or more course books on loan
• Teacher can be assigned as the care taker / guardian for one or more campus buildings

Code

SchoolService.cs

public sealed class SchoolService
{
    private readonly UniversityDbContext _dbContext;
    public SchoolService(UniversityDbContext dbContext)
    {
        _dbContext = dbContext;
    }

     // This is the code of interest for the feature request
    public School GetSchoolAndPeopleWithDetails(int schoolId)
    {
        return _dbContext.Schools.Where(x => x.SchoolId == schoolId)
            .Include(x => x.EntitiesOfSchool.OfType<Teacher>().Select(y => y.Buildings))
            .Include(x => x.EntitiesOfSchool.OfType<Student>().Select(y => y.BooksOnLoan))
            .SingleOrDefault();
    }
}

Entities.cs

public sealed class School
{
    public int SchoolId { get; set; }
    public string Name { get; set; }
    public List<Person> EntitiesOfSchool { get; set; }
}

public abstract class Person
{
    public int PersonId { get; set; }
    public string Name { get; set; }
    public School PrimarySchool { get; set; }
    public int SchoolId { get; set; }
}

public sealed class Teacher : Person
{
    public ICollection<Building> Buildings { get; set; }
}

public sealed class Student : Person
{
    public ICollection<CourseBook> BooksOnLoan { get; set; }
}

public sealed class Building
{
    public int BuildingId { get; set; }
    public string Name { get; set; }
    public Teacher AssignedGuardian { get; set; }
    public int GuardianId { get; set; }
}

public sealed class CourseBook
{
    public int CourseBookId { get; set; }
    public int BookNumber { get; set; }
    public Student AssignedTo { get; set; }
    public int? AssignedToId { get; set; }
}

EntityMappings.cs does not include Building or CourseBook mapping types as they are not relevant

public sealed class SchoolMap : EntityTypeConfiguration<School>
{
    public SchoolMap()
    {
        ToTable("Schools");
        HasKey(x => x.SchoolId);
        Property(x => x.SchoolId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.Name).IsRequired().IsUnicode(true).HasMaxLength(200);
        HasMany(x => x.EntitiesOfSchool).WithRequired(x => x.PrimarySchool).HasForeignKey(person => person.SchoolId);
    }
}

public sealed class PersonMap : EntityTypeConfiguration<Person>
{
    public PersonMap()
    {
        ToTable("Persons");
        HasKey(x => x.PersonId);
        Property(x => x.PersonId).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        Property(x => x.Name).IsRequired().IsUnicode(true).HasMaxLength(256);
        Map<Teacher>(configuration => configuration.Requires("PersonType").HasValue(1));
        Map<Student>(configuration => configuration.Requires("PersonType").HasValue(2));
    }
}

public sealed class TeacherMap : EntityTypeConfiguration<Teacher>
{
    public TeacherMap()
    {
        HasMany(x => x.Buildings).WithRequired(x => x.AssignedGuardian).HasForeignKey(x => x.GuardianId);
    }
}
public sealed class StudentMap : EntityTypeConfiguration<Student>
{
    public StudentMap()
    {
        HasMany(x => x.BooksOnLoan).WithOptional(x => x.AssignedTo).HasForeignKey(x => x.AssignedToId);
    }
}

UniversityDbContext.cs

public sealed class UniversityDbContext : DbContext
{
    public UniversityDbContext() : base("Name=default")
    {
        this.Configuration.LazyLoadingEnabled = false; // disable lazy loading
    }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Configurations.Add(new SchoolMap());
        modelBuilder.Configurations.Add(new PersonMap());
        modelBuilder.Configurations.Add(new TeacherMap());
        modelBuilder.Configurations.Add(new StudentMap());
    }

    public DbSet<School> Schools { get; set; }
}

Sql code for table DDE and seed of data.sql

CREATE TABLE [dbo].[Schools](
	[SchoolId] [int] IDENTITY(1,1) NOT NULL,
	[Name] Nvarchar (200) not null
CONSTRAINT [PK_Schools] PRIMARY KEY CLUSTERED 
(
	[SchoolId] ASC
)) ON [PRIMARY]
GO


CREATE TABLE [dbo].[Persons](
	[PersonId] [int] IDENTITY(1,1) NOT NULL,
	[SchoolId] [int] not null,
	[PersonType] [int] not null,
	[Name] nvarchar(256) not null
CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED 
(
	[PersonId] ASC
)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Buildings](
	[BuildingId] [int] IDENTITY(1,1) NOT NULL,
	[Name] nvarchar(200) not null,
	[GuardianId] [int] not null,
CONSTRAINT [PK_Buildings] PRIMARY KEY CLUSTERED 
(
	[BuildingId] ASC
)) ON [PRIMARY]
GO

CREATE TABLE [dbo].[CourseBooks](
	[CourseBookId] [int] IDENTITY(1,1) NOT NULL,
	[BookNumber] varchar(200) not null,
	[AssignedToId] [int] null,
CONSTRAINT [PK_CourseBooks] PRIMARY KEY CLUSTERED 
(
	[CourseBookId] ASC
)) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Persons] WITH CHECK ADD CONSTRAINT [FK_Schools_Persons] FOREIGN KEY([SchoolId])
REFERENCES [dbo].[Schools] ([SchoolId])

ALTER TABLE [dbo].[Buildings] WITH CHECK ADD CONSTRAINT [FK_Persons_Buildings] FOREIGN KEY([GuardianId])
REFERENCES [dbo].[Persons] ([PersonId])

ALTER TABLE [dbo].[CourseBooks] WITH CHECK ADD CONSTRAINT [FK_Persons_CourseBooks] FOREIGN KEY([AssignedToId])
REFERENCES [dbo].[Persons] ([PersonId])



INSERT INTO Schools (Name) values (N'Business'), (N'Education')
INSERT INTO Persons (Name, PersonType, SchoolId) values ('Eddy',1, 1), ('Fran',1, 1), ('Joe',2, 1), ('Kim',2, 1)
INSERT INTO Buildings (Name, GuardianId) values (N'Offsite staff', 1), (N'Main Business Building', 1)
INSERT INTO CourseBooks(AssignedToId, BookNumber) values (3, 'Course A book 1'),(3, 'Course C book 31')

[ajcvickers]

EF Team Triage: This issue is not something that our team is planning to address in the EF6.x code base. This does not mean that we would not consider a community contribution to address this issue.

Moving forwards, our team will be fixing bugs, implementing small improvements, and accepting community contributions to the EF6.x code base. Larger feature work and innovation will happen in the EF Core code base (https://github.com/aspnet/EntityFramework).

Closing an issue in the EF6.x project does not exclude us addressing it in EF Core. In fact, a number of popular feature requests for EF have already been implemented in EF Core (alternate keys, batching in SaveChanges, etc.).

BTW this is a canned response and may have info or details that do not directly apply to this particular issue. While we'd like to spend the time to uniquely address every incoming issue, we get a lot traffic on the EF projects and that is not practical. To ensure we maximize the time we have to work on fixing bugs, implementing new features, etc. we use canned responses for common triage decisions.