.NET Micro-ORMs - Dapper, PetaPoco, and more

The move to ORMs - from NHibernate, to Linq to SQL, and then to Entity Framework - has been fraught with complications, excitement, and a variety of issues for developers.

What did we learn during the ORM journey?

Along the way, we learned that developers hate working with DataSets and DataReaders - preferring to work with strongly typed Domain model objects. We also found that most developers don't like XML mapping files at all. In fact, many developers aren't really that excited about visual designers - preferring to work with POCOs, as I have learned to do. Also, developers seem not to like to have to decorate their POCOs with various attributes to the point where they're not really POCOs any longer. After all, a POCO is just what it stands for - not some class with dependencies on attributes or a base class or some special interface.

For NHibernate, there was Fluent NH - an excellent addition. Entity Framework came out with Model First, and finally a POCO model.

ORMs - the good and the bad

All of these systems provide an Object Relational Model (ORM), each with its own pros and cons. I have mixed feelings about most ORM systems. I like them because some of them allow me to develop a web site or application quickly. I don't have to spend as much time on the database access code.

However, at the same time, I dislike them because their performance and scalability can be very poor, even when they’re integrated with a comprehensive caching system. Very few ORMs could be considered "high performance" - what you need on a high traffic web site that really needs to scale.

The object orientation of ORM systems often results in extremely chatty implementations. Because ORM systems tend to make it a little too easy to access the database, they often result in making more round-trips than you really need.

Although both LINQ and NHibernate’s hql do provide some control over the queries that are autogenerated by these systems, in complex applications the queries are often inefficient and difficult or impossible to fully tune. It is Microsoft's current posture that Entity Framework is now the "preferred" or recommended means of data access. This could mean that legions of trusting developers will go happily trotting off into the sunset with their Entity Framework creations, blissfully unaware that under heavy load, these things can literally bring a server to its knees.  Don't get me wrong - I like Entity Framework. But where performance is a must, it may not be the best choice.

The Micro-ORM

Enter the Micro-ORM. These provide the esssence of what developers want - an easy way to map Database operations to strongly typed classes. LINQ support in some makes it even better. But the main advantage of some of these Micro-ORMs is raw speed. They don't offer all the bells and whistles of the Entity Framework or NHibernate, but in many cases, you don't need all this glop anyway. You simply want to be able to do CRUD and work with objects instead of DataReaders etc.

Let's have a look at the performance test results from Dapper. The following table and info comes directly from the main Dapper Documentation page. With the full source to Dapper, you can run all these tests locally on your own machine and draw your own real-life conclusions:

The following metrics show how long it takes to execute 500 SELECT statements against a DB and map the data returned to objects.

The performance tests are broken in to 3 lists:

1. POCO serialization for frameworks that support pulling static typed objects from the DB. Using raw SQL.
2. Dynamic serialization for frameworks that support returning dynamic lists of objects.
3. Typical framework usage. Often typical framework usage differs from the optimal usage performance wise. Often it will not involve writing SQL.

It is easy to see that our old favorites - including Entity Framework - are way down at the bottom of the list. Let's face it -- they're absolute HOGS! They burn up a huge amount of CPU to do the same thing the skinny guys do very quickly. Of course, this is a boon for computer manufacturers because web site developers often try to solve such problems by throwing another server at it instead of finding and fixing the bottlenecks!

Leave my POCOs Alone!

I've looked at a number of Micro-ORMs, and decided to focus in on both PetaPoco and Dapper. They both have comparable speed. But of the two, it's my opinion that Dapper is more mature, has more complete test coverage, better supports LINQ, leaves your POCO's alone (important) and has better support for stored procedures. I don't want to get into the "Stored Procs Are Evil" flame war here. I do stuff with stored procedures, table-valued-functions, triggers, and CLR procs that those SQL purists will never be able to do. It's your choice - I like stored procs, period. And another thing - while provider agnosticism is of value, it's been my experience that the vast majority of applications and frameworks are developed from the start to target a specific RDBMS flavor, and that rarely ever gets changed. Another advantage I see with the Dapper offering is that the primary developer, Sam Saffron, has successfully resisted the inevitable torrent of suggestions for change by insisting that they go through the Dapper.Contrib process - thereby keeping the main offering 100% "pure". Marc Gravell is also on this ticket. I know Marc pretty well, and he is a very smart fellow.

I have no idea how they came up with "Dapper" for a name, it is completely undescriptive of what the thing is for. But that's a minor issue. At least the Class file is named, appropriately, "SqlMapper". Dapper supports any RDBMS that has an ADO.NET provider, so let's zero in on Dapper and do a quick review on why you might want to consider this for your data access needs.

Dapper consists of a single file, SqlMapper.cs, that you can drop into any project. It provides a set of extension methods on the IDBConnection interface. The easiest was to see examples of how Dapper is used is to look at an abbreviated version of the SqlMapperUtil class that I wrote to make it easier to "genericise" the use of the major methods:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Dapper;

