Persistence Ignorant POCOS part 4 – LinqToSql

Posted : Wednesday, 23 March 2011 10:47:04

In this, the fourth post in a series comparing the developer experience of hooking up a POCO using various ORMs I shall be creating an LinqToSql implementation of the persistence layer of my application. Linq2Sql although now officially deprecated by Microsoft still has a considerable following in the community and various open-source projects mean that, despite the lack of official support, LinqToSql isn’t going anywhere soon. While I haven’t used this particular persistence platform for a few years I do fondly remember how easy it was to get up and running with. The only aspect of LinqToSql that I have no prior experience of is defining the mapping configuration without using attributes. Given that the goal of this exercise is to create truly ignorant POCO entities, then defining the mapping in xml files is the only option!

So lets rock and roll!!! As with the previous posts, the first step is to create the basic project skeleton. I added a new class library called LcaModel.Linq2Sql to my application and as before, to this project I added an implementation of each of the repositories and a class to handle the wiring up of the dependencies:


In order to use all the LinqToSql goodness I had to add a reference to the System.Data.Linq assembly which can be found in the .Net tab of the add reference dialogue box:


In striking similarity to the EntityFramework, LinqToSql facilitates data-access, change-tracking and lazy-loading via an object known as a DataContext (compared to ObjectContext in EntityFramework). The constructor of this class has a two-parameter overload which accepts a connection string and an instance of an XmlMappingSource object. As the name suggests this object is responsible for mapping a CLR object to a database table. So following the convention used in the NHibernate example I added a folder to my project called “Mapping”. To this folder I added a single xml file called “Mapping.xml” and set its build action to “copy always”, at this point the xml file contained only the default xml document attribute added by visual studio. Next I added a binding to the Linq2SqlModule class to create an instance of XmlMappingSource from the xml file and altered the constructor of each of the repository implementations to accept an instance of string and this XmlMappingSource. I then stored class level reference to each of these. The load() method of the Linq2SqlModule is shown below:

   20         public override void Load()

   21         {

   22             //check there is a connection string

   23             var connectionStringSection = ConfigurationManager.ConnectionStrings["LightsCameraAuction"];

   24             if (connectionStringSection == null || string.IsNullOrEmpty(connectionStringSection.ConnectionString))

   25                 throw new ApplicationException("no connection string defined");


   27             Bind<AuctionService>()

   28                 .ToSelf()

   29                 .WithMetadata(MetadataBindingParamName, MetadataBindingParamValue);


   31             Bind<XmlMappingSource>()

   32                 .ToMethod(ctx => XmlMappingSource.FromUrl("Mapping/Mapping.xml"))

   33                 .InSingletonScope();


   35             Bind<IItemRepository>()

   36                 .To<Linq2SqlItemRepository>()

   37                 .When(c => c.ParentContext.Binding.Metadata.Get(MetadataBindingParamName, "") == MetadataBindingParamValue)

   38                 .WithConstructorArgument("connectionString", connectionStringSection.ConnectionString);


   40             Bind<IBidRepository>()

   41                 .To<Linq2SqlBidRepository>()

   42                 .When(c => c.ParentContext.Binding.Metadata.Get(MetadataBindingParamName, "") == MetadataBindingParamValue)

   43                 .WithConstructorArgument("connectionString", connectionStringSection.ConnectionString);


   45             Bind<IBidderRepository>()

   46                 .To<Linq2SqlBidderRepository>()

   47                 .When(c => c.ParentContext.Binding.Metadata.Get(MetadataBindingParamName, "") == MetadataBindingParamValue)

   48                 .WithConstructorArgument("connectionString", connectionStringSection.ConnectionString);


   50             Bind<IAuctionRepository>().To<Linq2SqlAuctionRepository>()

   51                 .When(c => c.ParentContext.Binding.Metadata.Get(MetadataBindingParamName, "") == MetadataBindingParamValue)

   52                 .WithConstructorArgument("connectionString", connectionStringSection.ConnectionString);

   53         }

