Inheritance Modelling Patterns with Entity Framework 6 Code First

Introduction

In this post I’m going to demonstrate how to model inheritance using Entity Framework 6 Code First. In particular, I’m going to demonstrate the following alternative database mapping strategies –

  • Table Per Hierarchy (TPH)
  • Table Per Concrete Type (TPC)
  • Table Per Type (TPT)

My model is as follows –

contract

Contract, the base class, has a start and end date, and a unique identifier. CreditContract and PrepaymentContract derive from Contract and hence inherit it’s properties. In addition, CreditContract has credit limit and currency properties, whilst PrepaymentContract has prepayment amount and period properties..

Code First Classes

First step is to create the model classes.

Contract

Contract is the base class for the hierarchy, hence the abstract modifier.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }
}

CreditContract

CreditContract is derived from Contract.

For simplicity I have modelled the CreditLimitCurrency property as a string with data annotations mapping it to a char(3) column. In practice it would be better to model this as a Enum.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public class CreditContract : Contract
    {
        [Required]
        public float CreditLimit { get; set; }

        [Column(TypeName = "char")]
        [StringLength(3)]
        [Required]
        public string CreditLimitCurrency { get; set; }
    }
}

PrepaymentContract

PrepaymentContract also derives from Contract.

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public class PrepaymentContract : Contract
    {
        [Required]
        public float PrepaymentAmount { get; set; }

        [Column(TypeName = "char")]
        [StringLength(1)]
        [Required]
        public string PrepaymentPeriod { get; set; }
    }
}

Table Per Hierarchy (TPH)

With the TPH mapping strategy, all classes in the inheritance tree are mapped to a single database table. A database field is used as a discriminator in determining which concrete type each database record should be mapped to.

To instruct Entity Framework to use this strategy, all that is required is to derive a class from the DbContext class, and add a DBSet property for the Contract type, whilst NOT adding DBSet properties for CreditContract nor PrepaymentContract.

I have also added an override of the OnModelCreating method. I’ll discuss this in a moment.

CodeFirstInheritanceDbContext

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Contract>()
                .Map<CreditContract>(m => m.Requires("Discriminator").HasValue("C"))
                .Map<PrepaymentContract>(m => m.Requires("Discriminator").HasValue("P"));

            base.OnModelCreating(modelBuilder);
        }
    }
}

If we then enable migrations and add an initial migration, the following DbMigration class is generated –

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Contracts",
                c => new
                    {
                        ContractID = c.Int(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        CreditLimit = c.Single(),
                        CreditLimitCurrency = c.String(maxLength: 3, fixedLength: true, unicode: false),
                        PrepaymentAmount = c.Single(),
                        PrepaymentPeriod = c.String(maxLength: 1, fixedLength: true, unicode: false),
                        Discriminator = c.String(nullable: false, maxLength: 128),
                    })
                .PrimaryKey(t => t.ContractID);

        }

        public override void Down()
        {
            DropTable("dbo.Contracts");
        }
    }
}

A single table, Contract, is created with fields from all types. Of note is the Discriminator field, which we did not explicitly specify in our hierarchy. EF has generated this field for us and will use it to discriminate between credit and prepayment contract records.

If we update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

The following records are added to the Contract table –

tphresults

By default EF will use the full names of the types as the discriminator values. This may significantly increase the size of each record. EF allows us to override these values using the fluent API. This is what I did in the OnModelCreating method of the CodeFirstInheritanceDbContext class, in order to shorten the discriminator to a single character.

We can then query the model across contract types and for specific contract types as follows –

using (var db = new CodeFirstInheritanceDbContext())
{
    var countOfAllContract = db.Contracts.Count();
    var countOfCreditContracts = db.Contracts.OfType<CreditContract>().Count();
}

TPH is the simplest mapping strategy to implement. And as all data is stored in a single table there are no joins required when querying data. It is therefore the most performant strategy.

However, there are a few issues –

  • If you look back at the CreditContract and PrepaymentContract classes you’ll note that all the properties are decorated with the [Required] attribute. However, to fit records of both types into a single database table EF has had to generate the fields as nullable. Data integrity has potentially been compromised.
  • It is not clear from the database table which fields relate to which type. This may make life difficult when populating the table from other sources.
  • The table breaks 3rd normal form as the values contained in the sub-type fields depend on the discriminator. 3rd normal form states that fields should only depend on the table key.

