Linq to SQL Inheritance

1 באוקטובר 2007

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









 Linq to SQL Inheritance  Linq to SQL Inheritance
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.

Add comment
facebook linkedin twitter email

8 comments

  1. Charles Lazarr1 באוקטובר 2007 ב 19:52

    Minor correction… the first predicate in the WHERE clause in the above SQL should compare PersonType to the value 2 so that both Programmer and SalesPerson object instances are returned for the Employee query.

  2. Michael DePouw4 בספטמבר 2008 ב 11:52

    When I try to view " How To: Model Inheritance in Databases " I'm prompted for login information. So I try to create an account, it complains that my username has to be less than 10 chars, come on, is this 1982 or 2008? Why isn't that site linked up with Live?

    Anyways, thought I share my experience with you about trying to read your post on Inheritance in a relational DB.

    Regards,
    Mike D
    spottedmahn@gmail.com

  3. Michael DePouw4 בספטמבר 2008 ב 12:02

    Thanks for the post.

    I'm interested to know how I would implement inheritance using the DBML diagram tool. When you mention adding attirbutes to the class that's a problem because that file is generated by using the GUI tool.

    How do we implement inheritance using the GUI tool?

    Thanks,
    Mike D.
    spottedmahn@gmail.com

  4. Michael DePouw8 בספטמבר 2008 ב 10:00

    I found a Microsoft Walkthrough using the GUI tool: http://msdn.microsoft.com/en-us/library/bb384467.aspx

  5. Khaled30 בספטמבר 2008 ב 0:20

    Instead of using generic methods inside the Linq query, you can use this simpler syntax:

    var query = from p in ctx.Person
    where p is Employee
    select p;

    foreach (Employee emp in query) {
    // Do something with employees
    }

  6. Raghunath1 במאי 2009 ב 14:09

    Can you please tell associations also in Linq to Sql inheritance

  7. Funka!18 במאי 2009 ב 21:32

    The first link in this article ("How To: Model Inheritance in Databases") appears to be a private link?

  8. Funka!18 במאי 2009 ב 21:34

    Sorry to complain but there is another broken link in the second section, "Linq to SQL Attribute Based Mapping".

Comments are closed.