A Beginner's Tutorial on Understanding Table Per Type(TPT) Inheritance in Entity Framework

• Download sample - 387.8 KB

Introduction

In this article we will discuss about implementing Table per Type inheritance hierarchy using Entity Framework. We will see a small sample to 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).

In this article we will try to look at the Table per Type inheritance relationship because from an efficiency standpoint this tends to be the efficient(comparatively) and this provides a great way to model the tables having one to one relationships.

Using the code

The TPT relationship is particularly very useful when we have multiple table with one to one relationships using a foreign key constraint. If we create the entities for these tables the entity framework will generate the default entities with one to one relationship where as this could be modeled better by using inheritance relationship.

Let us try to understand this concept with the help of a small example. Lets say we have a table that keeps track of Bikes data. This table will keep record of all the Bikes currently in the store. Now the shop also has two more tables for PreOwnedBikes and DiscountedBikes. For this they have created separate tables with the foreign key relationship with the original bikes table. To visualize this design:

Now from our application, If we try to generate an entity model for this design the default entity model generated by the entity framework will look like this:

This model is ok to work with but looking from the application's perspective All the PreOwnedBikes and DiscountedBikes are also Bikes. This mean that logically there is an inheritance relationship between them.

So to create the inheritance relationship, let us delete the existing relationship between entities and add inheritance relationship between them(This can be done by right clicking on the entity designer). After making these changed the inheritance relationship between the entities will look like this:

When we try to look at the mapping details we can see that the respective tables are mapped to the respective entities. We just created a logical inheritance relationship between them. 

Note: If we want all the entities present in Bikes table to be either of PreOwnedBikes or DiscountedBikes, We have to mark the Bikes entity as abstract. For now lets keep it possible to create Bike entity too i.e. It is not abstract.

Now we have entities with some 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 which in turn will update the respective tables.

Insert 

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

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

using (BikeDbEntities entities = new BikeDbEntities())
{
    PreOwnedBike bike = new PreOwnedBike { BikeName=name, Manufacturer=manf, YearsOld = years};

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

Here we are taking the input from the user and then creating a PreOwnedBike object. Then we are adding this to the Bikes collection of entities(Since we have the inheritance relationship, all PreOwnedBikes are Bikes and thus this call will take care of inserting all the data in the respective tables.

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

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

using (BikeDbEntities entities = new BikeDbEntities())
{
    DiscountedBike bike = new DiscountedBike { BikeName = name, Manufacturer = manf, DiscountRate = years };

    entities.AddToBikes(bike);
    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 an 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.

// Get the bikes 
GridView1.DataSource = entities.Bikes;
GridView1.DataBind();

// Get the bikes of type preowned bikes
GridView2.DataSource = entities.Bikes.OfType<PreOwnedBike>();
GridView2.DataBind();

// Get the bikes of type discounted bikes
GridView3.DataSource = entities.Bikes.OfType<DiscountedBike>();
GridView3.DataBind();

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

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

using (BikeDbEntities entities = new BikeDbEntities())
{
    Bike bike = entities.Bikes.SingleOrDefault<Bike>(b => b.BikeID == id);
    if (bike != null)
    {
        txtNameP.Text = bike.BikeName;
        txtManfP.Text = bike.Manufacturer;

        if (bike.GetType() == typeof(PreOwnedBike))
        {
            lblField.Text = "Years Old";
            txtFieldData.Text = ((PreOwnedBike)bike).YearsOld.ToString();
        }
        else if (bike.GetType() == typeof(DiscountedBike))
        {
            lblField.Text = "Discount Rate";
            txtFieldData.Text = ((DiscountedBike)bike).DiscountRate.ToString();
        }
    }
}

In the above code we are asking the bike's id from the user and then selecting the Bike accordingly. The important thing to note in the above code is that we are getting the details using the base class

Bike

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 bike, which we can do by using the typeof operator and then we can update the properties of the bike in the same way as we do with the normal entities.

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

using (BikeDbEntities entities = new BikeDbEntities())
{
    Bike bike = entities.Bikes.SingleOrDefault<Bike>(b => b.BikeID == id);
    if (bike != null)
    {
         bike.BikeName = txtNameP.Text;
         bike.Manufacturer = txtManfP.Text;

        if (bike.GetType() == typeof(PreOwnedBike))
        {
            ((PreOwnedBike)bike).YearsOld = Convert.ToInt32(txtFieldData.Text);
        }
        else if (bike.GetType() == typeof(DiscountedBike))
        {
            lblField.Text = "Discount Rate";
            ((DiscountedBike)bike).DiscountRate = Convert.ToInt32(txtFieldData.Text);
        }

        entities.SaveChanges();        
    }
}

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 (BikeDbEntities entities = new BikeDbEntities())
{
    Bike bike = entities.Bikes.SingleOrDefault<Bike>(b => b.BikeID == id);
    if (bike != null)
    {
        entities.DeleteObject(bike);
        entities.SaveChanges();        
    }
}

Performing all the above operations will change the data in the respective tables and will also handle the referential integrity among the tables.

Note: Please look at the sample code attached 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 Type Inheritance relationship in this article. To discuss the other relationship types, perhaps, I will write separate articles(to avoid confusion). This aritcle has been written from a beginner's perspective. I hope this has been somewhat informative.

History

• 13 February 2013: First version