LINQ to SQL has been gaining popularity since its release last year, and if blogs and forum discussions are any indicator, developers are really pleased with this technology and have been putting it to use in novel ways.

At the same time, I’ve seen questions around features and functionality that LINQ to SQL doesn’t offer—one of them being support for inheritance models other than TPH (Table per Hierarchy). Before I discuss that further, I will reiterate some of my comments from an earlier post, specifically, around how LINQ to SQL compares with the Entity Framework.

You can find a fair amount of information on the capabilities of LINQ to SQL versus those of the Entity Framework, but let me just say that each technology has specific strengths that make it more appealing in some scenarios over the other. For example, LINQ to SQL would be your best bet in a RAD-like environment with SQL Server as the storage back-end. In Enterprise scenarios that demand more flexibility, the Entity Framework would be the technology of choice.

Inheritance Hierarchy and O/R Mapping

As you may already know, there are three primary strategies for mapping object hierarchies to tables in a relational store.

TPH

In a Table per Hierarchy model, an entire class hierarchy is mapped to one table that contains the union of the attributes from all classes. A discriminator column identifies the object type. Advantages of this approach include simplicity and ease of querying, given that all data resides in one table. On the other hand, this approach does not work well when the classes in the hierarchy change frequently, requiring corresponding changes to the table.

TPT

The Table per Type approach, each class (including abstract classes) is mapped to a table in the database. Data integrity is maintained by introducing foreign keys – often a primary key which is also a foreign key. While this approach most closely resembles the object hierarchy, it has the disadvantage that you have to join several tables for querying and updates.

TPCT

In a Table per Concrete Type strategy, a table is created for each concrete class, with columns corresponding to the attributes of the class as well as those inherited from any abstract bases. I like to think of it as the hybrid approach, where you get some of the advantages of TPH (easier querying) but have to deal with having to update multiple tables corresponding to subclasses of a class that needs to be changed frequently.

If you find yourself interested in more on O/R mapping, here’s an excellent article I can recommend: http://www.ibm.com/developerworks/library/ws-mapping-to-rdb/.

LINQ to SQL and the Entity Framework

While LINQ to SQL only supports TPH, the Entity Framework offers all three mapping approaches, and should be the framework of choice when it comes to developing enterprise applications with flexible mapping requirements.

Having said that, I wrote the sample application that demonstrates how one can model TPT entities in a LINQ to SQL context using the built-in TPH support and a bit of T-SQL code. The sample is an exercise in exploring different options, and not necessarily a design recommendation. I hope, though, that some of you will find it useful, if only in a didactic sense.

Overview of the Application

First, an aside (and a shameless plug): The sample app I wrote is inspired by an application I used to work on—it ships as part of Microsoft Office, and you can check it out at http://www.ideawins.com.

Consider a hierarchy of classes captured by the following UML diagram.

clip_image002

Note that only the leaf classes are concrete—the base classes Account, FinancialAccount, and BankAccount are abstract. Now let us assume we wish to reflect this hierarchy in the database. How would we do it? A logical structure in the spirit of TPT would look like the following.

clip_image004

Note here that the primary keys in the child classes also serve as the foreign keys into parent classes. In fact, there really is only one key per row in each “leaf table.” This schema is about as close as you can get to the original class hierarchy.

Bringing it all together

The glue that will allow us to access a TPT-style logical layer with a LINQ to SQL DataContext is a view. The view definition will be a union of rows from the two tables corresponding to the concrete classes, joined to the base tables for the inherited attributes.

CREATE VIEW dbo.ACCOUNTSVIEW AS

SELECT [t0].ID,

[t0].ACCOUNTNUMBER,

[t0].ACCOUNTNAME,

[t1].BALANCE,

[t2].BANKNAME,

[t2].ACCOUNTHOLDER,

NULL AS CREDITLIMIT,

'H' AS Tag

FROM ACCOUNT AS [t0]

INNER JOIN FINANCIALACCOUNT AS [t1] ON [t0].ID = [t1].ID

INNER JOIN BANKACCOUNT AS [t2] ON [t0].ID = [t2].ID

INNER JOIN CHECKINGACCOUNT AS [t3] ON [t0].ID = [t3].ID

UNION

SELECT [t0].ID,

[t0].ACCOUNTNUMBER,

[t0].ACCOUNTNAME,

[t1].BALANCE,

[t2].BANKNAME,

[t2].ACCOUNTHOLDER,

[t3].CREDITLIMIT,

'C' AS Tag

FROM ACCOUNT AS [t0]

INNER JOIN FINANCIALACCOUNT AS [t1] ON [t0].ID = [t1].ID

INNER JOIN BANKACCOUNT AS [t2] ON [t0].ID = [t2].ID

INNER JOIN CREDITCARDACCOUNT AS [t3] ON [t0].ID = [t3].ID

 

Note that the view includes a discriminator column (‘Tag’) that doesn’t exist in any base table. Now it’s a straightforward matter of mapping this view as a TPH entity to the Account class hierarchy in the application. Once you do that, you can query the database and see that the objects are materialized as expected.

Updates

But we’re not done yet…what about updates? If you try to create new CheckingAccount or CreditCardAccount entities and submit them, SQL Server will complain saying that the view is not updatable.

This is the second piece of the puzzle we must resolve, and this is where you roll up your sleeves and write some T-SQL code to work around the limitation. Specifically, an INSTEAD OF INSERT trigger (included in the SQL script with the project) will intercept attempts to insert rows against the view and redirect them to the appropriate base tables. Please refer to the sample code for details.

Prerequisites: Building and Running the Sample

This sample is available with complete source code—I’d encourage you to make changes and play with the application. The app was built using Visual Studio 2008 and SQL Server 2005 Express Edition.

The Visual Studio solution for the sample contains:

  • TPT: This is the (main) console application project.
  • Accounts.bak: A backup of the database that you can restore on your SQL Server installation—it contains some minimal seed data.
  • Accounts.sql: The SQL code used to create the database, the various tables, the view and the trigger.

The connection string for the database is stored in the application configuration file—you’ll have to edit it in accordance with your server installation. You can download the sample from http://code.msdn.com/TPT.

Limitations

While the technique illustrated in the sample application is interesting, there are some drawbacks. First, it won’t work in an environment where the DBA locks down the database and developers aren’t allowed to make any changes to the schema. So if you cannot create views and triggers, this technique is of no use. Secondly, since SQL Server CE does not support views and triggers, this mechanism cannot be employed on that platform.

Feedback

I’d be interested in finding out what kind of samples and or documentation you’d like to see, both for the Entity Framework as well as LINQ to SQL. If you have ideas, drop me a line at linq@live.com.