namespace MyTest
{
public static class SqlMapperUtil
{
private static string connectionString = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString;

private static SqlConnection GetOpenConnection()
{
var connection = new SqlConnection(connectionString);
connection.Open();
return connection;
}


/// <summary>
/// Stored proc.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="procname">The procname.</param>
/// <param name="parms">The parms.</param>
/// <returns></returns>
public static List<T> StoredProcWithParams<T>(string procname, dynamic parms)
{
using (SqlConnection connection = GetOpenConnection())
{
return connection.Query<T>(procname, (object)parms, commandType: CommandType.StoredProcedure).ToList();
}

}


/// <summary>
/// SQL with params.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sql">The SQL.</param>
/// <param name="parms">The parms.</param>
/// <returns></returns>
public static List<T> SqlWithParams<T>(string sql, dynamic parms)
{
using (SqlConnection connection = GetOpenConnection())
{
return connection.Query<T>(sql, (object)parms).ToList();
}
}

/// <summary>
/// Insert update or delete SQL.
/// </summary>
/// <param name="sql">The SQL.</param>
/// <param name="parms">The parms.</param>
/// <returns></returns>
public static int InsertUpdateOrDeleteSql(string sql, dynamic parms)
{
using (SqlConnection connection = GetOpenConnection())
{
return connection.Execute(sql, (object)parms);
}
}

/// <summary>
/// Insert update or delete stored proc.
/// </summary>
/// <param name="procName">Name of the proc.</param>
/// <param name="parms">The parms.</param>
/// <returns></returns>
public static int InsertUpdateOrDeleteStoredProc(string procName, dynamic parms)
{
using (SqlConnection connection = GetOpenConnection())
{
return connection.Execute(procName, (object)parms, commandType: CommandType.StoredProcedure );
}
}
}
}


My "Test" program exercises the above:

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using Dapper;
using LinqUtils;
using sqlserver;

namespace MyTest
{
class Program
{
// INSTRUCTIONS:
// 1) Ensure the connection string in App.config points to your instance of the SQL Server Northwind Database.
// 2) Execute the 2 sample stored procedure scripts located in the /SQL folder of this project.

static void Main(string[] args)
{
// Find Nancy Davolio by partial last name
List<Employee> list2 = SqlMapperUtil.StoredProcWithParams<Employee>("FindEmployees", new { FirstName = "", LastName = "Dav" , City=""});

StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
ObjectDumper.Write(list2, 1, sw);
Employee emp = list2[0];
Console.WriteLine( sb.ToString());

Console.WriteLine("StoredProc done. Any key to continue");
Console.ReadKey();

emp.LastName += "REVISED";
// Update Nancy Davolio row with the modified last name
int retval= SqlMapperUtil.InsertUpdateOrDeleteStoredProc("UpdateEmployee", emp);

Console.WriteLine("InsertUpdateOrdeleteStoredProc done. Result="+retval.ToString( ) +". Any key to continue");
Console.ReadKey();

// Get all matching employees on lastname like "Da"
List<Employee> zips = SqlMapperUtil.SqlWithParams<Employee>("select * from Employees where LastName LIKE '%' +@LastName + '%'",
new {FirstName = "", LastName = "Da", City="" });

StringBuilder sb2 = new StringBuilder();
StringWriter sw2 = new StringWriter(sb2);
ObjectDumper.Write(zips, 1, sw2);
Console.WriteLine(sb2.ToString());

Console.WriteLine("Demo complete. Any key to quit.");
Console.ReadKey();
}
}
}

The ObjectDumper class in the project is a holdover from the Microsoft LINQ Utilities and is simply used to output the properties of a class to the console.

You can execute a query and map it to an IEnumerable of your type, or to a dynamic list if no type is provided. You can execute commands such as Update, Insert or Delete that return no results, and you can execute stored procedures.

Parameters are passed as anonmous types, which allows you to name parameters. It supports Lists as parameters for queries that have, for example, an IN list. The default behavior is to buffer the entire reader on return, which cuts down on database network time. However, you can also pass buffered: false into the Query method.

You can map a single row to multiple types, and also process multiple resultsets from a single query.  For example, if you have a Customer object with a property consisting of List<Address> you can easily write a method that will assemble this using the QueryMultiple method.

You can use an anonymous type to pass SqlParameters, or you can use the DynamicParameters object to fine-grain specify each parameter to a query (such as an output parameter for @@IDENTITY). To support NVARCHAR columns, you use IsAnsi= false within your anonymous type declaration of your input parameters.

Dapper also caches information about every query it runs, which allows it to materialize objects and process parameters quickly.

You can also map types that have sub-object properties by constructing the SQL Statement correctly with a JOIN, and issuing a query like the following:

var result = connection.Query<Manager, Employee,Manager>( sqlString, (manager,employee)=> {manager.Employee=employee; return manager;});

Dapper uses DynamicMethod.ILGenerator to emit inline IL for binding properties which gives it a big speed boost over competing micro-ORMs.

Approximately 1 to 2 hours invested with the main Documentation Page on the Dapper site, along with an inspection of the many Test methods in the full source code, will get you to the point where you've completed virtually the entire "learning curve" for Dapper. After that, I'm willing to bet that if performance and ease-of-use are primary concerns, you'll be a Dapper Convert!

My full SqlMapperUtil class has more methods than shown, such as GetDynamicParametersFromObject(object target, string[] propertiesToIgnore) , SetIdentity<T>, and static U StoredProcInsertWithID<T,U>(string procName, DynamicParameters parms), which executes a sproc and returns the @@Identity value from an output parameter in the sproc.

There is more to Dapper, but the above represent the main points. That's a lot for about 1500 lines of code! I've experimented with a whole lot of OPC - other people's code - and in my opinion, Dapper is just a little masterpiece of smart code. There's also a vibrant discussion on it both on the Google Code site and on Stackoverflow.

You can find Dapper on Google Code here: http://code.google.com/p/dapper-dot-net/ and the GitHub distro here: https://github.com/SamSaffron/dapper-dot-net.


The Visual Studio 2010 downloadable solution includes most of the original Dapper distribution (I removed all the different provider tests), a set of T4 templates that I modified from the PetaPoco distribution which you can point at any database to generate a file with 100% clean POCO classes for all the tables in a database, and two stored proc SQL scripts that go with my tests of the Northwind SQL Server Database. You should be able to run this locally with no more modifications than ensuring the connection string suits your environment,