.NET – Benefits of a Micro-ORM

netorm

I've been looking into the so-called "Micro ORMs" like Dapper and (to a lesser extent as it relies on .NET 4.0) Massive as these might be easier to implement at work than a full-blown ORM since our current system is highly reliant on stored procedures and would require significant refactoring to work with an ORM like NHibernate or EF. What is the benefit of using one of these over a full-featured ORM? It seems like just a thin layer around a database connection that still forces you to write raw SQL – perhaps I'm wrong but I was always told the reason for ORMs in the first place is so you didn't have to write SQL, it could be automatically generated; especially for multi-table joins and mapping relationships between tables which are a pain to do in pure SQL but trivial with an ORM.

For instance, looking at an example of Dapper:

var connection = new SqlConnection(); // setup here...
var person = connection.Query<Person>("select * from people where PersonId = @personId", new { PersonId = 42 });

How is that any different than using a handrolled ADO.NET data layer, except that you don't have to write the command, set the parameters and I suppose map the entity back using a Builder. It looks like you could even use a stored procedure call as the SQL string.

Are there other tangible benefits that I'm missing here where a Micro ORM makes sense to use? I'm not really seeing how it's saving anything over the "old" way of using ADO.NET except maybe a few lines of code – you still have to write to figure out what SQL you need to execute (which can get hairy) and you still have to map relationships between tables (the part that IMHO ORMs help the most with).

Best Answer

Benefits:

  • Similar performance to a raw SqlCommand with DataReader and parsing.
  • No need to roll your own conversion layer for the DataReader.

That's pretty much it, to be honest. You've got a very lightweight wrapper to your sql connections that will do the object conversion for you. You can, obviously, fine-tune the queries without having to deal with any autogenerated SQL.

Cons:

  • Not even slightly typesafe. If you make a typo in the SQL your CI server is not going to catch it, you'll have to hope it's caught during automated UI or functional testing.
  • A pain to maintain. You've got a bunch of inline SQL statements that do various queries that have no strong ties to the DB architecture. This can quite easily lead to queries that get "left behind" when the underlying DB structure changes, which, again, you will not see at build time.

They have their place, and they're a very effective tool that can take away some of the "donkey work" from developers when interacting with the DB, but in reality they simply cannot take the place of a full ORM in any large-scale system for queries that are not performance-critical, simply due to the increased maintenance cost.

If you are struggling with performance on DB queries I'd suggest that it would be better to use these mapping frameworks with Stored Procedures only, in order to get a compile-time indication of whether your SQL is valid (plus the additional performance benefits).

Related Topic