Inheritance with EF Core 5 and migrations

Published on: 28 November 2020

Author: Ramesh Kanjinghat

Entity Framework Core supports mapping inheritance hierarchy .Net types to database tables. By default, EF Core maps all the types in the hierarchy to a single table. This table will have columns for all the inherited properties and unique properties of any .Net type in the hierarchy. This type of mapping is called Table-per-hierarchy (TPH). Entity Framework Core 5 has another type of mapping called Table-per-type (TPT).

In this article I will take a simple scenario and walk through how both, TPH and TPT, works with EF Core 5.

Scenario

The requirement is to have a system where organization can manage the document templates which the employees of the organization can use to generate policy documents. The organization has presence in multiple regions and the regions are further divided in to countries. These regions and countries can either use the organization level policy documents or have their own policies. Simply put the organization provides the templates for the policy documents. Regions can either use the company level templates or define their own. Countries also have the option to use organization or region templates or define their own, if required.

Now let’s get into the technicalities

Entities

Entities, as like to call, are .Net types to represent the database tables. Instead of class I have used record type, a new type in .Net 5. .Net 5 record type provides a simple way to create immutable types. Please check https://docs.microsoft.com/en-us/dotnet/csharp/whats-new/csharp-9#record-types for more details.

PolicyTemplateHierarchy is an enum to represent the hierarchy

1public enum PolicyTemplateHierarchy
2{
3	Unknown = 0,
4	Organization = 1,
5	Region = 2,
6	Country = 3
7}

OrganizationPolicyTemplate

OrganizationPolicyTemplate is a record type which represents the organization level templates.

1public record OrganizationPolicyTemplate
2{
3    protected OrganizationPolicyTemplate(PolicyTemplateHierarchy policyTemplateHierarchy, string displayName, string location, DateTime createdDate, string createdUser)
4    {
5        this.PolicyTemplateHierarchy = policyTemplateHierarchy;
6        this.DisplayName = (string.IsNullOrWhiteSpace(displayName) ? null : displayName) ?? throw new ArgumentNullException(nameof(displayName));
7        this.Location = (string.IsNullOrWhiteSpace(location) ? null : location) ?? throw new ArgumentNullException(nameof(location));
8        this.CreatedDate = createdDate;
9        this.CreatedUser = (string.IsNullOrWhiteSpace(createdUser) ? null : createdUser) ?? throw new ArgumentNullException(nameof(createdUser));
10    }
11    public OrganizationPolicyTemplate(string displayName, string location, DateTime createdDate, string createdUser)
12        : this(PolicyTemplateHierarchy.Organization, displayName, location, createdDate, createdUser)
13    {
14    }
15
16    public int Id { get; init; }
17    public PolicyTemplateHierarchy PolicyTemplateHierarchy { get; init; }
18    public string DisplayName { get; init; }
19    public string Location { get; init; }
20    public DateTime CreatedDate { get; init; }
21    public string CreatedUser { get; init; }
22}

RegionPolicyTemplate

RegionPolicyTemplate is a record type that represent the region level templates. To keep it simple the only additional property that Region policy templates have is RegionId. This type inherits OrganizationPolicyTemplate

1public record RegionPolicyTemplate : OrganizationPolicyTemplate
2{
3    protected RegionPolicyTemplate(PolicyTemplateHierarchy policyTemplateHierarchy, string displayName, string location, DateTime createdDate, string createdUser, int regionId)
4        : base(policyTemplateHierarchy, displayName, location, createdDate, createdUser)
5    {
6        this.RegionId = regionId;
7    }
8
9    public RegionPolicyTemplate(string displayName, string location, DateTime createdDate, string createdUser, int regionId) 
10        : this(PolicyTemplateHierarchy.Region, displayName, location, createdDate, createdUser, regionId)
11    {
12    }
13
14    public int RegionId { get; init; }
15}

CountryPolicyTemplate

CountryPolicyTemplate is a record type that represent the region level templates . Country templates have an additional property, CountryId. This one inherits RegionPolicyTemplate and thus inherits OrganizationPolicyTemplate

1public record CountryPolicyTemplate : RegionPolicyTemplate
2{
3    public CountryPolicyTemplate(string displayName, string location, DateTime createdDate, string createdUser, int regionId, int countryId)
4        : base(PolicyTemplateHierarchy.Country, displayName, location, createdDate, createdUser, regionId)
5    {
6        this.CountryId = countryId;
7    }
8
9    public int CountryId { get; init; }
10}

