Fitting Linq To Sql into a real world datalayer

Let me first start off by saying that I love Linq. I think that idea of injecting set based logic as a language feature is genius. I believe that we will start to see even more advantages of this when we start doing more parallel programming. But right now Linq has the ability to simplify a lot of code that we have to write in order to filter and manipulate in-memory classes. So what would be better than to apply this directly to a database? Well…nothing. The ability to have strongly typed queries that can persist data into POCOs is actually a pretty appealing proposition, so what is wrong?

Well, I was trying to do a very simple setup that is quite common among business application developers. I was trying to create a business entity base object that all of my business objects descend from. In this instance I have created a simple scenario that looks like this:

In this instance the BusinessBase would have any common properties like "Id" or a property like "LastUpdated" which tracks the last insertion in the database. Usually this class will also contain any methods that you don’t want duplicated across all of your business objects. So, you would think that this kind of setup would be quite easy for Linq to Sql, but it isn’t. Linq to Sql only has one concept of inheritance, and that is table inheritance. Table inheritance is if you had a class heirarchy that looks like this:

Then all of these classes would be stored in a single table with a column called something like VehicleType. Then you could query out a Truck and LinqToSql could look for only that type by using the VehicleType column. This type of inheritance is very useful for mapping to tables, but what about the inheritance that I showed earlier where it has no effect on the table mapping? This is where we start to have trouble.

Now, if you want to use the Linq To Sql designer plugin for Visual Studio then you should probably stop now. The designer for Linq To Sql (as well as SqlMetal) have a few limitations that get you right off the bat. Number one for me was not having the ability to put classes in separate namespaces, all of the classes that are generated have to be put into the same namespace. I don’t know about you, but this doesn’t really fly for me. Secondly, there really is no way to have your own business object hierarchy since that would require modifying the generated file and you would just overwrite it anytime you made a modification. Not really ideal. So I decided that I would have to bite the bullet and do manual mappings, which ended up not being too hard thanks to SqlMetal.

First of all I prefer to use Linq To Sql with map files, I don’t like having the attributes spread all throughout my application. I’m not really religious about which way to do it, but I prefer to have a single map file where I can go to view all of my mappings. I used SqlMetal to generate my initial mappings, and then I can tweak them. To do this I just open up the Visual Studio 2008 command prompt and run this command: (you’ll need to modify it for your settings, it requires you to spit out a code file as well, but you can use this as a starting point for your datalayer classes)

sqlmetal /server:. /database:DatalayerTest /map:"Map.map" /code:"Map.cs"

So, my mapping for my User class looks like this:

  <Table Name="dbo.Users" Member="Users">

    <Type Name="Core.Membership.User">

      <Column Name="Id" Member="Id" 

              Storage="_Id" 

              DbType="Int NOT NULL" IsPrimaryKey="true" />

      <Column Name="Username" Member="Username" 

              Storage="_Username" 

              DbType="NVarChar(100) NOT NULL" CanBeNull="false" />

      <Column Name="EmailAddress" Member="EmailAddress" 

              Storage="_EmailAddress" 

              DbType="NVarChar(255) NOT NULL" CanBeNull="false" />

      <Column Name="Password" Member="Password" Storage="_Password" 

              DbType="NVarChar(255) NOT NULL" CanBeNull="false" />

      <Column Name="LastUpdated" Member="LastUpdated" 

              Storage="_LastUpdated" 

              DbType="DateTime NOT NULL" />

      <Association Name="FK_Orders_Users" Member="Orders" 

                  Storage="_Orders" 

                  ThisKey="Id" OtherKey="UserId" 

                  DeleteRule="NO ACTION" />

    </Type>

  </Table>

In this map the "Id" and "LastUpdated" columns are located in the Core.BusinessBase while the rest of the columns are located in the Core.Membership.User class. With this exact mapping and the setup I just described you will get an error that says this:

"The column or association ‘Id’ in the mapping had no corresponding member in type ‘User’. Mapping members from above root type is not supported."

Doh! So, essentially what this is telling us is that Linq To Sql cannot see properties that are in the hierarchy of the class that we are currently using. So, how do we fix this? Well, we introduce wrappers for our properties that we have in the base class. Booooooooooooooooooo! This means that every property we have in our inheritance hierarchy that we want to expose through our entity we have to write wrappers for them. Now, I can understand for performance reasons why they wouldn’t want to look through a large inheritance hierarchy for these properties, but I think that we should at least have this option (especially if we know our hierarchies aren’t that deep).

private new int Id

{

    get { return base.Id; }

    set { base.Id = value; }

}

private new System.DateTime LastUpdated

{

    get { return base.LastUpdated; }

    set { base.LastUpdated = value; }

}

So, what happens when we put these wrappers classes in place? It all works, right? Nope. As soon as we put these wrappers in place we get this error:

"Bad Storage property: ‘_Id’ on member ‘Core.Membership.User.Id’."

If you look back at the Xml mapping up above you will see there is a "Storage" attributes on each Column element. This is actually optional, but you use it to tell Linq To Sql which private variables to use to store values. This is quite important if you have business logic that runs when an item is assigned to a property and you want Linq To Sql to bypass the property when creating a class. In our base class our fields look like this:

private int _Id;

private System.DateTime _LastUpdated;

The problem is that Linq To Sql cannot see these variables when it reflects over our class, a quick fix it to make them protected and voila, suddenly Linq to Sql can now get to them! This issue isn’t quite as big of a deal as having to paste wrappers throughout all of our business objects, but sometimes the error above doesn’t quite point to exactly what the problem is.

So, this isn’t that big of a deal, right? Well, pasting wrappers across a large number of business objects could be considered a pretty big deal, but overall it isn’t terrible. At least you are able to have POCO support, even if it is not perfect. So, what did they do right with Linq To Sql?

1) Actual POCO support, not the IPOCO (come on!) stuff that the Linq To Entity people are pushing.

2) I don’t have to have a public constructor, Linq To Sql will find a private parameterless constructor. This way all of my business classes can be created through a factory method, but it would have been cool if there was support for factory methods in the mapping. Although they were probably trying to keep things as simple as possible for v1.

3) They give you the option of supporting interfaces for property changes, but they aren’t required (these are INotifyPropertyChanging and INotifyPropertyChanged).

4) Even though my business classes can’t be in different namespaces with the Linq To Sql designer, I can do this using my POCO classes.

5) SqlMetal allows me to automatically generate my map files and my classes which allow me to have a starting point for my business classes.

Linq To Sql is not perfect, and yes I know that it isn’t the only OR/M solution out there, but they have done an excellent job in v1 and hopefully we will get a bit more flexibility in v2. In the meantime these are some pretty simple solutions that you can use to wrangle Linq to Sql into your datalayer. Also, if you have any good ideas for how to do any of this better please let me know!

Click here to download the full source for the Linq To Sql datalayer – This is not production code, it is barely even test code. I am providing this because if I don’t someone will complain, so please don’t make any comments about it.