Sep 18, 2008

Choosing an ORM When You Can Only Use Stored Procedures

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.


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!).



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!


  1. Just to add some completely unsubstantiated hearsay: I've heard iBatis.NET can be quite good for this type of situation (existing DB + sprocs).

  2. I don't really think your guys got it or at least got it backwards when trying to use SP's with L2S. You should give it another look.

    You don't need to call update SP's directly to get updates. The UpdateXXX overrides do this for you. You do need to use L2S Table objects to insert/delete objects but you don't have to use these Table's to query for data. You can have SP's return your entity instead of accessing from the Table directly. You can also easily turn off permissions to the tables in the database so errant/incorrect uses of the Table objects can be caught. L2S also allows you to specify LoadXXX method overrides so you can map lazy-load properties to SP calls too.

    I'm also not sure what problem you had with ISingleResult return type for SP's. The 'T's are your entities if you want them to be. The default for the designer is to invent a new type for you since it at first won't be able to deduce this. You can simply override it in the designer an pick one of your entity types. Then you'll get a return type of ISingleResult which is simply a collection of entities. Single in this case does not refer to a single entity, but a single result, where a result is an entire collection. The IMultipleResults return type can be used to denote a SP that return multiple result sets.

  3. @anonymous Thanks for the thoughts.

    The problem with L2S is that you have to have a mapping to a physical table, even if you never use it. If you haven't got a db table to create mappings against because of lack of access then you're stuck. And no tables means no UpdateXXX overrides.

    Also, to get the SP's to return the BL entities requires editing the designer or sqlmetal generated functions them making the application fragile. A change in the sproc will most likely result in a developer doing a regenerate and trashing the manual edits, plus not all sprocs will have a perfect mapping of return types to BL classes, thus the need to do data massaging in code.

    While you can get it to work, it's not at all pretty, and definitely not something this team would like to work with.

  4. Hi Richard,

    As I was reading your blog post I was hoping the winner would be nHibernate - we went with NHibernate :)

    I dismissed Entity Framework after a few Dot Net Rocks podcasts on it that didn’t really excite me. Microsoft has a few of these big grand frameworks (EntLib, Acropolis etc) that just seem to change the pain points rather than remove them and the documentation always seems all over the place therefore making them hard to understand.

    Linq To Sql however – I was thinking about looking at again – mainly based on the fact that I think the StackOverflow team went with it and have had positive things to say about it on their podcasts.

    My thinking is moving towards: SPROC’s are really an ‘optimisation’ rather than a ‘functional’ necessity wrt CRUD i.e. they perhaps should get added to an app as it moves up the S-curve rather than at the beginning. Even managing the SPROCs within the repo vs the ORM mappings creates extra dev work for little or no end-user benefit.

    U have a great blog - perhaps get to meet u at the Syd ALT-NET night coming up soon.

  5. Have you ever try with IBatis?
    It's absoluty great and very simple when you work with Store Procedures

  6. Don't be fooled by making simplistic cases work in an ORM. Some may have good results but down the road when requirements change and complexity creeps in, you may find the architecure too fragile and will have to write pass through code to get you straight to the database from your application.

    I have discussed this more in my blog here

  7. Well well well, the irony that my search on Google for guidance on using SP’s with an ORM should lead me here. Great article, and thanks Richard for sharing your experience. Being an “old school” developer I am keen to go with the old faithful and use SP’s with my ORM of choice. Imagine my dismay when I find my research leaving me high and dry on the topic of, which ORM provides this support and is easy to use. Then I come across this article only a week after shaking your hand saying thanks for your hard work @ SAI. I have one question for you, given your article was written 2 plus a bit years ago, is NHibernate still the front runner or is there a new kid on the block?

    Jason Shone

  8. @Jason I'll email you separately with some thoughts.

  9. Richard, I am faced with the same challenge on a project I just inherited. Tried EF but still seems better suited for table access than SPs. What are your thoughts on the topic today? NHibernate still the winner?

    Mike Sheehan

  10. @Mike These days I'd likely look at using a Micro ORM such as PetaPOCO or Dapper and avoid a full blown ORM entirely. Why? Simply because the features of the ORM will never be used and it's effectively bloatware in your app that will slow it down.

  11. Richard Banks

    Here is an ORM that works with Microsoft Access

  12. Which ORM tool is better for working with Mybatis.?