Part 11 - Single table inheritance in linq to sql

Suggested Videos
Part 8 - Lazy loading in LINQ to SQL
Part 9 - Eager loading in LINQ to SQL
Part 10 - Difference between eager loading and lazy loading



With single table inheritance one database table is used to store data for all of the entity types in the entire inheritance hierarchy. Let us understand this with an example.



We will be using the following Employees table.


SQL Script to create Employees Table

Create Table Employees

(

     ID int primary key identity,

     Name nvarchar(50),

     Gender nvarchar(50),

     AnuualSalary int,

     HourlyPay int,

     HoursWorked int,

     Discriminator nvarchar(50)

)

GO

Insert into Employees values ('Mark', 'Male', 60000, NULL, NULL, 'PermanentEmployee')

Insert into Employees values ('Steve', 'Male', NULL, 50, 160, 'ContractEmployee')

Insert into Employees values ('Ben', 'Male', NULL, 40, 120, 'ContractEmployee')

Insert into Employees values ('Philip', 'Male', 45000, NULL, NULL, 'PermanentEmployee')

Insert into Employees values ('Mary', 'Female', 30000, NULL, NULL, 'PermanentEmployee')

Insert into Employees values ('Valarie', 'Female', NULL, 30, 140, 'ContractEmployee')

Insert into Employees values ('John', 'Male', 80000, NULL, NULL, 'PermanentEmployee')

In our organization we have 2 types of employees
1. Permanent Employees - AnuualSalary is specific for Permanent Employees
2. Contract Employees - HourlyPay & HoursWorked is specific for Contract Employees

By default LINQ to SQL creates the following Employee entity class, based on Employees table


But from an application perspective we want 3 entity classes
Employee - This should be an abstract class and should contain all the common properties of permanent and contract employees. ID, Name & Gender properties should be present in this class.

PermanentEmployee - This class should inherit from the abstract Employee class and should contain AnnualSalary property

ContractEmployee - This class should inherit from the abstract Employee class and should contain HourlyPay & HoursWorked properties

To achieve this using the LINQ to SQL class designer
1. Right click on the designer surface and select Add - Class option. Change the class name to PermanentEmployee.

2. Cut AnnualSalary property from Employee class and paste it in PermanentEmployee class.

3. Right click on the designer surface and select Add - Class option. Change the class name to ContractEmployee.

4. Cut HourlyPay & HoursWorked properties from Employee class and paste them in ContractEmployee class.

5. Right click on PermanentEmployee and select Add - Inheritance option. In the "New Inheritance" window select "Employee" class from "Select a base class" dropdownlist and "PermanentEmployee" class from "Select a derived class" dropdownlist. This should create an inheritance relationship between Employee and PermanentEmployee classes.

6. Along the same lines, Right click on ContractEmployee and select Add - Inheritance option. In the "New Inheritance" window select "Employee" class from "Select a base class" dropdownlist and "ContractEmployee" class from "Select a derived class" dropdownlist. This should create an inheritance relationship between Employee and ContractEmployee classes. At this point, the three classes should be as shown below.


7. Right click on the inheritance relationship arrow(that connects Employee and PermanentEmployee classes) and select "Properties". Set the properties of the inheritance relationship as shown below.


8. Along the same lines, right click on the inheritance relationship arrow(that connects Employee and ContractEmployee classes) and select "Properties". Set the properties of the inheritance relationship as shown below.


9. Finally right click on Employee class and select properties. In the properties window set Inheritance Modifier = abstract. This should make Employee class an abstract class. 

Now, let us see how to query the data. Design a webform as shown below. 


Here is the HTML for the web form.

<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>

</div>

Here is the code behind code:

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

namespace Demo

{

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

    {

        protected void Page_Load(object sender, EventArgs e)

        { }

        private DataTable ConvertEmployeesForDisplay(List<Employee> employees)

        {

            DataTable dt = new DataTable();

            dt.Columns.Add("ID");

            dt.Columns.Add("Name");

            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.ID;

                dr["Name"] = employee.Name;

                dr["Gender"] = employee.Gender;

                if (employee is PermanentEmployee)

                {

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

                    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 RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e)

        {

            SampleDataContext dbContext = new SampleDataContext();

            dbContext.Log = Response.Output;

            switch (RadioButtonList1.SelectedValue)

            {

                case "Permanent":

                    GridView1.DataSource =

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

                    GridView1.DataBind();

                    break;

                case "Contract":

                    GridView1.DataSource =

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

                    GridView1.DataBind();

                    break;

                default:

                    GridView1.DataSource =

                        ConvertEmployeesForDisplay(dbContext.Employees.ToList());

                    GridView1.DataBind();

                    break;

            }

        }

    }

}

Run the application and inspect the queries that are generated.

When Load All Employees radio button is selected:

SELECT [t0].[Discriminator], [t0].[HourlyPay], [t0].[HoursWorked], [t0].[ID], [t0].[Name], [t0].[Gender], [t0].[AnuualSalary] FROM [dbo].[Employees] AS [t0]

When Load Permanent Employees radio button is selected:

SELECT [t0].[Discriminator], [t0].[AnuualSalary], [t0].[ID], [t0].[Name], [t0].[Gender] FROM [dbo].[Employees] AS [t0] WHERE ([t0].[Discriminator] <> @p0) OR ([t0].[Discriminator] IS NULL) -- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [ContractEmployee]

When Load Contract Employees radio button is selected:

SELECT [t0].[Discriminator], [t0].[HourlyPay], [t0].[HoursWorked], [t0].[ID], [t0].[Name], [t0].[Gender] FROM [dbo].[Employees] AS [t0] WHERE ([t0].[Discriminator] = @p0) AND ([t0].[Discriminator] IS NOT NULL) -- @p0: Input NVarChar (Size = 4000; Prec = 0; Scale = 0) [ContractEmployee]