Going the micro-ORM way with Dapper

If you're a developer you've most certainly used stackoverflow (or any other of the stackexchange sites). It's the reference Q&A site out there and has got tons of helpful content and a really enthusiastic community.

One of my favorite things about Stackoverflow is that it's damn fast. The interface, although minimalistic, shows lots of information, refreshes everything on-the-fly and just feels snappy. It's public knowledge that Stackoverflow developers are obsessed with performance, and fortunately they've been more than happy to share their secrets with the community through some blog posts and videos.

To achieve the results you see today they've used many clever tricks and tweaks, but have also developed some in-house tools to overcome the performance limitations of existing technologies. In this post I'm going to look at their own ORM: Dapper, which is widely used in Stackoverflow and has been open-sourced on Github (link).

First of all, Dapper is specifically a micro-ORM. You can't just compare it with NHibernate or Entity Framework because all that it does is materialize your POCOs from your manual queries results.

It's used mainly like this:

using (var connection = new SqlConnection(connectionString))
{
  connection.Open();
  List<Person> persons = connection.Query<Person>("select ID,Name from Person");
}

You might argue: "Manual Sql strings? That's so 20th century. Where's my LINQ?". Yeah, it's true, but remember, this is built for performance and simplicity. If you want a little bit more background and motivation on the "why" check Sam Saffron blog post where he describes why and how Dapper was developed.

Demo

Anyway, let's create a demo from scratch.

• I'll assume there's a database with a table named "Person" with the following columns:
• ID: int identity
• Name: varchar(255)
• Age: int

I've also added 10 persons to the database:

• Now lets create a C# console application in Visual Studio 2010/2012

• Dapper has been packaged on NuGet for our convenience, so just add it to your project.

• Let's start coding:

using System.Collections.Generic;
using System.Data.SqlClient;
using Dapper;

namespace DapperTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (var conn = new SqlConnection("<connection string>"))
            {
                conn.Open();
                var persons = conn.Query<Person>("select * from person");
            }
        }
    }
}

Simple stuff. Create a connection and fetch all the Persons in the database. If you place a breakpoint after the query you can see that it correctly fetches all the persons with their data.

You can also pass parameters using anonymous objects. For example, to just get people with age > 35.

var persons = conn.Query<Person>("select * from person where Age > @age",
                                  new { age = 35 });

What about Inserts, Updates? Well, we've got to go old school on this, sending: "insert into/update" sql commands to the database. Something like:

connection.Execute(@"insert into Person(Name, Age) values (@name, @age)", 
                     new{ name="John Smith", age=20 });

Not very compelling... but at least is fast.

I'm not going to delve on all the features of Dapper, as the main page of Dapper does a good job at that, but it's basically some simple variants of what I've shown above:

• Executing commands
• Sending multiple commands to the database
• Dynamic data 
• (etc)

Personal oppinion

Dapper is really simple and easy to grasp, but I'm not that fond of having explicit SQL laying around and not taking advantage of the LINQ goodness and all the stuff that NHibernate/Entity Framework provides.

I guess one can follow the approach Stackoverflow uses: they've started out using Linq-to-SQL, and fine-tune to Dapper as needed. The advantage of using POCOs is that they're ORM agnostic, and you can use them in your business logic, whatever the ORM that materialized it.

I'm not really sold on this concept yet, because experience has shown me that typically the performance bottlenecks with big ORMs are related to the database itself (badly designed schema, incorrect indexing strategy, etc) or with the incorrect usage of the ORM (not understanding lazzy loading, the N+1 problem, not using a profiler to check the generated queries, etc). The ORM "bloat" is typically not the main issue.

A "better" Dapper

I like the simplicity of Dapper but miss some of the help you get from a more complete ORM. For example, to do updates/inserts.

Sam Saffron wrote a blog post talking about this "problem" and here's what he says about it:

"In the past I “sold” Dapper as a minimal ultra fast data mapper. In retrospect the message I sent was lacking. Dapper is piece of Lego you can build other ORMs with."

This makes sense, and one can confirm this is true just by checking some NuGet packages like:

• Dapper.Contrib
• Dapper.Extensions

Both give you additional methods like the following:

connection.Insert<Person>(new Person { Name="John Smith", Age = 20});

or

connection.Update<Person>(new Person { ID=6, Name="John Smith", Age = 20});

Dapper.Extensions provides some additional goodness. Its full feature set is:

• Automatic mapping of POCOs for Get, Insert, Update, and Delete operations.
• GetList, Count methods for more advanced scenarios.
• GetPage for returning paged result sets.
• Automatic support for Guid and Integer primary keys (Includes manual support for other key types).
• Pure POCOs through use of ClassMapper (Attribute Free!).
• Customized entity-table mapping through the use of ClassMapper.
• Composite Primary Key support.
• Singular and Pluralized table name support (Singular by default).
• Easy-to-use Predicate System for more advanced scenarios.

Not bad at all. Lets try it:

• Add the NuGet package called "DapperExtensions"

• Modify the source-code

using System.Collections.Generic;
using System.Data.SqlClient;
using Dapper;
using DapperExtensions;
using DapperTest.Properties;
using System.Linq;

namespace DapperTest
{
    class Program
    {
        static void Main(string[] args)
        {
            using (SqlConnection conn = new SqlConnection("<conn string>"))
            {
                conn.Open();

                int countBeforeInsert = conn.Count<Person>(null);

                conn.Insert<Person>(new Person { Age = 33, Name = "John Smith"});
                conn.Insert<Person>(new Person { Age = 64, Name = "Mary Kole" });

                int countAfterInsert = conn.Count<Person>(null);

                IList<Person> persons = conn.GetList<Person>(
                    Predicates.Field<Person>(f => f.Age, Operator.Gt, 35))
                    .ToList();
            }
        }
    }
}

Here we're using some specific DapperExtensions methods like the "Count", "Insert" and "GetList" (which supports a basic predicate system). The result of this execution is:

There were 10 persons on the database (countBeforeInsert). 2 extra persons were inserted, thus 12 in the database (countAfterInsert). There are now 6 persons with ages above 35, the first 5 from the original data and the new one that was inserted (64 year-old Mary Kole).

Conclusion

Well, this ends my post on Dapper, the micro-ORM used at Stackoverflow. It's simple, fast and doesn't do too much magic for you.

Just to wrap up, I would like to mention two other great Micro-ORM libraries: Massive and PetaPoco. Although their usage is a little bit different the principle remains the same.