Tutorial 9: Defining Data Model Classes (Model Inheritance)

Description Article
Table of Contents
Summary

Data model classes are normally definied following the strategy one classe-one table.

An aternative strategy of modeling is to have an entity classes inheritance to be saved on the same table: a discriminator table column indicates what class type each record represents.

The table per class hierarchy strategy can be implemented using both DataAnnotation (Attributes) and XML mapping.

Class hierarchy and correspondent data table

Operations
Implement Table per Class Hierarchy strategy using DataAnnotation (Attributes)

The discriminator column must be configured.

According the column value, the framework can verify which type is represented by each record and which type should be instantiated according to the discriminator codes

Remarks

The default class must be set as default. This is used by the framework when it finds a code that is not defined in the considered attributes.

How to do it
[Table(Name="People")]
[InheritanceMapping(Code = 1, Type = typeof(Contact), IsDefault=true)]
[InheritanceMapping(Code = 3, Type = typeof(SalesPerson))]
[InheritanceMapping(Code = 2, Type = typeof(Programmer))]
public abstract class Person
{
	[Column]
	public int PersonID { get; set; }

	[Column(IsDiscriminator=true)]
	public int PersonType { get; set; }

	...
}
Implement Table per Class Hierarchy strategy using XML mapping

The root level class (“Person”) has a column marked as discriminator.

Each “Type” element can contain child “Type” elements that represent the derived concrete types. Each of this nodes has an “InheritanceCode” attribute that informs the framework which type to instantiate.

Remarks

The default class must be set as default through the “IsInheritanceDefault” node attribute. This is used by the framework when it finds a code that is not defined in the considered “InheritanceCode” node attribute

How to do it
<?xml version="1.0" encoding="utf-8" ?>
<Database Name="TablePerClassHierarchy"
          xmlns="http://schemas.microsoft.com/linqtosql/mapping/2007">

  <Table Name="dbo.People">
    <Type Name="Person">
      <Column Name="PersonID" Member="PersonID" />
      <Column Name="PersonType" Member="PersonType" IsDiscriminator="true" />
      <Column Name="FirstName" Member="FirstName" />
      <Column Name="LastName" Member="LastName" />
      <Column Name="BirthDate" Member="BirthDate" />

      <Type Name="Contact" InheritanceCode="1" IsInheritanceDefault="true">
        <Column Name="EmailAddress" Member="EmailAddress" />
        <Column Name="Phone" Member="Phone" />
      </Type>

      <Type Name="Employee" IsInheritanceDefault="true">
        <Column Name="JobTitle" Member="JobTitle" />
        <Column Name="HireDate" Member="HireDate" />
        <Column Name="Salary" Member="Salary" />

        <Type Name="SalesPerson" InheritanceCode="3">
          <Column Name="Bonus" Member="Bonus" />
          <Column Name="SumSales" Member="SumSales" />
        </Type>

        <Type Name="Programmer" InheritanceCode="2">
          <Column Name="ProgLanguage" Member="ProgLanguage" />
          <Column Name="ProgLevel" Member="ProgLevel" />
        </Type>
      </Type>
    </Type>
  </Table>
</Database>
Querying the database (fitering at memory level)

The framework looks at the inheritance and when querying the database instantiates the appropriate class types according the discriminator code.

To filter only specific types the the “is” operator can be used.

Remarks

The problem with this approach is that the if statement that filters the required type is executed in the client side

How to do it
DataContext dc = new DataContext(@"...");

var query = from p in dc.GetTable<Person>()
            select p;

foreach (Person p in query)
{
	if ( p is Employee ) {
	...
	}
}
SELECT [t0].[PersonType], [t0].[EmailAddress], [t0].[Phone], [t0].[PersonID], [t0].[FirstName],
           [t0].[LastName], [t0].[BirthDate], [t0].[Bonus], [t0].[SumSales], [t0].[JobTitle], [t0].[HireDate],
           [t0].[Salary], [t0].[ProgLanguage], [t0].[ProgLevel]
FROM    [dbo].[People] AS [t0]
Querying the database (fitering at database level)

The framework looks at the inheritance amp and when querying the database instantiates the appropriate class types according the discriminator code.

In order to filter the types at the database level the “OfType()” method must be use: this way the “WHERE” clause will contain the relevant inheritance code(s).

How to do it
var query = from p in dc.GetTable<Person>().OfType<Employee>()
            select p;

foreach (Person p in query)
{
 ...
}
SELECT [t0].[PersonType], [t0].[Bonus], [t0].[SumSales], [t0].[JobTitle], [t0].[HireDate], [t0].[Salary],
           [t0].[PersonID], [t0].[FirstName], [t0].[LastName], [t0].[BirthDate], [t0].[ProgLanguage],
           [t0].[ProgLevel] FROM [dbo].[People] AS [t0]
WHERE ([t0].[PersonType] = 3) OR ([t0].[PersonType] = 2)