Thoughts on ORM Tools

January 14, 2015

The following is a summary of an email thread discussing Object Relation Mapping (ORM) Tools.  In my experience developers hold strong opinions about ORM Tools.  In a past life my organization used LLBLGen and the folks that were most informed on ORM tools had strong opinions that it was much better than both nHibernate and Entity Framework.   As a conversation starter I provided two articles from December of 2013 and follow up from February 2014 comparing the various ORM / Data access frameworks.  I wanted to see where my organization stood on the topic of ORM.

As expected there were strong opinions.  I found that there were essentially two camps – believers and non-believers. Interestingly the group (of about 10 very well informed senior folks) were evenly split on their opinions as to whether ORM is worth the effort or not.  Also very interesting was that there was little disagreement about the pros and cons of ORM.

Believers

The “Believers” are proponents of Microsoft’s Entity Framework.  I am apparently the only one to have ever used LLBLGen.   Somewhat surprisingly no one in the group had any significant experience with nHiberate.  Some had some passing experience with micro ORMs Dapper and Peta Pocco.  Believers say that the savings achieved by having a clean, very flexible data access layer code is worth the investment in the overhead in maintaining the ORM.  Their argument is that investment in tuning the ORM is smaller than the productivity gains achieved from its usage.

Non-believers

This group believes that the overhead associated with maintaining an ORM tool does not justify the return on the investment.  They believe that stored procedures connected to the database using custom data access layer code written in ADO.NET are best.  Some have built code templates to help generate and maintain their Data Access Layer.  This believe this really helps us on our efficiency while keeping full control on the code/execution.

Pros and Cons

There was broad consensus around the pros and cons of ORM – again based on experience with Entity Framework version 5 and 6.

Pros Cons
Relatively straight-forward. It has good default conventions and rules. Hard to fine tune EF (e.g. query optimization). In half cases it ends up writing SQL manually and executing it from EF context.
Functional – it implements 3 approaches (code- model- database- first), inheritance support, eager and lazy loading. Not very good for complex models. SQL queries become very large (could be up to several pages) and hard to understand.
Flexible. It’s possible to change conventions and rules; select only needed relations. Slow to fetch large datasets (thousands of rows).
Not suitable for batch operations (insert, update, delete)

Net net

There are a range of problems where ORM would be a good solution and others where it would not.  Small, relatively non-transactional applications seem to be a good fit.  As the volume of data grows the value gap narrows to well-done hand crafted SQL.  The tradeoff is obviously the cost of having simple changes take more time to implement and test than with something like EF.

ORM seemingly can be made to work for most applications – the question is at what cost.  Hand coding SQL might not make sense for an application with hundreds of database tables.  On the other hand ORM might not make sense for a highly transactional database.   In the end my sense is that this comes down to people and your architect’s preference.  The choice of an ORM is like choosing a platform – .Net MVC or Ruby on Rails, SQL Server or MySQL, Linux or Windows.  While there are some people out there who can easily move between platforms in my experience developers have preferences and comfort zones.  The choice of whether to use and ORM Tool and if so which platform to use is both an application and a personal decision.

References

https://www.devbridge.com/articles/entity-framework-6-vs-nhibernate-4/

http://stackoverflow.com/questions/2891905/should-i-use-entity-framework-instead-of-raw-ado-net

http://weblogs.asp.net/fbouma/fetch-performance-of-various-net-orm-data-access-frameworks-part-2

http://weblogs.asp.net/fbouma/fetch-performance-of-various-net-orm-data-access-frameworks

Advertisements