21

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

10
  • +1: You'll still need a query language no matter what, so you may as well stick with something familiar like linq or sql, but returning anonymous types like in your example doesn't seem to map a relational model to a concreted domain model. That sticks out as odd to me. Nov 18, 2011 at 14:24
  • Yeah, I couldn't find a concrete example of Dapper, on the site for it it literally does var dog = connection.Query<Dog>("select Age = @Age, Id = @Id", new { Age = (int?)null, Id = guid }); and then dog.First().Age to access properties. Nov 18, 2011 at 14:27
  • 5
    The example doesn't return an anonymous type, the "var" keyword can be used in the place of a concrete type in C# to save the extra typing, that query would return IEnumerable<Person>.
    – Ed James
    Nov 18, 2011 at 14:47
  • 1
    The main reason is less overhead having to constantly check in sprocs and then remember to update some two dozen SQL files, not to mention having to have stored procs for trivial CRUD code. Nov 18, 2011 at 15:42
  • 2
    The main benefit to not using sprocs to keep all the code (and I use code on purpose here, including SQL and your other languages-of-choice) in the same version control system. I've yet to come across a good cross-DB database VCS.
    – Ed James
    Nov 18, 2011 at 16:32

3 Answers 3

12

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

1
  • There are libraries that can be used as a wrapper for these micro orms which can reduce the problem you're mentioning with typos. Eg: Simple Crud add-on for Dapper Jan 23, 2015 at 12:00
3

In the website of micro ORM PetaPoco he explain some advantage with other ORM. to explain more

PetaPoco is a tiny, fast, single-file micro-ORM for .NET and Mono.

  • Like Massive it's a single file that you easily add to any project
  • Unlike Massive it works with strongly typed POCO's
  • Like Massive, it now also supports dynamic Expandos too - read more
  • Like ActiveRecord, it supports a close relationship between object and database table
  • Like SubSonic, it supports generation of poco classes with T4 templates
  • Like Dapper, it's fast because it uses dynamic method generation (MSIL) to assign column values to properties
0
1

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.

I believe that is the main benefit of a Micro-ORM, to get the other benefits of an ORM you would need to use a full blown one. That and the code is relatively smaller, so if you need to customize it, that would be easier.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.