Table Per Concrete Type (TPC)

With the TPC mapping strategy, each concrete class is mapped to it’s own database table. So we get 2 tables, CreditContract and PrepaymentContract, with each having all fields in the respective branch of the hierarchy. We do not get a table for the abstract base class.

Before we instruct EF to use this strategy, we need to make a small modification to the Contract class – change the type of the ContractID property from int to Guid. I’ll discuss why in a moment.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public Guid ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }
}

As per the TPH strategy, we derive a class from DbContext, and add a DBSet property for the Contract type, whilst NOT adding DBSet properties for CreditContract nor PrepaymentContract.

We then override the OnModelCreating method and add fluent API code to instruct EF to map the CreditContract entity to a specific table. And to also map the inherited properties to this table. The table name does not have to be the same as the entity name.

We do the same for PrepaymentContract.

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CreditContract>().Map(m =>
            {
                m.MapInheritedProperties();
                m.ToTable("CreditContract");
            });

            modelBuilder.Entity<PrepaymentContract>().Map(m =>
            {
                m.MapInheritedProperties();
                m.ToTable("PrepaymentContract");
            });   
           
            base.OnModelCreating(modelBuilder);
        }
    }
}

If we then enable migrations and add an initial migration, we can see from the migration script that 2 separate tables are created on the database.

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
    
    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.CreditContract",
                c => new
                    {
                        ContractID = c.Guid(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        CreditLimit = c.Single(nullable: false),
                        CreditLimitCurrency = c.String(nullable: false, maxLength: 3, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID);
            
            CreateTable(
                "dbo.PrepaymentContract",
                c => new
                    {
                        ContractID = c.Guid(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                        PrepaymentAmount = c.Single(nullable: false),
                        PrepaymentPeriod = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID);
            
        }
        
        public override void Down()
        {
            DropTable("dbo.PrepaymentContract");
            DropTable("dbo.CreditContract");
        }
    }
}

Update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

The CreditContract entity has been persisted to the CreditContract table –

tpc1

Whilst the PrepaymentContract entity has been persisted to the PrepaymentContract table –

tpc2

We can then query the entities exactly as we did when the TPH strategy was adopted. The consumer of the model does not need to be aware of the mapping strategy.

We changed the ContractID property from int to Guid. The reason is that we are asking the database server to generate unique identifiers for our records. The concrete entity types are mapped to separate tables, and the database server does not and should not know this. Integer identity fields are only unique within the context of a single table, and so if the type was int we would soon experience errors similar to the following –

The changes to the database were committed successfully, but an error occurred while updating the object context. The ObjectContext might be in an inconsistent state. Inner exception message: AcceptChanges cannot continue because the object's key values conflict with another object in the ObjectStateManager. Make sure that the key values are unique before calling AcceptChanges.

However, by changing the type to Guid we are, to all intents and purposes, generating unique fields.

The downside of doing this is that SQL Server GUID fields are 16 bytes in length, compared to 4 bytes for an int.

Compared to TPH, TPC is less performant when querying across types as the queries require a UNION between the tables. However, it will be equally performant when querying a single type.

As TPC stores the data in separate tables, it allows for non-nullable columns, hence ensuring data integrity. And it is clear which properties are required by which type.

Table Per Type (TPT)

With the Table By Type (TPT) strategy, both abstract and concrete types are mapped to their own tables in the database with the inheritance relationships represented by foreign key relationships.

To instruct EF to use this strategy we have 2 options.

The first option is to override the OnModelCreating method and add Fluent API code to instruct EF to map the CreditContract entity to a specific table, and the PrepaymentContract entity to a different specific table.

using System.Data.Entity;

namespace CodeFirstInheritance.Models
{
    public class CodeFirstInheritanceDbContext : DbContext
    {
        public DbSet<Contract> Contracts { get; set; }

        protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<CreditContract>().Map(m =>
            {
                m.ToTable("CreditContract");
            });

            modelBuilder.Entity<PrepaymentContract>().Map(m =>
            {
                m.ToTable("PrepaymentContract");
            });   
           
            base.OnModelCreating(modelBuilder);
        }
    }
}

