Part 21 - Table Per Type (TPT) inheritance in entity framework code first

Suggested Videos
Part 18 - Table Per Hierarchy (TPH) inheritance in entity framework
Part 19 - Table Per Hierarchy (TPH) inheritance in entity framework with code first
Part 20 - Table Per Type (TPT) inheritance in entity framework



In this video, we will discuss implementing Table Per Type (TPT) inheritance in entity framework with code first approach. This is continuation to Part 20, where we discussed implementing Table Per Type inheritance with database first approach. Please watch Part 20 before proceeding.



In TPT inheritance one database table per type is used to store data for the respective entity types in the inheritance hierarchy. 

Step 1: Create a new empty asp.net web application project. Name it Demo. Install entity framework if it's not already installed.

Step 2: Add a class file to the project. Name it Employee.cs. Copy and paste the following code.

using System.ComponentModel.DataAnnotations.Schema;

namespace Demo

{

    [Table("Employees")]

    public class Employee

    {

        public int EmployeeID { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

        public string Gender { get; set; }

    }

}

Step 3: Add a class file to the project. Name it PermanentEmployee.cs. Copy and paste the following code.

using System.ComponentModel.DataAnnotations.Schema;

namespace Demo

{

    [Table("PermanentEmployees")]

    public class PermanentEmployee : Employee

    {

        public int AnnualSalary { get; set; }

    }

}

Step 4: Add a class file to the project. Name it ContractEmployee.cs. Copy and paste the following code.

using System.ComponentModel.DataAnnotations.Schema;

namespace Demo

{

    [Table("ContractEmployees")]

    public class ContractEmployee : Employee

    {

        public int HoursWorked { get; set; }

        public int HourlyPay { get; set; }

    }

}

Step 5: Add a class file to the project. Name it EmployeeDBContext.cs. Copy and paste the following code.

using System.Data.Entity;

namespace Demo

{

    public class EmployeeDBContext : DbContext

    {

        public DbSet<Employee> Employees { get; set; }

    }

}

Step 6: Add the database connection string in web.config file.

<connectionStrings>

  <add name="EmployeeDBContext"

            connectionString="server=.; database=Sample; integrated security=SSPI;"

            providerName="System.Data.SqlClient" />

</connectionStrings>

Step 7: Add a webform to the project. Copy and paste the following HTML.

<div style="font-family: Arial">

    <asp:RadioButtonList ID="RadioButtonList1" runat="server" AutoPostBack="True"

        OnSelectedIndexChanged="RadioButtonList1_SelectedIndexChanged">

        <asp:ListItem Text="Load all Employees" Value="All"></asp:ListItem>

        <asp:ListItem Text="Load Permanent Employees" Value="Permanent"></asp:ListItem>

        <asp:ListItem Text="Load Contract Employees" Value="Contract"></asp:ListItem>

    </asp:RadioButtonList>

    <asp:GridView ID="GridView1" runat="server">

    </asp:GridView>

    <br />

    <asp:Button ID="btnAddPermanentEmployee" runat="server" 

        Text="Add Permanent Employee"

        OnClick="btnAddPermanentEmployee_Click" />

    <br />

    <br />

    <asp:Button ID="btnAddContractEmployee" runat="server" Text="Add Contract Employee"

        OnClick="btnAddContractEmployee_Click" />

</div>

Step 8: Copy and paste the following code in the code-behind file.

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

namespace Demo

{

    public partial class WebForm1 : System.Web.UI.Page

    {

        protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)

        {

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            switch (RadioButtonList1.SelectedValue)

            {

                case "Permanent":

                    GridView1.DataSource = employeeDBContext.Employees

                             .OfType<PermanentEmployee>().ToList();

                    GridView1.DataBind();

                    break;

                case "Contract":

                    GridView1.DataSource = employeeDBContext.Employees

                             .OfType<ContractEmployee>().ToList();

                    GridView1.DataBind();

                    break;

                default:

                    GridView1.DataSource = ConvertEmployeesForDisplay(employeeDBContext.Employees.ToList());

                    GridView1.DataBind();

                    break;

            }

        }

        private DataTable ConvertEmployeesForDisplay(List<Employee> employees)

