LNQ to SQL with Inheritance by ThinqLinq

LNQ to SQL with Inheritance

Working on a side project extremely late last night, I found myself trying to count sheep. In particular I was interested in separating the males from the females. Using good OOP principals, I figured I could create a base class "Sheep" and inherit from that for the "Ram" and "Ewe" classes. Below is a simplified class model:

In order to map this to a LINQ to SQL class model, we can simply define the three classes, Sheep, Ram and Ewe.  We place the standard <column> attributes on the columns to map the columns. I'm attaching the full class implementation to this post. For the sake of brevity, I'll remove the implementatino details at this point. Below is the basic definition of the Sheep properties:

 <Column(IsDBGenerated:=True, IsPrimaryKey:=True, Storage:="_Id")> _
    Public ReadOnly Property Id() As Integer 

   <Column()> _
    Public Property Name() As String

    <Column(IsDiscriminator:=True)> _
    Public Property SheepType() As String

Notice in this case, we are including the "IsDiscriminator" attribute to the SheepType property. This indicates to LINQ that we will evaluate the values in the SheepType column in the database and use that to determine which kind of object implementation we want to use.

We need one other key element in this example in order to define our mappings. We need to decorate the Sheep class definition with the InheritanceMapping attributes to identify which type to use when passed various identifying codes. In our case, we want to get a Ram when the code is R, Ewe when the code is E and the base sheep type when the code is S. To do this, we add the following to our Sheep class declaration:

<Table()> _
    <InheritanceMapping(Code:="R", Type:=GetType(Ram))> _
    <InheritanceMapping(Code:="E", Type:=GetType(Ewe))> _
    <InheritanceMapping(Code:="S", Type:=GetType(Sheep), _
        IsDefault:=True)> _
Public Class Sheep

With these two elements in place, we can now query the database and return all of the sheep with the appropriate type implemented depending on the value in the SheepType column of our database as follows:

       Dim query = From s In dc.Sheep _
              Select s

This returns the following results:

Id=1    Name=Foo        SheepType=R
IsCloned=True   Id=2    Name=Dolly      SheepType=E
IsCloned=False  Id=3    Name=Sue        SheepType=E

Notice, for the sheep named "Foo" which is a Ram, the IsCloned property is not enumerated, but for the Ewe's it was mapped successfully. If we want to go a step further, we can eliminate Foo from the Ewe's by only fetching the objects that are of type Ewe as follows:

       Dim query = From s In dc.Sheep _
              Where TypeOf  s Is Ewe _
              Select s

This returns the following results:

IsCloned=True   Id=2    Name=Dolly      SheepType=E
IsCloned=False  Id=3    Name=Sue        SheepType=E

Notice, we are now excluding our Ram (named Foo). For those of you interested, LINQ to SQL generates the following query to the database:

SELECT [t0].[SheepType], [t0].[Id], [t0].[Name], [t0].[IsCloned]
FROM [Sheep] AS [t0]
WHERE ([t0].[SheepType] = @p0) AND ([t0].[SheepType] IS NOT NULL)

As an alternative, we can also define the query  as follows, eliminating the "Where" clause.

        Dim query = From s As Ewe In dc.Sheep _
               Select s

In this case, however, all of the records are returned from the database. As we iterate over the collection, we return a null item in place of the Ram.

So, as you can see it is relatively easy to use LINQ to SQL to work with inheritance structures as long as the data is all represented in a single table (or view). This sample shows how easily it is to use LINQ to separate FOO from the EWES.

Either that, or I'm just FOO LINQ EWE!!!  [;)]
(I need to get more sleep before posting again. Time to continue counting sheep.)

NB: This code actually works with the March CTP. Try it yourself.

Posted on - Comment
Categories: LINQ) -
comments powered by Disqus