The second option is to annotate the concrete types with the [Table()] attribute.

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace CodeFirstInheritance.Models
{
    public abstract class Contract
    {
        [Key]
        [DatabaseGeneratedAttribute(DatabaseGeneratedOption.Identity)]
        public int ContractID { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime StartDate { get; set; }

        [DataType(DataType.Date)]
        [Required]
        public DateTime EndDate { get; set; }
    }

    [Table("CreditContract")]
    public class CreditContract : Contract
    {
        [Required]
        public float CreditLimit { get; set; }

        [Column(TypeName = "char")]
        [StringLength(3)]
        [Required]
        public string CreditLimitCurrency { get; set; }
    }

    [Table("PrepaymentContract")]
    public class PrepaymentContract : Contract
    {
        [Required]
        public float PrepaymentAmount { get; set; }

        [Column(TypeName = "char")]
        [StringLength(1)]
        [Required]
        public string PrepaymentPeriod { get; set; }
    }
}

Enabling migrations and adding an initial migration gives us the following –

namespace CodeFirstInheritance.Migrations
{
    using System;
    using System.Data.Entity.Migrations;

    public partial class Initial : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Contracts",
                c => new
                    {
                        ContractID = c.Int(nullable: false, identity: true),
                        StartDate = c.DateTime(nullable: false),
                        EndDate = c.DateTime(nullable: false),
                    })
                .PrimaryKey(t => t.ContractID);

            CreateTable(
                "dbo.CreditContract",
                c => new
                    {
                        ContractID = c.Int(nullable: false),
                        CreditLimit = c.Single(nullable: false),
                        CreditLimitCurrency = c.String(nullable: false, maxLength: 3, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID)
                .ForeignKey("dbo.Contracts", t => t.ContractID)
                .Index(t => t.ContractID);

            CreateTable(
                "dbo.PrepaymentContract",
                c => new
                    {
                        ContractID = c.Int(nullable: false),
                        PrepaymentAmount = c.Single(nullable: false),
                        PrepaymentPeriod = c.String(nullable: false, maxLength: 1, fixedLength: true, unicode: false),
                    })
                .PrimaryKey(t => t.ContractID)
                .ForeignKey("dbo.Contracts", t => t.ContractID)
                .Index(t => t.ContractID);

        }

        public override void Down()
        {
            DropForeignKey("dbo.PrepaymentContract", "ContractID", "dbo.Contracts");
            DropForeignKey("dbo.CreditContract", "ContractID", "dbo.Contracts");
            DropIndex("dbo.PrepaymentContract", new[] { "ContractID" });
            DropIndex("dbo.CreditContract", new[] { "ContractID" });
            DropTable("dbo.PrepaymentContract");
            DropTable("dbo.CreditContract");
            DropTable("dbo.Contracts");
        }
    }
}

3 tables are created, one per type, as shown in the following database schema –

dbdiagram

Update the database and then execute the following code –

using (var db = new CodeFirstInheritanceDbContext())
{
    CreditContract creditContract = new CreditContract();
    creditContract.StartDate = new DateTime(2015, 1, 1);
    creditContract.EndDate = new DateTime(2015, 12, 31);
    creditContract.CreditLimit = 100000;
    creditContract.CreditLimitCurrency = "USD";
    db.Contracts.Add(creditContract);

    PrepaymentContract prepaymentContract = new PrepaymentContract();
    prepaymentContract.StartDate = new DateTime(2015, 1, 1);
    prepaymentContract.EndDate = new DateTime(2015, 12, 31);
    prepaymentContract.PrepaymentAmount = 10000;
    prepaymentContract.PrepaymentPeriod = "M";
    db.Contracts.Add(prepaymentContract);

    db.SaveChanges();
}

And this is what we get in our Contract table –

tpt1

And in the CreditContract table –

tpt2

And finally the PrepaymentContract table –

tpt3

TPT is the least performant of the strategies, but enables the highest degree of database integrity and normalization.

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s