Entity Framework Core 2.1 Performance: Beware of N+1 Queries (Revisited)
Entity Framework Core: Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 2 - Database First)

Entity Framework Core: Inheritance - Table-per-Type (TPT) is not supported, is it? (Part 1 - Code First)

With O/R mappers there are a few patterns how a class hierarchy can be mapped to a relational database. The most popular ones are the Table-Per-Hierarchy (TPH) and the Table-Per-Type (TPT) patterns. The Entity Framework Core 2.x (EF Core) officially supports the Table-per-Hierarchy pattern only. The support of Table-per-Type is in the backlog of the Entity Framework team, i.e. it is not (officially) supported yet. Nevertheless, you can use TPT with the current version of EF Core. The usability is not ideal but acceptable. Especially, if you have an existing database using TPT then this short blog post series may give you an idea how to migrate to EF Core.

In the 1st part we will set up 2 EF Core models incl. database migrations for TPH and TPT using code first approach. In the 2nd part we are going to use the database first approach.

Remarks: this blog post is not about what approach is the best for your solution :)

All demos are on Github.

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.

Stay tuned.

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment

Your Information

(Name and email address are required. Email address will not be displayed with the comment.)