Entity Framework Core：TPH模式下如何单查询Include所有关联对象？

Absolutely doable! This is actually one of the advantages of the Table Per Hierarchy (TPH) pattern in Entity Framework Core—since all your user types (Student, Teacher) are stored in a single underlying table, you can query them all in one go while eagerly loading all their associated entities with the right combination of Include and ThenInclude (along with type-specific navigation handling).

Here's how to implement it: #

First, let's assume your basic model setup looks something like this:

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    // Common properties for all users
    public UserProfile Profile { get; set; }
}

public class Student : User
{
    public int StudentNumber { get; set; }
    public ICollection<Enrollment> Enrollments { get; set; }
}

public class Teacher : User
{
    public string EmployeeId { get; set; }
    public ICollection<Course> Courses { get; set; }
}

// Related entities
public class UserProfile { /* Profile properties here */ }
public class Enrollment { public Course Course { get; set; } /* Enrollment details */ }
public class Course { public Department Department { get; set; } /* Course details */ }

And your TPH configuration in OnModelCreating:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Configure TPH discriminator column
    modelBuilder.Entity<User>()
        .HasDiscriminator<string>("UserType")
        .HasValue<Student>("Student")
        .HasValue<Teacher>("Teacher");

    // Configure common relationships
    modelBuilder.Entity<User>()
        .HasOne(u => u.Profile)
        .WithOne()
        .HasForeignKey<User>(u => u.Id);

    // Configure Student-specific relationships
    modelBuilder.Entity<Student>()
        .HasMany(s => s.Enrollments)
        .WithOne(e => e.Student)
        .HasForeignKey(e => e.StudentId);

    // Configure Teacher-specific relationships
    modelBuilder.Entity<Teacher>()
        .HasMany(t => t.Courses)
        .WithOne(c => c.Teacher)
        .HasForeignKey(c => c.TeacherId);
}

The single query to fetch all users with all relations: #

For EF Core 5.0 and above, you can use conditional Include with When to target subtype-specific navigation properties cleanly:

var allUsersWithRelations = await _context.Users
    // Include relations shared by all users first
    .Include(u => u.Profile)
    // Include Student-only related data
    .When(u => u is Student, q => q.Include(s => s.Enrollments)
        .ThenInclude(e => e.Course))
    // Include Teacher-only related data
    .When(u => u is Teacher, q => q.Include(t => t.Courses)
        .ThenInclude(c => c.Department))
    .ToListAsync();

If you're working with an older EF Core version, you can cast the user to the subtype directly in the Include:

var allUsersWithRelations = await _context.Users
    .Include(u => u.Profile)
    .Include(u => (u as Student).Enrollments)
        .ThenInclude(e => e.Course)
    .Include(u => (u as Teacher).Courses)
        .ThenInclude(c => c.Department)
    .ToListAsync();

What happens under the hood? #

EF Core will generate a single SQL query with multiple LEFT JOIN clauses (one for each related entity set) to pull all the required data in one database round-trip. This avoids the N+1 query problem and is efficient for fetching hierarchical data with TPH.

Key notes: #

• Ensure all navigation properties are properly configured in your model and OnModelCreating to avoid loading issues.
• If you only need specific subtypes later, you can filter with OfType<Student>() or OfType<Teacher>(), but since you want all users, you can skip these filters.

内容的提问来源于stack exchange，提问作者Shaddix