Entity Framework Core – Table per Hierarchy

The mature brother of Entity Framework Core, Entity Framework, supports several inheritance models – Table per Type (TPT), Table per concrete class (TPC), and Table per Hierarchy (TPH). Not all inheritance models are supported by Entity Framework Core, yet. They are planned with future releases, but Table per Hierarchy is supported with Entity Framework Core 1.0.
This article explains how you can implement TPH using both conventions, and the fluent API.

Model

First, let’s create a hierarchy of types. The Payment class is the base class of the hierarchy containing an PaymentId property that will map to a primary key ot the Payments table. Other than that, the Payment class contains Amount and Name properties that will be available with all derived types.

public abstract class Payment
{
    public int PaymentId { get; set; }
    public decimal Amount { get; set; }
    public string Name { get; set; }
}

Concrete types that derive form the base class Payment are CashPayment and CreditcardPayment. These are concrete types that will be instantiated.

public class CashPayment : Payment
{
}

public class CreditcardPayment : Payment
{
    public string CreditcardNumber { get; set; }
}

Context with Conventions

To map these types to the database, the PaymentsContext class is created. This class derives from the base class DbContext. For mapping the entity types to a SQL Server database, the NuGet packages Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.SqlServer are added to the dependencies. The OnConfiguring method defines to use the SQL Server passing a connection string to the UseSqlServer method.
Using Entity Framework Core conventions, all the types of the hierarchy, Payment, CashPayment, and CreditcardPayment are used to specify the generic type for the DbSet properties. Because of the hiarchry with these types, not separate tables are used within SQL Server, just one table for the base class, Payments.

public class PaymentsContext : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(
            @"server=(localdb)\MSSQLLocaldb;database=PaymentsSample1;trusted_connection=true");
    }
    public DbSet<Payment> Payments { get; set; }
    public DbSet<CashPayment> CashPayments { get; set; }
    public DbSet<CreditcardPayment> CreditcardPayments { get; set; }
}

Create and fill the database

The database is created invoking the EnsureCreated method of the DatabaseFacade class, and a few concrete payments are added to the context, and written to the database by invoking SaveChanges.

using (var context = new PaymentsContext())
{
    context.Database.EnsureCreated();
    context.Payments.Add(new CashPayment { Amount = 0.2M, Name = "Donald" });
    context.Payments.Add(new CashPayment { Amount = 100000M, Name = "Scrooge" });
    context.Payments.Add(
        new CreditcardPayment 
        { 
            Amount = 500, 
            Name = "Gladstone", 
            CreditcardNumber = "08154711123" 
        });
    int changed = context.SaveChanges();
    Console.WriteLine($"changed {changed} records");
}

Using conventions, the Payments table created contains a Discriminator column:

CREATE TABLE [dbo].[Payments] (
    [PaymentId]        INT             IDENTITY (1, 1) NOT NULL,
    [Amount]           DECIMAL (18, 2) NOT NULL,
    [Discriminator]    NVARCHAR (MAX)  NOT NULL,
    [Name]             NVARCHAR (MAX)  NULL,
    [CreditcardNumber] NVARCHAR (MAX)  NULL,
    CONSTRAINT [PK_Payments] PRIMARY KEY CLUSTERED ([PaymentId] ASC)
);

During runtime, the Discriminator column contains the name of the model class stored:

Reading Data

Next, let’s read the data by accessing the Payments property.

var payments = context.Payments.ToList();
foreach (var payment in payments)
{
    Console.WriteLine($"{payment.Name} {payment.Amount} {payment.GetType().Name}");
}

Running the application, you can see that indeed CashPayment and CreditcardPayment objects are returned.

The LINQ OfType method comes very handy with TPH mapping. Using this method, a SQL statement is generated to query only for the specified discriminator, and in the sample code to only return Payment records where the Discriminator has a value of CreditcardPayment.

foreach (var payment in context.Payments.OfType<CreditcardPayment>())
{
    Console.WriteLine($"{payment.Name} {payment.Amount} {payment.GetType().Name}");
}

Instead of using the OfType method, you can also use the CreditcardPayments property of the context to retrieve all CreditcardPayment objects. Using this property creates a WHERE clause with the discriminator similar to the OfType method.

Context with Fluent API

For having a greater flexibility, you can use the Fluent API to customize definition for TPH. The class Payments2Context just defines a Payments property to map the Payment type to the Payments table. The derived types are assigned using the ModelBuilder. The method HasDiscriminator specifies the column named type for the Payments table. The values for CashPayment and CreditcardPayment are specified calling the HasValue method.

public class Payments2Context : DbContext
{
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(@"server=(localdb)\MSSQLLocaldb;database=PaymentsSample2;trusted_connection=true");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Payment>()
            .HasDiscriminator<string>("type")
            .HasValue<CashPayment>("cash")
            .HasValue<CreditcardPayment>("creditcard");
    }
    public DbSet<Payment> Payments { get; set; }
}

The generated table with its values now looks like this – with a type column, and the values cash and creditcard:

Summary

Entity Framework Core 1.0 is not yet feature equivalent compared to Entity Framework 6. However, it already supports many features available with EF 6 – one of the supported features are the mapping of a table to a hierarchy (TPH), which is nicely supported by conventions, and has a better flexibility using the Fluent API.

Sample Code

The sample code is available at GitHub.

Have fun with programming and learning!
Christian

More Information

More information about Entity Framework Core is available in my new book and my C# workshops:

Professional C# 6 and .NET Core 1.0

Christian Nagel’s Workshops

Image from © Saniphoto | Dreamstime.com Eye Scanning Data Photo