Code First EF 4.1 : Table per Hierarchy

Concept of Table Per Hierarchy (TPH) is to implement polymorphism and to de-normalize the relational database via inheritance. The entire hierarchy is mapped to a single database table holding all the properties of all the classes. The database then would create a column called “[Discriminator]” to hold the specific type. In Code First this is default behavior and we do not have to do anything extra.

TPH is the simplest and possibly the fastest approach. Code First just amazingly supports it.

If we consider the below diagram of Animal, Mammal and Reptile. Here Animal is the abstract base class holding common properties.

 This finally would get converted into a table as below (notice the additional [Discrminator] column which is NOT NULL)

To have this we need the below code

 public abstract class Animal
{
    public int AnimalId { get; set; }
    public string Name { get; set; }
}

public class Reptile : Animal
{
    public double Length { get; set; }
}

public class Mammal : Animal
{
    public double Weight { get; set; }
    public bool IsMarried { get; set; }
}

public class AnimalContext : DbContext
{
    public DbSet<Animal> Animals { get; set; }
}

To add data

To add data to Reptile

using (var ctx = new AnimalContext())

{

var Rep = new Reptile() { Name = "Snake", Length = 10.0 };

ctx.Animals.Add(Rep);

ctx.SaveChanges();

To add data to Mammal

 using (var ctx = new AnimalContext())
{
    var Mam = new Mammal() { IsMarried = true, Name = "Adam", Weight = 100.0 };
    ctx.Animals.Add(Mam);
    ctx.SaveChanges();

After adding data would be saved as below

Querying data

We can query data in various ways,

 var q = from a in ctx.Animals.OfType<Mammal>()
        select a;
foreach (var k in q)
{
    Console.WriteLine(k.Name);
}

We can also use Entity SQL with “OFTYPE”.

Now the the column [Discriminator] can be altered through FluentAPI

This would create a column in the database as “AnimalType” and for each entry of Mammal add “M” and Reptile add “R”.

TPH anyways violets the third normal form and has serious data integrity issue and for a long term perspective this is not manageable. But still the simplest approach.

Namoskar!!!