Table Per Type Inheritance in Entity Framework

The concept of Table Per Type (TPT) is that for every entity it will create a new table. For example, in the following screen, there are 3 entities (Student, College Student, School Student). It will create 3 new tables for each entity.

 

 

In Table Per Hierarchy we saw that one database table stores all the data for all the entity types.

 

With this, there is the problem that when we store Collage student entity data into the table then the columns “CollageStudentName” and “CollageStudentbranch” will be left NULL and the same goes to School student.

 

For removing this problem we use Table Per Type inheritance. In this, we will create 3 tables for each entity.

 

The 3 tables are named “Student table”, “CollageStudent Table” and “SchoolStudent table”. The common thing in all the tables is that “StudentID” will be the same.

 

This StudentID will be the primary key in the Student table and will be the foreign key in the CollageStudent and SchoolStudent tables.

Create Table Student
(
StudentID int primary key,
FirstName nvarchar(50),
LastName nvarchar(50),
Gender nvarchar(50),
)
GO
Create Table CollageStudents
(
StudentID int foreign key references
Student(StudentID) not null,
CollageName nvarchar(50),
CollageBranch nvarchar(50)
)
GO
Create Table SchoolStudents
(
StudentID int foreign key references
Student(StudentID) not null,
SchoolName nvarchar(50),
SchoolClass nvarchar(50)
)
GO

Insert into Student values (1, 'Munesh', 'Sharma','Male')
Insert into Student values (2, 'Rahul', 'Sharma','Male')
Insert into Student values (3, 'Sara', 'vilium','Female')
Insert into Student values (4, 'Rani', 'hash','Female')
Insert into Student values (5, 'XYZ', 'ABC','Female')
Insert into Student values (6, 'Anshuman', 'EFG','Male')
Insert into CollageStudents values (1, 'VIT','IT')
Insert into CollageStudents values (4, 'MIT','ECE')
Insert into CollageStudents values (6, 'BTC','Mechenical')
Insert into SchoolStudents values (2, 'KVM','Seven')
Insert into SchoolStudents values (3,'Aadharsh','Eight')
Insert into SchoolStudents values (5, 'Ravat','Tenth')

Now go to your application and right-click on the Solution Explorer and select ADO.NET Entity Data Model and select your table and generate the entity. The following 3 entities will be generated:

 

 

Now delete the association between the Student entity and the CollageStudent entity and this will also automatically delete the CollageStudent navigation property from the student entity and the student navigation property from the CollageStudent entity.

 

Do the same thing with the SchoolStudent entity.

 

 

Now add the inheritance relationship between the Student entity and the collageStudent entity.

1. Right-click on the designer surface and click on the Add-Inheritance option.
    
2. Select Student at the base entity and collageStudent as the derived entity.
    
    
3. When you click on Inheritance, your screen will look as in the following. Add the base entity and derived entity using a drop-down as in the following:
    
    
4. Do the same with the Student entity and SchoolStudent entities. This means generate inheritance for these entities.
    
    
5. After creating an inheritance between these entities your entity model will look as in the following:
    
    
6. Let's understand this with an example.

<div style="font-family: Arial">
    <asp: Button ID=" Button1" runat="server" Text="All Student Information" onclick="Button1_Click" />
    <asp: Button ID=" Button2" runat="server" Text="Collage Student Information" onclick="Button2_Click" />
    <asp: Button ID=" Button3" runat="server" Text="School Student Information" onclick="Button3_Click" />
    <asp: GridView ID="GridView1" runat="server">
        </asp:GridView>
</div>
protected void Button1_Click(object sender, EventArgs e) {
    GridView1.DataSource = ConvertListToDataTable(
        studentDBContext.Students.ToList());
    GridView1.DataBind();
}
protected void Button2_Click(object sender, EventArgs e) {
    GridView1.DataSource = studentDBContext.Students.OfType < CollageStudent > ().ToList();
    GridView1.DataBind();
}
protected void Button3_Click(object sender, EventArgs e) {
    GridView1.DataSource = studentDBContext.Students.OfType < SchoolStudent > ().ToList();
    GridView1.DataBind();
}
private DataTable ConvertListToDataTable(List < Student > students) {
        DataTable dt = new DataTable();
        dt.Columns.Add("ID");
        dt.Columns.Add("FirstName");
        dt.Columns.Add("LastName");
        dt.Columns.Add("Gender");
        dt.Columns.Add("SchoolName");
        dt.Columns.Add("SchoolClass");
        dt.Columns.Add("CollageName");
        dt.Columns.Add("CollageBranch");
        dt.Columns.Add("Type");
        foreach(Student _student in students) {
            DataRow dr = dt.NewRow();
            dr["ID"] = _student.ID;
            dr["FirstName"] = _student.FirstName;
            dr["LastName"] = _student.LastName;
            dr["Gender"] = _student.Gender;
            if (_student is CollageStudent) {
                dr["CollageName"] = ((CollageStudent) _student).CollageStudentName;
                dr["CollageBranch"] = ((CollageStudent) _student).CollageStudentBranch;
                dr["Type"] = "CollageStudent";
            } else {
                dr["SchoolName "] = ((SchoolStudent) _student).SchoolStudentName;
                dr["SchoolClass "] = ((SchoolStudent) _student).SchoolStudentBranch;
                dr["Type"] = "SchoolStudent";
            }
            dt.Rows.Add(dr);
        }
        return dt;

Run your application and see the output.