A Beginner's Tutorial on Understanding Table Per Hierarchy(TPH) Inheritance in Entity Framework

• Download sample - 390.4 KB

Introduction

In this article we discuss about the Table per Hierarchy inheritance relationship using entity framework. We will see how this can be implemented in a step by step manner.

Background

There are times when our database design has tables that do not logically match with the entities that we need in our application. There could be chances that the database has too many or too few tables than the entities required logically by the application. Some other times when the tables are created one per logical entity but the relationship between them is not logical from the application and entity perspective.

Inheritance in entity framework provides a way to create the required logical entities to act on a set of database tables and also to create a more meaningful relationship between entities using inheritance.

There are three type of inheritance relationships in the entity framework. Table per type(TPT), Table per hierarchy (TPH) and Table per concrete type(TPC).

Earlier we have discussed about TPT Inheritance. Now in this article we will discuss about the Table per Hierarchy inheritance relationship i.e. TPH.

Using the code 

TPH inheritance actually keeps only one database table for all the classes in the hierarchy. This solution is not recommended from a database perspective because using this solution would require the underlying table design in such a way that it will not be normalized and infact will have a lot of redundant columns(the number of redundant columns will depend on number of derived classes, We will see that in a moment).

Now to understand this concept let us try to implement a similar project (like we did it in TPT article) but this time instead of keeping a record of Cars, lets design the solution for Cars. Lets say we have a table that keeps track of Cars data. This table will keep record of all the Cars currently in the store. Now the shop also has provision for PreOwned cars and Discounted cars. For this they have created columns in the same cars tables. To distinguish the actual car type the columns related to other type will be null i.e.

In the above table is the car is of Preowned type then the data in YearsOld column will have some value but the DiscountRate column will be null and same is the case for Discounted cars. So we can see how this is a not an efficient and normalized solution from a database perspective.

Nevertheless, Let us try to model this table using entity framework in our solution. When we add an entity data model for this table we will get the following default entity created.

Now we don't want this bad table design to propagate to our application(otherwise too many workarounds will creep up with time). So we break this into a better logical design. We create two more Entities for DicountedCars and PreOwnedCars. We will then create an inheritance relationship between then where Car will be the base entity for both these entities. Finally, we remove the respective properties from the Car entity and move them to these newly created entities. (All this can be done by using the context menu on entity designer). The resulting entities will look like:

Next thing is that we need to define the table mapping and mapping conditions for these two entities. We want the entities to push the data to their respective columns in table and will ensure that the columns pertaining to the other entities are null. The table mappings for the DiscountedCar entity:

The table mapping for the PreOwnedCar entity:

The last thing we need to do is that we need to mark the Car entity as abstract so that nobody should be able to able to create a Car type without creating the concrete type.

Now we have entities with required logical relationship and we are ready to work with these entities. Let us now see how we can perform various CRUD operations on these related entities

Insert

Let us start with the insert operation. Lets see how can we add an entity to The PreOwnedCar entity:

string name = txtNameP.Text;
string manf = txtManfP.Text;
int years = Convert.ToInt32(txtYearsP.Text);

using (CarsDbEntities entities = new CarsDbEntities())
{
    PreOwnedCar car = new PreOwnedCar { CarName = name, Manufacturer = manf, YearsOld = years };

    entities.AddToCars(car);
    entities.SaveChanges();
    Response.Redirect("Default.aspx");
}

