Table Per Type (TPT) database using EntityFramework Code First

Recently @dot_Net_Junkie posted a question on StackOverflow on how to implement the TPT using EntityFramework. His original question was using a DB-First approach so my immediate thought was 'how about trying a code-first approach'. I set up a quick test project and this is how did it:

The models

public abstract class BaseTable
{
    public int Id { get; set; }
    public string Name { get; set; }
}

[Table("DerivedWithRelation")]
public class DerivedWithRelation : BaseTable
{
    public int Amount { get; set; }
    public string About { get; set; }
    public int RelatedId { get; set; }

    public virtual ICollection<Relation> Relations { get; set; }
}

[Table("DerivedWithoutRelation")]
public class DerivedWithoutRelation : BaseTable
{
    public int Quantity { get; set; }
    public string Description { get; set; }
}

public class Relation
{
    public int Id { get; set; }
    public string RelationshipType { get; set; }

    public virtual DerivedWithRelation DerivedWithRelation { get; set; }
}

The context

public class MyContext : DbContext
{
    public MyContext()
        : base("DefaultConnection")
    {            
    }
	
    public IDbSet<BaseTable> BaseTables { get; set; }
    public IDbSet<DerivedWithRelation> DerivedWithRelations { get; set; }
    public IDbSet<DerivedWithoutRelation> DerivedWithoutRelations { get; set; }
}

Creating the Entity Framework code first migration Running Add-Migration TPTCodeFirstTest -ProjectName MyProject.Data -StartUpProjectName MyProject.Data should create something similar to the following:

public partial class AddTPTTest : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.BaseTables",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    Name = c.String(),
                })
            .PrimaryKey(t => t.Id);
        
        CreateTable(
            "dbo.Relations",
            c => new
                {
                    Id = c.Int(nullable: false, identity: true),
                    RelationshipType = c.String(),
                    DerivedWithRelation_Id = c.Int(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.DerivedWithRelation", t => t.DerivedWithRelation_Id)
            .Index(t => t.DerivedWithRelation_Id);
        
        CreateTable(
            "dbo.DerivedWithRelation",
            c => new
                {
                    Id = c.Int(nullable: false),
                    Amount = c.Int(nullable: false),
                    About = c.String(),
                    RelatedId = c.Int(nullable: false),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.BaseTables", t => t.Id)
            .Index(t => t.Id);
        
        CreateTable(
            "dbo.DerivedWithoutRelation",
            c => new
                {
                    Id = c.Int(nullable: false),
                    Quantity = c.Int(nullable: false),
                    Description = c.String(),
                })
            .PrimaryKey(t => t.Id)
            .ForeignKey("dbo.BaseTables", t => t.Id)
            .Index(t => t.Id);
        
    }
    
    public override void Down()
    {
        DropIndex("dbo.DerivedWithoutRelation", new[] { "Id" });
        DropIndex("dbo.DerivedWithRelation", new[] { "Id" });
        DropIndex("dbo.Relations", new[] { "DerivedWithRelation_Id" });
        DropForeignKey("dbo.DerivedWithoutRelation", "Id", "dbo.BaseTables");
        DropForeignKey("dbo.DerivedWithRelation", "Id", "dbo.BaseTables");
        DropForeignKey("dbo.Relations", "DerivedWithRelation_Id", "dbo.DerivedWithRelation");
        DropTable("dbo.DerivedWithoutRelation");
        DropTable("dbo.DerivedWithRelation");
        DropTable("dbo.Relations");
        DropTable("dbo.BaseTables");
    }
}

All that is left is to push our changes to the database
Update-Database -ProjectName MyProject.Data -StartUpProjectName MyProject.Data -Verbose

Et voila: The following database structure is created:

Hope that helps. If you found this useful, please share using the links at the start of this post.