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.