Now we will create code first migrations for both TPH and TPT mapping.

Table-per-hierarchy (TPH)

This is the only mapping available with earlier versions of Entity Framework Core 5. In case of TPH there will be only one table. In our case we will name the table PolicyTemplate. Now let’s add a DbContext where we will configure the mapping a Table-per-hierarchy. Lines from 12 to 18 is where we are configuring the table.

Line 13 tells EF Core to name the table, PolicyTemplate.
Line 14 configures the discriminator. As the name tells Discriminator is how EF Core differentiates the .Net types in the hierarchy. Discriminator can be either a single column or a combination of columns, as composite keys.
Lines 15 to 17 tells the EF Core what is the discriminator value for each .Net type in the hierarchy.

1public class PolicyTemplateDbContext : DbContext
2{
3    public PolicyTemplateDbContext(DbContextOptions<PolicyTemplateDbContext> dbContextOptions) : base(dbContextOptions)
4    {
5    }
6    public DbSet<OrganizationPolicyTemplate> OrganizationPolicyTemplates { get; set; }
7    public DbSet<RegionPolicyTemplate> RegionPolicyTemplates { get; set; }
8    public DbSet<CountryPolicyTemplate> CountryPolicyTemplates { get; set; }
9    protected override void OnModelCreating(ModelBuilder modelBuilder)
10    {
11        modelBuilder
12            .Entity<OrganizationPolicyTemplate>()
13            .ToTable("PolicyTemplate")
14            .HasDiscriminator(x => x.PolicyTemplateHierarchy)
15            .HasValue<OrganizationPolicyTemplate>(PolicyTemplateHierarchy.Organization)
16            .HasValue<RegionPolicyTemplate>(PolicyTemplateHierarchy.Region)
17            .HasValue<CountryPolicyTemplate>(PolicyTemplateHierarchy.Country);
18    }
19}

Now let’s see how the table, PolicyTemplate, looks in database. Below is a screenshot of the table in SSMS

Table schema with Table-per-hierarchy mapping

In the table properties from OrganizationPolicyTemplate type are non-nullable because those properties weren't nullable in the type. Both RegionId and CountryId columns are nullable. These 2 columns will have null if the record is of Organization level, only CountryId will be null if record if of region level.

Table-per-type (TPT)

This is mapping available starting with Entity Framework Core 5. In case of TPT each .Net type in the inheritance hierarchy gets a table in database. The top .Net type gets a full-fledged table with all common properties and subsequent types get table with only the properties they have added.

Lines 12, 13 and 14 we are telling EF Core that we want each type to be stored in its own table.
- .Net type OrganizationPolicyTemplate maps to the table OrganizationPolicyTemplate
- .Net type RegionPolicyTemplate maps to the table RegionPolicyTemplate
- .Net type CountryPolicyTemplate maps to the table CountryPolicyTemplate

In our case I kept both >net type and table name same but we can have a different name for the table if we want.

1public class PolicyTemplateDbContext : DbContext
2{
3    public PolicyTemplateDbContext(DbContextOptions<PolicyTemplateDbContext> dbContextOptions) 
4    : base(dbContextOptions)
5    {
6    }
7    public DbSet<OrganizationPolicyTemplate> OrganizationPolicyTemplates { get; set; }
8    public DbSet<RegionPolicyTemplate> RegionPolicyTemplates { get; set; }
9    public DbSet<CountryPolicyTemplate> CountryPolicyTemplates { get; set; }
10    protected override void OnModelCreating(ModelBuilder modelBuilder)
11    {
12        modelBuilder.Entity<OrganizationPolicyTemplate>().ToTable("OrganizationPolicyTemplate");
13        modelBuilder.Entity<RegionPolicyTemplate>().ToTable("RegionPolicyTemplate");
14        modelBuilder.Entity<CountryPolicyTemplate>().ToTable("CountryPolicyTemplate");
15    }
16}

The table schema in SSMS is as given below

Schema of the tables with Table-per-type mapping

I kept this part to the end so that it will be easier to understand the data once we understand both TPH and TPT.

TPH doesn't seem to adhere to normalization rules where TPT respects normalization. If we want to add a new hierarchy level with new properties, TPH requires us to add a new column where TPT adds a new table. I like TPT because

personally, I don't like null values floating around.

I feel it safe to add a new table compared to altering an existing table to add a new column.

A working version of this code with both TPH and TPT can be found at https://github.com/Dhrutara/blogs.dhrutara.com.blogs/tree/main/EFCoreInheritance