Home > dotnet 4, Entity Framework > EF Code First: Table Per Hierarchy

EF Code First: Table Per Hierarchy

Continuing with my previous blog post about Entity framework today I am going to show you how to use Table Per Hierarchy relationship with Code First Approach.

It’s again a very useful mapping scenario and can be used for filtering specific types of domain objects.I am going to use a very simple real life scenario like a Person table and with that we have 2 derived types Players and Coaches, I know this may not be the best example for this kind of scenario but for illustration purpose and keep the example as simple as possible.

Lets first start with a domain model where we want to conceptually map 2 derived entities to a base entity.

Player Coach Class Diagram

and the code for 3 classes are as following :-

public abstract class Person
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public DateTime DateOfBirth { get; set; }
public class Player : Person
    public int SportId { get; set; }
    public int NoOfGames { get; set; }
public class Coach : Person
    public DateTime ContractStartDate { get; set; }
    public DateTime ContractEndDate { get; set; }

and this is how we define the relationship in the configuration file.

public class PersonConfiguration : EntityTypeConfiguration<Person>
    public PersonConfiguration()
        this.Map<Player>(x => x.Requires("PersonType").HasValue("P"))
            .Map<Coach>(x => x.Requires("PersonType").HasValue("C"));

and now all we have to do is add the configuration class to the OnModelCreating method of our POCOContext class.

modelBuilder.Configurations.Add(new PersonConfiguration());

Lets write a unit test to make sure the database is getting created and the table are generated with the right column.

public void Should_be_able_to_create_POCOContext_database()
    var target = new POCOContext();

and this is how our People table looks like.

People table structure

So far so good but the best part is the data is flat combined structure in the database but yet in our domain model it is hierarchical and we can treat even the saving or retrieval of data in terms of domain model and not like treating it a data structure and separating the two kinds of Person with just types.

Lets write some more code to see it in action, first we will try adding a new Player into the table.

public void Should_be_able_add_a_player()
    var player = new Player
                    FirstName = "Joe",
                    LastName = "Blogg",
                    SportId = 1,
                    NoOfGames = 120,
                    DateOfBirth = new DateTime(1980, 12, 12)



and lets add a coach as well.

public void Should_be_able_add_a_coach()
    var coach = new Coach
                    FirstName = "Joe",
                    LastName = "Blogg",
                    DateOfBirth = new DateTime(1960, 1, 2),
                    ContractStartDate = new DateTime(2009, 12, 1),
                    ContractEndDate = new DateTime(2012,12,1)

As you can see it can’t be easier than this and the power of this feature is then you are retrieving records of either a player or a coach type.

public void Should_be_able_to_get_Players()
    var players = from p in context.Person.OfType<Player>()
                select p;

    var result = players.ToList();

By just using OfType on the Person Entity the EF is doing the filtering of records based on the type and it’s doing all hard work. Hope you guys find it quite handy and use it in your project.

  1. David Wick
    April 8, 2011 at 2:51 pm

    The title is correct but the body of the article says its table per type.

    • April 8, 2011 at 9:08 pm

      Thanks David for pointing out the mistake and I have corrected it.

  2. avtobazar
    April 15, 2011 at 7:55 am

    Thanks – where is article source?

  3. December 26, 2011 at 12:43 pm

    My brother recommended I may like this website. He was totally right. This publish actually made my day. You can not believe simply how so much time I had spent for this information! Thank you!

  1. April 11, 2011 at 9:06 pm

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 )

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: