Before we look at anything, we must try to understand what an ORM is. It stands for object relational mapper. It tries to solve a very common problem of impedance mismatch between the set based SQL world and the loops based programming world. (This impedance mismatch has given birth to NoSQL databases (MongoDb, Couchbase, RavenDB,etc). ORM takes sets of results returned by running the SQL and maps it to POCOs (plain old CLR objects). It also tends to have some SQL like capacities that can used to send the modified POCOs to the database to manipulate data. The typical ORMs used in the .Net world include NHibernate, EF (EntityFramework), and a whole bunch of micro ORMs such as Dapper, Massive, PetaPoco ,Simple.Data, OrmLite from ServiceStack ,etc.

These tools gained ground very quickly.  Unfortunately, that spread the myth of “You don’t have to know SQL if you use ORM”. Can one say, “I am going to write something in my favorite web framework and I’m not going to care about HTTP and HTML.”?   Abstraction makes things easier to deal with but does not replace the need for understanding the underlying layer. While accessing the data, if we know our SQL well enough, we can work with these ORMs in a better way or at least not abuse them.  Otherwise, we are at a high risk of spiraling down the hell hole of Select N +1 problems along with others.

Why use Micro-ORMs again?  What’s wrong with EF?

Sam Saffron says:

“It is that the one-size-fits-all approach does not offer a snug fit. When ORMs compete, they often use a big table to describe every feature they have that the competition lacks. Unfortunately, this is a big driver for bloat and complexity, it leaves us developers confused.”

(You can read  the entire post here )

This is coming from a guy I have great respect for. He is one of the creators  of MiniProfiler and Dapper (and some other stuff), one of my favorite Micro-ORMs. He also has done Speed Asp.net MVC edition series on Tekpub.com. I could not agree more with Sam here. It does seem like EF is trying to please everyone by jamming in a ton of unnecessary features .

The maintenance of EDMX(glorified XML) is usually a pain. Code first in EF is there, but there is no stored procedure support. This big “enterprisy” framework tries to do too many things.

It usually does a very good job working through the expression trees to generate SQL to fetch the correct data, but the generated SQL can be hard to understand, let alone controlling it. You will have to use profilers like EFProfiler, MiniProfiler or SQL profiler to keep an eye on the queries being generated. The query initialization in EF tends to be slower the first time. It is also hard to debug.

Why not let the database do some of the work of query optimizations? SQL server’s (if you are using SQL server) query optimizer is exceptional. Microsoft has put a lot of research into it.  Stored procedures can give that extra performance boost by precompiling the SQL.

Performance is a critical factor when it comes to data access. We don’t want to have nasty queries consuming too many resources and taking too long to execute. Especially if you are running it on services like Windows Azure which has a “pay as you go” model. (You pay only for the resources you used).  Good performance can directly result in saving money.

If you have a Select N+1 problem, the app could be hitting the database a thousand times to get one result, which can be converted to one simple query returning all the results. It is very easy to create this type of problem with EF.  Sometimes, the developers are not even aware of it.

In addition to all this, EF can create leaky abstractions.  In your typical Asp.net MVC application, you can make it hit the database from Razor View, Controller and business logic or service layer. Everything can get tightly coupled and may end up with duplicated logic.  It is hard to stop this kind of stuff. 

Micro-ORMs, on the other hand , work on the principle of less is more. They generally have a small set of features that work very well. So where is it all going? Are we going to be stuck in SQL land all the time? On Hanselminutes, Rob Conery, nailed the answer to perfection by saying "the complexity (of big ORMs) is resolving to something simple (micro ORMS)."

My take on it is that it is good to have options, have a good  understanding of them and use the right tool for the job.

This is a prologue to my talk on Micro ORMs at Connecticut dot net user group’s Code Camp. I have put together a project on github to demonstrate Dapper, Massive, Simple.Data and PetaPoco.