        {

            DataTable dt = new DataTable();

            dt.Columns.Add("ID");

            dt.Columns.Add("FirstName");

            dt.Columns.Add("LastName");

            dt.Columns.Add("Gender");

            dt.Columns.Add("AnuualSalary");

            dt.Columns.Add("HourlyPay");

            dt.Columns.Add("HoursWorked");

            dt.Columns.Add("Type");

            foreach (Employee employee in employees)

            {

                DataRow dr = dt.NewRow();

                dr["ID"] = employee.EmployeeID;

                dr["FirstName"] = employee.FirstName;

                dr["LastName"] = employee.LastName;

                dr["Gender"] = employee.Gender;

                if (employee is PermanentEmployee)

                {

                    dr["AnuualSalary"] = ((PermanentEmployee)employee).AnnualSalary;

                    dr["Type"] = "Permanent";

                }

                else

                {

                    dr["HourlyPay"] = ((ContractEmployee)employee).HourlyPay;

                    dr["HoursWorked"] = ((ContractEmployee)employee).HoursWorked;

                    dr["Type"] = "Contract";

                }

                dt.Rows.Add(dr);

            }

            return dt;

        }

        protected void btnAddPermanentEmployee_Click(object sender, EventArgs e)

        {

            PermanentEmployee permanentEmployee = new PermanentEmployee

            {

                FirstName = "Mike",

                LastName = "Brown",

                Gender = "Male",

                AnnualSalary = 70000,

            };

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            employeeDBContext.Employees.Add(permanentEmployee);

            employeeDBContext.SaveChanges();

        }

        protected void btnAddContractEmployee_Click(object sender, EventArgs e)

        {

            ContractEmployee contractEmployee = new ContractEmployee

            {

                FirstName = "Stacy",

                LastName = "Josh",

                Gender = "Female",

                HourlyPay = 50,

                HoursWorked = 120

            };

            EmployeeDBContext employeeDBContext = new EmployeeDBContext();

            employeeDBContext.Employees.Add(contractEmployee);

            employeeDBContext.SaveChanges();

        }

    }

}

Step 7: If you already have Sample database in SQL Server. Delete it from SQL Server Management Studio.

Step 8: Run the application. Sample database and the required tables (Employees, PermanentEmployees & ContractEmployees) must be created at this point. 

Step 9: Insert test data using the following SQL script

-- Employees Table Insert

Insert into Employees values ('Mark', 'Hastings', 'Male')

Insert into Employees values ('Steve', 'Pound', 'Male')

Insert into Employees values ('Ben', 'Hoskins', 'Male')

Insert into Employees values ('Philip', 'Hastings', 'Male')

Insert into Employees values ('Mary', 'Lambeth', 'Female')

Insert into Employees values ('Valarie', 'Vikings', 'Female')

Insert into Employees values ('John', 'Stanmore', 'Male')

GO

-- PermanentEmployees Table Insert

Insert into PermanentEmployees values (1, 60000)

Insert into PermanentEmployees values (3, 45000)

Insert into PermanentEmployees values (4, 30000)

Insert into PermanentEmployees values (7, 80000)

GO

-- ContractEmployees Table Insert

Insert into ContractEmployees values (2, 50, 160)

Insert into ContractEmployees values (5, 40, 120)

Insert into ContractEmployees values (6, 30, 140)

GO

Step 10: Reload the webform. Open SQL Profiler. Select the different radio buttons to load contract, permanent & all employees and notice the queries generated by the entity framework. Also click on the following buttons.
a) Add Permanent Employee
b) Add Contract Employee

Notice that entity framework automatically inserts records into the correct underlying tables based on the type of Employee (PermanentEmployee or ContractEmployee) being inserted.

Note: You can also use Fluent API to do the table mapping by overriding OnModelCreating() as shown below.

public class EmployeeDBContext : DbContext

{

    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)

    {

        modelBuilder.Entity<ContractEmployee>().ToTable("ContractEmployees");

        modelBuilder.Entity<PermanentEmployee>().ToTable("PermanentEmployees");

        base.OnModelCreating(modelBuilder);

    }

}