Linq-to-Sql, Entity Framework, NHibernate and what O/RM means to me.

We’ve recently completed an upgrade of our enterprise application suite, or home grown ‘mini-ERP’ for lack of a better term.  We upgraded from .NET 2.0 to .NET 3.5 SP1,  a fairly painless exercise as far as complete system upgrades go.  We didn’t really have to change much code to get there, but with .NET 3.5 we get a whole host of new features and capabilities.  There’s WCF, WF, WPF, Silverlight, ASP.NET MVC, Linq and the Entity Framework just to name a few.  Naturally, the question is which of these new capabilities do we try to take advantage of and in what order?

To use any of them represents a considerable learning curve, a significant change in our architecture and in some cases a paradigm shift in thinking about how we solve certain kinds of problems. As the title implies, in this post I intend to discuss persistence patterns and technologies.

Being persistent

Our current persistence pattern is likely a familiar one to most.  We have a collection of business objects that serve the dual purpose of both business logic layer and data access layer.  That is to say that the objects contain both data and behavior as well as the logic for how to hydrate and persist themselves.  These objects loosely follow an Active Record pattern, mapping roughly one-to-one with the underlying data structure entities.  The persistence logic consists of internal calls to stored procedures which return DataSets/Datareaders and are then manually mapped to the object properties (or private members), in turn the properties are mapped back to stored procedure parameters for persistence.  This works adequately and has the advantage of being already written, proven and time tested code. Why would I consider changing existing working code to utilize an O/RM?


There’s a multitude of O/RM tools that have been around, widely used, feature rich and have achieved a level of maturity, none of which are dependent on .NET 3.5.  The most well known and probably most widely used of these is NHibernate.  Admittedly, my only experience with this tool was purging its remnants from a codebase I inherited a number of years back, not because of any deficiency with NHibernate, but because it was haphazardly implemented by a developer who didn’t understand it and then left the company.  At that time we had no experience with NHibernate, and were inundated with performance problems, locking and transactional issues with any code that used NHibernate.  Being as it was a relatively small percentage of the codebase utilizing NHibermate, we decided that rather than ramp up on NHibernate and fix the implementation, we’d instead favor consistency by replacing the NHibernate code with our tried and true DataSet pattern.  We weren’t ready then, but I knew at some point we’d need to revisit O/RM whether it be NHibernate or another of the multitude out there.  What does .NET 3.5 have to do with revisiting O/RM?

Linq is the word, its got groove its got meaning

For me, Linq is the catalyst to challenging our data access approach.  From the get-go, Linq-to-Objects and Linq-to-Xml were no-brainers.  To be able to query collections, xml documents, and DataSets with Sql-like syntax was an obvious and giant leap over nested foreach loops and similar conventions.  Linq-to-Sql and later Linq-to-Entities, however, are intriguing but much less obvious choices.  Considering that we already have hundreds of business objects, the rapid development features of generating code directly from the database schema and coding away isn’t compelling.  These two technologies, if we are to use them, would have to be shoehorned into our existing architecture.  We aren’t about to replace all our business objects with Linq-to-Sql’s ‘dumb’ data objects and throw away all our optimized stored procedures and hope for the best from the generated Sql.  Similarly, we aren’t going to convert all our business objects to EntityObjects and also hope for the best from the Linq-to-Entity Sql generator.  In both instances, we’d also be forced to find a place for all the other code and business logic contained in our business objects that wouldn’t play nice with the code generators, whether that be by using partial classes, inheritance or whatever.  Add to this the fact that L2S is supposedly dead, and EF is v1, I can’t make a strong case to jump on either of these as a business object replacement.  But do I really need L2S or EF to replace my business objects, or even want them to if they could?

Rise of the Data Layer

Code generation, tight coupling between database schema and objects, and Sql generation all become much more compelling at the data layer rather than the business layer.  If I view L2S objects, EF Entities, NHibernate generated objects, or any other O/RM objects as data transfer objects (dto) rather than business objects, then things get a little more interesting.  Using EF (I actually started out with L2S at first) to do some prototyping, I used a Repository Pattern in conjunction with Linq to query and persist data centric entities in a ‘detached’ manner.  What this approach allows me to do is replace my DataSets/Datareader mapping code with Entity mapping code, having the following benefits:

  • I only need to change the internals of my business objects, their structure, inheritance tree and interfaces are all unchanged.
  • My business objects are now mapped to type safe Entities, rather then DataSets and DataReaders, and with fewer lines of code.
  • Persistence is two-way, handled by mapping back to those same Entities, rather than passing a litany or parameters back to a stored procedure.
  • With very few lines of .NET code and no stored procedures I can handle SELECT, INSERT, UPDATE and DELETE, allowing me to drop all the boiler plate stored procedures that perform CRUD now, and not write new ones.
  • This same approach could have just as likely utilized L2S, NHibernate, LLBLGen, etc. giving me flexibility to chose or change tools without significant impact on the business objects.

This may not be POCO paradise or necessarily what the O/RM vendors intended, but for me it provides a compelling use for the technology while not forcing me to lock in and conform to the particulars of one tool.

Sometimes code is worth a thousand words so I’ve provided sample code. Download

Why use the Entity Framework?


Popular posts from this blog

RIF Notes #42

RIF Notes #4

The greatest trick the AI ever pulled was convincing the world it wasn't THAT intelligent