Business data model
In both cases we are going to use the following business data model. For our outward-facing interface, we are using DTOs. We have a PersonDto with 3 fields and 2 derived classes CustomerDto and EmployeeDto, both having 1 additional field.
public class PersonDto
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class CustomerDto : PersonDto
{
public DateTime DateOfBirth { get; set; }
}
public class EmployeeDto : PersonDto
{
public decimal Turnover { get; set; }
}
Table-Per-Hierarchy (TPH)
Now, let’s look at the solution to have internal entities based on TPH. At first, we need to define the entity classes. Thanks to the native support of TPH and the very simple data model the entities are identical to the DTOs.
public class PersonTph
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class CustomerTph : PersonTph
{
public DateTime DateOfBirth { get; set; }
}
public class EmployeeTph : PersonTph
{
public decimal Turnover { get; set; }
}
We can implement the database context to be able to access customers and employees like this:
public class TphDbContext : DbContext
{
public DbSet<PersonTph> People { get; set; }
public DbSet<CustomerTph> Customers { get; set; }
public DbSet<EmployeeTph> Employees { get; set; }
public TphDbContext(DbContextOptions<TphDbContext> options)
: base(options)
{
}
}
And for the sake of completion we will be using Entity Framework Core Migrations to create and update the database schema. For that we execute the following command:
dotnet ef migrations add Initial_TPH_Migration -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj -c TphDbContext -o ./TphModel/CodeFirst/Migrations
As expected we have 1 table with all fields from person, customer and employee and 1 additional column Descriminator, so EF Core is able to differentiate customers from employees.
public partial class Initial_TPH_Migration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable("People",
table => new
{
Id = table.Column<Guid>(nullable: false),
FirstName = table.Column<string>(nullable: true),
LastName = table.Column<string>(nullable: true),
DateOfBirth = table.Column<DateTime>(nullable: true),
Turnover = table.Column<decimal>(nullable: true),
Discriminator = table.Column<string>(nullable: false)
},
constraints: table => table.PrimaryKey("PK_People", x => x.Id));
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("People");
}
}
The usage of TPH is nothing special, we just use the appropriate property on the TphDbContext.
TphDbContext ctx = ...
// Create a customer
ctx.Customers.Add(new CustomerTph()
{
Id = Guid.NewGuid(),
FirstName = "John",
LastName = "Foo",
DateOfBirth = new DateTime(1980, 1, 1)
});
// Fetch all customers
var customers = ctx.Customers
.Select(c => new CustomerDto()
{
Id = c.Id,
FirstName = c.FirstName,
LastName = c.LastName,
DateOfBirth = c.DateOfBirth
})
.ToList();
Table-Per-Type (TPT)
Ok, that was easy. Now, how can a solution for TPT look like? With the absence of native support for TPT the entities do not derive from each other but reference each other. The field Id of customer and employee is the primary key and a foreign key pointing to person. The structure of the entities is very similar to the database schema of the TPT pattern.
public class PersonTpt
{
public Guid Id { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
}
public class CustomerTpt
{
[ForeignKey(nameof(Person))]
public Guid Id { get; set; } // PK and FK pointing to PersonTpt
public PersonTpt Person { get; set; }
public DateTime DateOfBirth { get; set; }
}
public class EmployeeTpt
{
[ForeignKey(nameof(Person))]
public Guid Id { get; set; } // PK and FK pointing to PersonTpt
public PersonTpt Person { get; set; }
public decimal Turnover { get; set; }
}
The database context of TPT is identical to the one of TPH.
public class TptDbContext : DbContext
{
public DbSet<PersonTpt> People { get; set; }
public DbSet<CustomerTpt> Customers { get; set; }
public DbSet<EmployeeTpt> Employees { get; set; }
public TptDbContext(DbContextOptions<TptDbContext> options)
: base(options)
{
}
}
Next, we will create an EF Core migration with the following command
dotnet ef migrations add Initial_TPT_Migration -p ./../../EntityFramework.Demo.csproj -s ./../../EntityFramework.Demo.csproj -c TptDbContext -o ./TptModel/CodeFirst/Migrations
The migration creates 3 tables with correct columns, primary keys and foreign keys.
public partial class Initial_TPT_Migration : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable("People",
table => new
{
Id = table.Column<Guid>(nullable: false),
FirstName = table.Column<string>(nullable: true),
LastName = table.Column<string>(nullable: true)
},
constraints: table => table.PrimaryKey("PK_People", x => x.Id));
migrationBuilder.CreateTable("Customers",
table => new
{
Id = table.Column<Guid>(nullable: false),
DateOfBirth = table.Column<DateTime>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Customers", x => x.Id);
table.ForeignKey("FK_Customers_People_Id",
x => x.Id,
"People",
"Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateTable("Employees",
table => new
{
Id = table.Column<Guid>(nullable: false),
Turnover = table.Column<decimal>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Employees", x => x.Id);
table.ForeignKey("FK_Employees_People_Id",
x => x.Id,
"People",
"Id",
onDelete: ReferentialAction.Cascade);
});
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("Customers");
migrationBuilder.DropTable("Employees");
migrationBuilder.DropTable("People");
}
}
The biggest difference – compared to TPH – is in the usage of the entities. To get to the fields of the person (i.e. the base type) we have to use the navigational property Person. This may seem cumbersome at first, but it is not a hindrance in practice.
TptDbContext ctx = ...
// Fetch all customers
var customers = ctx.Customers
.Select(c => new CustomerDto()
{
Id = c.Id,
FirstName = c.Person.FirstName,
LastName = c.Person.LastName,
DateOfBirth = c.DateOfBirth
})
.ToList();
// Create a customer
ctx.Customers.Add(new CustomerTpt()
{
Person = new PersonTpt()
{
Id = Guid.NewGuid(),
FirstName = "John",
LastName = "Foo"
},
DateOfBirth = new DateTime(1980, 1, 1)
});
Voila!
Conclusion
With Entity Framework Core we can use both the Table-Per-Hierarchy and Table-Per-Type patterns. At least with a code first approach. Whether and how the patterns are applicable using the database first approach we will see in the next blog post.
