1
Entity Framework inheritance modelling, TPH v TPT v TPC with EF 8 on .NET 8
Date Added (UTC):
14 Apr 2024 @ 22:02
Date Updated (UTC):14 Apr 2024 @ 22:02
.NET Version(s): Tag(s):
#DBOperations #EntityFramework
Added By:
.NET Developer and tech lead from Ireland!
Benchmark Results:
Benchmark Code:
using System;
using System.Collections.Generic;
using System.Linq;
using BenchmarkDotNet.Attributes;
using Microsoft.EntityFrameworkCore;
[MemoryDiagnoser]
public class Inheritance
{
[Params(5000)]
public int RowsPerEntityType { get; set; }
[GlobalSetup(Target = nameof(TPH))]
public void SetupTPH()
{
Console.WriteLine("Setting up database...");
using var context = new TPHContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.SeedData(RowsPerEntityType);
Console.WriteLine("Setup complete.");
}
[GlobalSetup(Target = nameof(TPT))]
public void SetupTPT()
{
Console.WriteLine("Setting up database...");
using var context = new TPTContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.SeedData(RowsPerEntityType);
Console.WriteLine("Setup complete.");
}
[GlobalSetup(Target = nameof(TPC))]
public void SetupTPC()
{
Console.WriteLine("Setting up database...");
using var context = new TPCContext();
context.Database.EnsureDeleted();
context.Database.EnsureCreated();
context.SeedData(RowsPerEntityType);
Console.WriteLine("Setup complete.");
}
[Benchmark]
public List<Root> TPH()
{
using var context = new TPHContext();
return context.Roots.ToList();
}
[Benchmark]
public List<Root> TPT()
{
using var context = new TPTContext();
return context.Roots.ToList();
}
[Benchmark]
public List<Root> TPC()
{
using var context = new TPCContext();
return context.Roots.ToList();
}
public abstract class InheritanceContext : DbContext
{
public DbSet<Root> Roots { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder.UseSqlServer(
@"Server=localhost;Database=efsamples;Trusted_Connection=True;TrustServerCertificate=true");
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Child1>();
modelBuilder.Entity<Child1A>();
modelBuilder.Entity<Child1B>();
modelBuilder.Entity<Child2>();
modelBuilder.Entity<Child2A>();
modelBuilder.Entity<Child2B>();
}
public void SeedData(int rowsPerEntityType)
{
Set<Root>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Root { RootProperty = i }));
Set<Child1>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Child1 { Child1Property = i }));
Set<Child1A>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Child1A { Child1AProperty = i }));
Set<Child1B>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Child1B { Child1BProperty = i }));
Set<Child2>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Child2 { Child2Property = i }));
Set<Child2A>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Child2A { Child2AProperty = i }));
Set<Child2B>().AddRange(Enumerable.Range(0, rowsPerEntityType).Select(i => new Child2B { Child2BProperty = i }));
SaveChanges();
}
}
public class TPHContext : InheritanceContext
{
}
public class TPTContext : InheritanceContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Root>().UseTptMappingStrategy();
}
}
public class TPCContext : InheritanceContext
{
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
base.OnModelCreating(modelBuilder);
modelBuilder.Entity<Root>().UseTpcMappingStrategy();
}
}
public class Root
{
public int Id { get; set; }
public int RootProperty { get; set; }
}
public class Child1 : Root
{
public int Child1Property { get; set; }
}
public class Child1A : Root
{
public int Child1AProperty { get; set; }
}
public class Child1B : Root
{
public int Child1BProperty { get; set; }
}
public class Child2 : Root
{
public int Child2Property { get; set; }
}
public class Child2A : Root
{
public int Child2AProperty { get; set; }
}
public class Child2B : Root
{
public int Child2BProperty { get; set; }
}
}
Powered by SharpLab
// .NET 8 Lowered C# Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'ModelBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS1674: 'Inheritance.TPHContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'Inheritance.TPHContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'Inheritance.TPHContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'Inheritance.TPTContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'Inheritance.TPTContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'Inheritance.TPTContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'Inheritance.TPCContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'Inheritance.TPCContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'Inheritance.TPCContext' could be found (are you missing a using directive or an assembly reference?)
error CS0103: The name 'Set' does not exist in the current context
error CS0103: The name 'SaveChanges' does not exist in the current context
Powered by SharpLab
// .NET 8 IL Code unavailable due to errors:
error CS0234: The type or namespace name 'EntityFrameworkCore' does not exist in the namespace 'Microsoft' (are you missing an assembly reference?)
error CS0246: The type or namespace name 'DbContext' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbSet<>' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'DbContextOptionsBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS0246: The type or namespace name 'ModelBuilder' could not be found (are you missing a using directive or an assembly reference?)
error CS1674: 'Inheritance.TPHContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'Inheritance.TPHContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'Inheritance.TPHContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'Inheritance.TPTContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'Inheritance.TPTContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'Inheritance.TPTContext' could be found (are you missing a using directive or an assembly reference?)
error CS1674: 'Inheritance.TPCContext': type used in a using statement must be implicitly convertible to 'System.IDisposable'.
error CS1061: 'Inheritance.TPCContext' does not contain a definition for 'Database' and no accessible extension method 'Database' accepting a first argument of type 'Inheritance.TPCContext' could be found (are you missing a using directive or an assembly reference?)
error CS0103: The name 'Set' does not exist in the current context
error CS0103: The name 'SaveChanges' does not exist in the current context
Powered by SharpLab
|
Benchmark Description:
EF Core currently supports three techniques for mapping an inheritance model to a relational database:
1. Table-per-hierarchy (TPH), in which an entire .NET hierarchy of classes is mapped to a single database table.
2. Table-per-type (TPT), in which each type in the .NET hierarchy is mapped to a different table in the database.
3. Table-per-concrete-type (TPC), in which each concrete type in the .NET hierarchy is mapped to a different table in the database, where each table contains columns for all properties of the corresponding type.
TPT queries must join together multiple tables, and joins are one of the primary sources of performance issues in relational databases, we can see TPT is considerably slower than the other two approaches.
This benchmark setup is designed to compare the performance of three different inheritance mapping strategies in Entity Framework Core: Table Per Hierarchy (TPH), Table Per Type (TPT), and Table Per Concrete class (TPC). Each of these strategies has implications on database schema design and query performance, making this benchmark crucial for understanding the trade-offs associated with each approach. The .NET version isn't explicitly mentioned, but given the use of `Microsoft.EntityFrameworkCore`, it's safe to assume it targets a .NET Core or .NET 5/6 environment where EF Core is supported.
### General Setup
- **RowsPerEntityType**: The benchmark parameter is set to 5000, indicating that each entity type (Root, Child1, Child1A, etc.) will have 5000 rows seeded in the database. This provides a substantial dataset to test the performance of each inheritance strategy.
- **Database Configuration**: The `OnConfiguring` method in the `InheritanceContext` class specifies the use of SQL Server with a local database named `efsamples`. This implies that SQL Server must be available and accessible on the local machine where the benchmark is run.
- **Seeding Data**: The `SeedData` method in the `InheritanceContext` class is responsible for populating the database with test data for the benchmark. It adds a specified number of rows for each entity type to the database.
### Benchmarks
#### TPH Benchmark
- **Purpose**: This benchmark measures the performance of fetching all rows from a single table where all entity types are stored together, using the Table Per Hierarchy (TPH) strategy. TPH uses a single table to store the data of the entire inheritance hierarchy, differentiated by a discriminator column.
- **Importance**: TPH is often the default strategy due to its simplicity and can perform well when the hierarchy is not too deep or wide. This benchmark helps understand how well TPH performs in scenarios with a significant number of rows and a relatively deep inheritance hierarchy.
- **Expected Insights**: The results will indicate how efficiently EF Core can materialize entities when they are stored in a single table. A potential insight could be the impact of TPH on query performance when dealing with large datasets.
#### TPT Benchmark
- **Purpose**: This benchmark evaluates the performance of fetching data using the Table Per Type (TPT) strategy, where each entity type in the inheritance hierarchy is mapped to a separate table.
- **Importance**: TPT can lead to more normalized database schemas and might be preferred for complex hierarchies or when specific tables need to be optimized separately. However, it can suffer from performance issues due to the need for multiple joins to reconstruct the inheritance hierarchy.
- **Expected Insights**: The benchmark will show how TPT performs in terms of query efficiency, especially with a large number of entities. It's particularly useful for assessing the overhead introduced by the joins necessary to assemble the full entities.
#### TPC Benchmark
- **Purpose**: This benchmark tests the performance of the Table Per Concrete class (TPC) strategy, where each concrete class in the inheritance hierarchy is mapped to its own table, without any tables for abstract classes.
- **Importance**: TPC can be an effective strategy when entities in the hierarchy don't share many properties, or when it's important to avoid the discriminator column used in TPH. It can, however, result in schema duplication and potentially more complex queries.
- **Expected Insights**: Insights from this benchmark will highlight the performance implications of using TPC, especially regarding how EF Core handles queries that potentially span multiple tables without relying on inheritance-based joins.
### Conclusion
Running these benchmarks will provide valuable insights into how different inheritance mapping strategies affect performance in EF Core. It's crucial for developers to understand these trade-offs to make informed decisions when designing their database schema and choosing the appropriate inheritance strategy for their application's needs.