SQL

…but they’re still better than the alternative which is to either fill your code with SQL statements or write and maintain a load of stored procedures. Why do I think OR/Ms are flawed? Because their purpose is to shield the developer from having to deal with the relational model and SQL in favour of an object model. In other words, provide an abstraction. However, they don’t quite get there as you do still need to know and use SQL (or worse, in the case of NHibernate, HQL which kind of mixes a SQL-like syntax with objects in place of tables).

NHibernate is a powerful weapon in a good developer’s arsenal. Unfortunately, its impact can be even more powerful in the wrong hands and can lead to very poor performance if you are not aware of what’s going on under the hood. I once worked at a company who saw NHibernate as a silver bullet but no one, not even the team lead understood it. They thought they we’re cutting edge and any problems they had we’re just down to the complexity of the domain they we’re working in. Needless to say, I didn’t stay there long, and last I heard, NServiceBus was their current flavour of the month! Anyway, the point is, they didn’t understand the tool or the SQL it generated which is key to getting the best out of it. In fact, it’s vital and this is the reason the abstraction doesn’t quite work because there is still a responsibility on you the developer to ensure that you are getting what you expect. An OR/M doesn’t mean you can forget about SQL and relational data models but there are many who think it does. And when the application is performing poorly it’s more than likely that the OR/M will get the blame.

So if we agree that you still need to understand the SQL produced by the OR/M then why not just write it all by hand in the first place, whether inline or with stored procs? Each option should be considered on it’s own merits of course but personally speaking, my preference would be a combination of approaches. I would use stored procs only as a means for returning view model data through “query” objects (using say, a lightweight Micro-OR/M such as Dapper). This data is “shaped” to fit how it is presented on screen. When I need to initiate an action on the domain I use a full blown OR/M (yes, NHibernate) for domain model persistence through “command” objects. A kind of poor man’s CQRS approach. These days I even go as far as interacting with NHibernate sessions directly instead of through the more common Repository approach as I constantly look to simplify my designs.

OR/Ms are not perfect and they’re not the answer to all our data persistence problems but they’re the best we currently have in the relational world and to me, preferable to hand rolling it all. Whereas in the past NHibernate would have been my only data access strategy for the entire project, I now see it as best suited to the “command” side and not used at all on the “query” side. Yes you can ignore OR/Ms altogether and do it all by hand but you’d be missing some important benefits. But in order to understand what those benefits are you need to invest some time learning your OR/M of choice, what its strengths are as well as its weaknesses. Only then can you make an informed decision. To not even consider them is an even bigger flaw.