Here we are taking the input from the user and then creating a PreOwnedCar object. Then we are adding this to the Cars collection of entities(Since we have the inheritance relationship, all PreOwnedCars are Cars and thus this call will take care of the rest.

In the same way we can insert into the DiscountedCar table too.

string name = txtNameD.Text;
string manf = txtManfD.Text;
int years = Convert.ToInt32(txtRateD.Text);

using (CarsDbEntities entities = new CarsDbEntities())
{
    DiscountedCar car = new DiscountedCar { CarName = name, Manufacturer = manf, DiscountRate = years };

    entities.AddToCars(car);
    entities.SaveChanges();
    Response.Redirect("Default.aspx");
}

Select

To select the data from the table, we can either select all the data from the table or we can specify a select criteria to select from a table. To select all the data from the respective tables we just need to use the collection properties of the Context class.

using (CarsDbEntities entities = new CarsDbEntities())
{
    GridView1.DataSource = entities.Cars;
    GridView1.DataBind();

    GridView2.DataSource = entities.Cars.OfType<PreOwnedCar>();
    GridView2.DataBind();

    GridView3.DataSource = entities.Cars.OfType<DiscountedCar>();
    GridView3.DataBind();
}

The other case where we need to select a type of Car using some search criteria we can get the results from the Cars collection and check for the actual type of entity by using the typeof operator.

int id = Convert.ToInt32(TextBox1.Text);

using (CarsDbEntities entities = new CarsDbEntities())
{
    Car car = entities.Cars.SingleOrDefault<Car>(c => c.CarID == id);
    if (car != null)
    {
        txtNameP.Text = car.CarName;
        txtManfP.Text = car.Manufacturer;

        if (car.GetType() == typeof(PreOwnedCar))
        {
            lblField.Text = "Years Old";
            txtFieldData.Text = ((PreOwnedCar)car).YearsOld.ToString();
        }
        else if (car.GetType() == typeof(DiscountedCar))
        {
            lblField.Text = "Discount Rate";
            txtFieldData.Text = ((DiscountedCar)car).DiscountRate.ToString();
        }
    }
}

In the above code we are asking the car's id from the user and then selecting the Car accordingly. The important thing to note in the above code is that we are getting the details using the base class Car and then checking its type using typeof to get the details of the derived entities.

Update and Delete

To update the record, we first need to identify the actual type of the car, which we can do by using the typeof operator and then we can update the properties of the car in the same way as we do with the normal entities.

int id = Convert.ToInt32(TextBox1.Text);

using (CarsDbEntities entities = new CarsDbEntities())
{
    Car car = entities.Cars.SingleOrDefault<Car>(c => c.CarID == id);
    if (car != null)
    {
        car.CarName = txtNameP.Text;
        car.Manufacturer = txtManfP.Text;

        if (car.GetType() == typeof(PreOwnedCar))
        {
            ((PreOwnedCar)car).YearsOld = Convert.ToInt32(txtFieldData.Text);
        }
        else if (car.GetType() == typeof(DiscountedCar))
        {
            lblField.Text = "Discount Rate";
            ((DiscountedCar)car).DiscountRate = Convert.ToInt32(txtFieldData.Text);
        }

        entities.SaveChanges();
        Response.Redirect("Default.aspx");
    }
}

In the above code we are straight away updating the details associated with the base entity and to update the details associated with the derived entities we are converting the type of appropriate type first by checking the typeof and then changing the properties of the entity.

Delete will also follow the same philosophy and instead of updating the record it will simply delete the record from the table.

int id = Convert.ToInt32(TextBox1.Text);

using (CarsDbEntities entities = new CarsDbEntities())
{
    Car car = entities.Cars.SingleOrDefault<Car>(c => c.CarID == id);
    if (car != null)
    {
        entities.DeleteObject(car);
        entities.SaveChanges();
        Response.Redirect("Default.aspx");
    }
}

Performing all the above operations will change the data in the the single Car table even though from our application we are acting upon various logical entities, physically they all are using a single Cars table for the data operations and thus Table per Hierarchy.

Note: Please look at the sample code to get the full understanding of the code. The code snippets in this article only shows the code relevant to the topic of discussion.

Point of interest 

In this small article we saw how we can create logical relationships between the entities to use them in a better way. We specifically discusses about the Table Per Hierarchy Inheritance relationship in this article. This aritcle has been written from a beginner's perspective. I hope this has been somewhat informative.

History

• 14 February 2013: First version.