I've been working through some architectural choices with a team starting a new project recently and the ever running debate over database access philosophies came up (as it was bound to).

Personally I'm in favour of letting an ORM manage all the CRUD operations as I see very little benefit in writing CRUD stored procedures (as do others). However I'm also pragmatic and can see some value in the sprocs argument, though the purported benefits of security and performance don't wash with me (but that's for a blog post).

Anyhoo, this particular team decided they wanted stored procedures for ALL data access.  That means CRUD procedures for every table and no direct table access whatsoever.  Yet at the same time they wanted to use an ORM for the .NET side of the equation as coding ADO.NET can be painful at the best of times.

Now, as part of the architecture we had decided early on to use the Repository pattern for data access and ensure we had persistence ignorance for all our data classes.  This lets us isolate the data access plumbing from the rest of the application which helps with the persistence ignorance in the rest of the app, and lets us work with POCO objects everywhere except in the repositories.  However it doesn't solve what to actually use in the repositories themselves.

So what to do? Some of the team wanted to use Linq To Sql, others wanted to use Microsoft's new Entity Framework and others suggested NHibernate.  Too many opinions meant there was only one real way to decide.  We decided to have an ORM to SProcs smackdown - may the best working code win!

The rules were simple... Take a very simple, 3-property, POCO business class and persist it to/from a database using nothing but stored procedures and an implementation of the repository pattern in the ORM of your choice.  Get it done in 4 hours or less.  The team would then vote for their preference.

Here's the results:

Entity Framework

The entity framework has support for stored procedures for data access.  Surely it would be simple to match things up, right? Wrong!! The developer who got the EF mission tried his best to get the tool to do what we wanted but mapping the classes was a problem.

The stored procedure support is best described as simplistic, at it's worst it's probably described using words not suitable for children to hear.

Even when the developer got something almost working, it still required creating a class that duplicated the POCO class and then manually mapping fields from one class to another.  Gah! This is pretty much the same as using ADO.NET and mapping to parameters.

Also, the steps required to get the Entity Framework code "working" were so convoluted and confusing it would be a maintenance nightmare, even if it did work.

No vote was required for this.  Everyone hated it.

Linq To Sql

There was some hope here - a working implementation was shown, however there are some major limitations.

First, Linq to Sql wants access to tables.  Without table access you don't get any change tracking in your data context negating many of it's benefits.

Secondly, the designer support for stored procedures without is limited.  Sure you can drag the procs onto the designer surface, but it only helps a little as the select stored procedures don't actually return classes you can easily use, but rather ISingleResult<T> objects.  To get these into our business layer POCO's we needed to manually map them using either a linq projection or through standard property assignments in code.

Updates via stored procedures were also a problem.  Not having table access means that you won't get any value from implementing the partial UpdateXXX() methods that the linq data context provides as they'll never be called.  Instead you have to call the update stored procedures directly and you have to manually map your objects properties to the stored procedure's parameters.

Further, if you use an insert procedure and database generated primary key fields then you have to remember to get the new value of the key from the database and update the POCO's properties with the new value.

It's workable, but no one in the team was that impressed with it.  It received zero votes.

NHibernate

The implementation using NHibernate was also shown to be workable.

A hibernate mapping file was created that had a class definition with specific overrides for the update, insert and delete methods so they called out to stored procedures.  There was also an override for the object loader to use a select stored procedure.

The only change to the POCO was that the properties needed to be changed so that they were virtuals.  We need to investigate this a little more but I'm going to assume it's something to do with NHibernate creating proxy versions of the classes for internal use.

Other than that the actual repository implementation was very simple.  Pass an object to NHibernate's session and call Flush at the appropriate times.

When a vote was called for everyone went for this method.  If I get some time I'll try and explain how it all works (or you could just do a Google search for it!).

 

Conclusion

So NHibernate method wins, but the scenario was simplistic.  There may be some further issues that we need to deal with such as complex procs, optimistic concurrency and so forth, but even so there is more confidence that NHibernate will deliver the goods.  It has a large user base, it's been around for quite some time now and we're pretty sure other people have tried this before.

On the other side of the equation, Linq to Sql and the Entity Framework are surprisingly and embarrassingly inept at this.  If you don't have direct table access then you might as well forget about using these tools and stick with ADO.NET or code generation.  The simple fact that data access via just stored procedures is such a common requirement in enterprise databases and that these two "modern" offerings from Microsoft just don't work is downright perplexing.  Seriously, Microsoft recommends for years that people use stored procs and the release tools that are only partially functional in a pure stored procedure environment. What gives?!

Well, I think that's enough of a rant for now - time to get some back to doing some real work now!