And here is the relevant code from one of the repositories:

   12     public class Linq2SqlBidderRepository : IBidderRepository

   13     {

   14         private readonly string _connectionString;

   15         private readonly XmlMappingSource _xmlMappingSource;


   17         public Linq2SqlBidderRepository(string connectionString, XmlMappingSource xmlMappingSource)

   18         {

   19             _connectionString = connectionString;

   20             _xmlMappingSource = xmlMappingSource;

   21         }


   18         //rest of code omitted...

With the necessary dependencies wired up I now began to write the persistence code. This was very similar to the NHibernate implementation and, to coin a Hanselmanism, was very much “MonkeyCode” (“…just a monkey slapping a keyboard…”) type stuff. In a production application it may well be worth taking advantage of the .Net scaffolding functionality. I’ve listed an example below:

   23         public void Add(Bidder bidder)

   24         {

   25             using (var dc = new DataContext(_connectionString, _xmlMappingSource))

   26             {

   27                 dc.GetTable<Bidder>().InsertOnSubmit(bidder);

   28                 dc.SubmitChanges();

   29             }

   30         }

At this point I feel that I should say based on my experience of this and the previous post I thought back to the EntityFramework implementation, and decided to rewrite it slightly. In that implementation I created an instance of the LcaContext (a subclass of ObjectContext) in each repository and stored a class level reference to it. I changed this to store a class level reference to the connection string and wrap each data access operation in a using block around a new LcaContext to ensure all resources would be freed properly. In a production application I would probably implement the UnitOfWork pattern but for this post I felt that would be distracting, after all this is about Persistence Ignorance not UnitOfWork. Anyway I digress. So running the application now yielded,as expected, an exception due to a lack of mapping:


In order to map the POCO entities to the database tables I had to add the necessary xml to the Mapping.xml file I created earlier. The documentation I could find was pretty sparse but this link proved the most useful. Mapping simple properties is pretty straightforward,the mapping for the Bidder class is shown below (the rest of the mapping data is available in the download):

  <Table Name="dbo.bidder" Member="LcaModel.Bidder">
    <Type Name="LcaModel.Bidder">
      <Column Name="bidder_id" Member="BidderId" IsDbGenerated="true" IsPrimaryKey="true" />
      <Column Name="bidder_name" Member="Name"/>

After adding all the mapping data to the project, I ran the code again. This time I got the following output:


This time it ran ok but at the end of the auction the output suggested no bids had been made? After A bit of digging I found the problem, the auction class contained a method called EndAuction() which once the bidding process was finished would query the AuctionItem.Bids collection:

   70 if (AuctionItem.Bids.Count == 0)
   71 {
   72     Console.WriteLine(string.Format("{0} auction ended, no bids", AuctionItem.Name));
   73 }
   74 else
   75 {
   76     var winningBid = (from b in AuctionItem.Bids
   77                       orderby b.BidPrice descending
   78                       select b).First();

For some reason LinqToSql was not updating this collection as bids were made during the auction (EntityFramework and NHibernate both did so which is why this problem only came to light now). In order to fix the problem, I moved the part of the code in the Auction.RunAuction() method related to action to be taken once the auction was finished into the AuctionService class and altered the code above to query the Bids repository to get all bids made against an item:

   79     var allBids = _bidRepository.GetAllBidsForItem(auction.AuctionItem);
   80     if (allBids.Count() == 0)
   81     {
   82         Console.WriteLine(string.Format("{0} auction ended, no bids", auction.AuctionItem.Name));
   83     }
   84     else
   85     {
   86         var winningBid = (from b in allBids
   87                           orderby b.BidPrice descending
   88                           select b).First();

With the above change made, I ran the application again got the following error:


Because I was not persisting the DataContext between operations the entities used (in this particular case the Auction class) were not being managed by anything (in LinqToSql terminology they are all detached). In order to update an existing entity you need to attach it to a DataContext. One of the restrictions of doing this is that the DataContext will not participate in change-tracking (obvious really given that it has no notion of what the original values were) so I needed to add an extra attribute to alter each of the mapping nodes of the Auction class as follows:

 <Table Name="dbo.auction" Member="LcaModel.Auction">
  <Type Name="LcaModel.Auction">
    <Column Name="auction_id" Member="AuctionId" IsDbGenerated="true" IsPrimaryKey="true" UpdateCheck="Never"  />
    <Column Name="start_date" Member="StartDate" UpdateCheck="Never" />
    <Column Name="end_date" Member="EndDate" UpdateCheck="Never" />
    <Column Name="winning_bid" Member="WinningBidId" UpdateCheck="Never" />

Notice the UpdateCheck="Never" attribute, this tell LinqToSql not bother with change tracking and issue an update statement to update all of the properties of the class in the database table. I made the change above, recompiled and ran it again:


Excellent, all tickety-boo!

To complete the LinqToSql implementation I had to refactor the application slightly but on reflection this is a better design as is doesn’t rely on references updating correctly but rather it will get the most up to date data available. To make sure I hadn’t broken anything I re-ran the application using the other implementations and they all worked.

In summary LinqToSql was fairly easy to get running although I did have problems finding documentation on using XML as a source of mapping data. Other that that it was pretty straightforward and mapping was certainly easier than using EntityFramework but not as simple as the NHibernate fluent interface. So far the winner, from a developers viewpoint at least, has been NHibernate. I was planning to end the series here but after Microsoft released EntityFramework 4.1 RC last week containing among other things CodeFirst which also provides a fluent mapping API, the next and final post in this series will feature an EntityFramework CodeFirst implementation of the same persistence layer…

  • (This will not appear on the site)