Linq to SQL Inheritance
Linq to SQL Inheritance
If you read my post How To: Model Inheritance in Databases you'd be familiar with the three common strategies of modeling inheritance in relational databases. Linq to SQL, as an Object Relational Mapping technology, supports only the Table per Class Hierarchy strategy. This means that all levels in the inheritance hierarchy are stored in the same table, and a discriminator column tells what class a record represents.
If we take a look at the sample from the previous post - Assuming that the class hierarchy should look like Listing 1 and that according to the Table per Class Hierarchy strategy, it is all stored in a single table (Listing 2):
 |
 |
| Listing 1: Class Hierarchy |
Listing 2: Storage Table |
How do we map this in Linq to SQL Inheritance?
Using Attributes Based Mapping
Using Linq to SQL Attribute Based Mapping, we start from the top level class Person:
[Table(Name="People")]
public abstract class Person
{
[Column]
public int PersonID { get; set; }
[Column]
public int PersonType { get; set; }
...
}
First, we should tell the framework which column is the discriminator. According to this column's values, the framework could tell which type is represented by each record. In this example, the PersonType column is the discriminator:
[Column(IsDiscriminator=true)]
public int PersonType { get; set; }
To tell the framework which type should be instantiated according to the discriminator codes, we use Linq to SQL InheritanceMapping Attribute above the root level class:
[Table(Name="People")]
[InheritanceMapping(Code = 1, Type = typeof(Contact))]
[InheritanceMapping(Code = 3, Type = typeof(SalesPerson))]
[InheritanceMapping(Code = 2, Type = typeof(Programmer))]
public abstract class Person
{
...
}
One important thing, is that you must declare one of the Inheritance Mappings as default. This is used by the framework when it finds a code that is not defined in the above attributes. The problem I ran into when writing this post is that my root level class is an abstract class, and therefore cannot be the default Inheritance Mapping (I got an exception trying that). On the other hand, It is not logically true to make one of the other levels as the default one.
[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
{
...
}
Using Xml Based Mapping
Using Linq to SQL Xml Based Mapping, we specify the class hierarchy below the table in which they are all stored. Each TypeMapping element can contain child TypeMapping elements that represent derived types. Notice that the Person type has Contact and Employee types as child elements and very similar - Programmer and SalesPerson are child types of Employee.
<?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>
Again, the root level class (Person) has a column marked as discriminator. Each concrete table has an InheritanceCode attribute that lets the framework know which type to instantiate, and one type must be marked as the default.
Querying the Database
The framework looks at the inheritance mapping (Xml or attributes) and when querying the database, instantiates the appropriate types according to the discriminator code. Then we can the is operator to filter only the types we want.
DataContext ctx = new DataContext(@"...");
var query = from p in ctx.GetTable<Person>()
select p;
foreach (Person p in query)
{
if ( p is Employee )
// Do something with employees
}
Executing this query results in the following SQL statement sent to the database:
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]
The problem with this approach is that the if statement that filters the type to work with is executed in the client which causes many unneeded rows to be retrieved and sent to the client. In order to filter the types in the database we can use the OfType<T>() method:
var query = from p in ctx.GetTable<Person>().OfType<Employee>()
select p;
foreach (Person p in query)
{
// Do something with employees
}
which results in an addition of a where clause that has the relevant inheritance codes.
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)
Conclusion
Linq to SQL Inheritance supports Table per Class Hierarchy strategy that can be mapped both in Xml Based Mapping and Attribute Based Mapping. It has the OfType<T> extension method to allow filtering the query according to the inheritance hierarchy.