Code First Entity Framework - Table Per Type

Classes in .Net can be inherited and extended, with new properties added for the sub classes. Entity Framework allows you to represent this in three main ways: Table-Per-Type, Table-Per-Hierarchy, Table-Per-ConcreteClass. This article describes Table-Per-Type.

Firstly, in Table-Per-Type (TPT) your base class gets a table, and any classes inherited and extending your base class get their own table in the database. These secondary tables are linked to the base table by Foreign Key. Let's have an example. We define a new POCO class called Animal with a couple of properties:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace TestEF.Entity
{
    public class Animal
    {
        public int Id { get; set; }

        public bool WarmBlooded { get; set; }

        public string NotedCountry { get; set; }
    }
}

To control how the table is created, we decorate it with the usual Data Annotations:

    public class Animal
    {
        [Key]
        [Required]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public int Id { get; set; }

        [Required]
        public bool WarmBlooded { get; set; }

        [Required]
        [StringLength(100)]
        public string NotedCountry { get; set; }
    }

Now we create a context for it, as before:

using System.Data;
using System.Data.Entity;
using System.Data.EntityModel;

namespace TestEF.Contexts
{
    public class AnimalContext : DbContext
    {
        public DbSet<TestEF.Entity.Animal> Animals { get; set; }
    }
}

Add a table generation plan line in Application_Start in Global.asax as before:

    Database.SetInitializer<TestEF.Contexts.AnimalContext>(new DropCreateDatabaseAlways<TestEF.Contexts.AnimalContext>());

And a connection string line in web.config

    <add name="AnimalContext"
         connectionString="Persist Security Info=False;Initial Catalog=MySchoolDB;Server=MYSERVER/Instance;Connect Timeout=30"
         providerName="System.Data.SqlClient"  />

Now we can create the context and populate the Animal object in our code behind or MVC controller:

    using (TestEF.Contexts.AnimalContext AnimalKingdom = new TestEF.Contexts.AnimalContext())
    {
        Entity.Animal beast = new Entity.Animal();
        beast.NotedCountry = "Australia";
        beast.WarmBlooded = true;
        AnimalKingdom.Animals.Add(beast);
        AnimalKingdom.SaveChanges();
    }

When we run this we can see that, as expected, we have an Animals table with three columns, and one populated row. This is as expected with Entity Framework.

Now we can add two classes which inherit from Animal, Dog and Cat

    [Table("Dogs")]
    public class Dog : Animal
    {
        [Required]
        [StringLength(100)]
        public string Name { get; set; }

        [Required]
        [StringLength(100)]
        public string Colour { get; set; }
    }

    [Table("Cats")]
    public class Cat : Animal
    {
        [Required]
        [StringLength(100)]
        public string Breed { get; set; }

        [Required]
        [StringLength(100)]
        public string FavouriteFood { get; set; }
    }

Note on these inherited classes, we don't need to reproduce the properties of the parent class, all we need to do is tell Entity Framework what we want these tables to be called in the database by using the [Table] Data Attribute. This can also be done in Fluent API by adding the following lines to the OnModelCreating override of the Context:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Dog>().ToTable("Dogs");
    modelBuilder.Entity<Cat>().ToTable("Cats");
}

Now we can create a Dog and a Cat in our code behind or MVC controller:

    using (TestEF.Contexts.AnimalContext AnimalKingdom = new TestEF.Contexts.AnimalContext())
    {
        Entity.Dog doggy = new Entity.Dog();
        // fill in the base class properties
        doggy.NotedCountry = "England";
        doggy.WarmBlooded = true;
        // fill in subclass specific properties
        doggy.Name = "Fido";
        doggy.Colour = "Brown";

        Entity.Cat pussy = new Entity.Cat();
        // fill in the base class properties
        pussy.NotedCountry = "Ethiopia";
        pussy.WarmBlooded = true;
        // fill in subclass specific properties
        pussy.Breed = "Abyssinian";
        pussy.FavouriteFood = "Fish";

        AnimalKingdom.Animals.Add(doggy);
        AnimalKingdom.Animals.Add(pussy);

        AnimalKingdom.SaveChanges();
    }

When we examine the database we see three tables: Animal, Cats and Dogs:

Animals

Id	NotedCountry	WarmBlooded
1	England	true
2	Ethiopia	true

Dogs

Id	Name	Colour
1	Fido	Brown

Cats

Id	Breed	FavouriteFood
2	Abyssinian	Fish

We see that an Animal row has been created for each one, and a row in each of the sub-class tables with the class specific properties. The Id of the sub class table is a foreign key to the Animal table. In order to see a complete Dog object, EF must do a SQL join behind the scenes. This sort of data hierarchy is called Table-Per-Type, because each type, even if inherited from another class, gets its own table for its own properties.

And that's the basics of table-Per-Type class representation in Entity Framework.