Practical .NET

High Performance Object-Oriented Data Access with Dapper

• By Peter Vogel
• 03/22/2018

There's no doubt that Entity Framework with LINQ is great (I've certainly written enough articles about it). But Entity Framework comes at a cost. As Matthew Jones points out on his blog, using LINQ+Entity Framework adds time to each data retrieval compared to using ADO.NET's SqlDataAdapter. The cost is small (anywhere from a third of a millisecond to 100 milliseconds) but it's not zero, either.

If speed is important to you but you don't want to give up object-oriented data access, then you can consider using Dapper. Like Entity Framework, Dapper is an Object Relation Mapper that allows you to issue queries against a relational database and get back objects. What's attractive about Dapper, compared to Entity Framework, is that its response times are equivalent to using pure ADO.NET.

There are some caveats about performance to make here and I'll discuss them at the end of this column. First, let me show you the basics of Dapper.

What is Dapper?
Dapper describes itself as "micro-ORM" because it provides much less functionality than Entity Framework does. It still counts as an ORM, though, because Dapper allows you to retrieve entity objects from relational tables. As part of "micro-izing" ORM functionality, rather than requiring you to create a context object as Entity Framework does, Dapper works as a set of extension methods for ADO.NET's Connection classes. Further, out of the box, Dapper supports only querying and updating through raw SQL, does not support configuring classes to match database tables, and doesn't support code-first development.

Having said that, there already exist multiple third-party extensions to the Dapper library that do, for example, support object-oriented updates and class-to-table mapping. If Dapper proves popular enough, you should expect more extension libraries to appear. It's at least conceivable that much of Entity Framework's functionality could be available through Dapper ... some day.

The beauty of Dapper's architecture is that, like JavaScript libraries, you're only obliged to add the library/functionality that you actually need. The downside is that you'll have to assemble a Dapper toolkit by picking and choosing among Dapper extensions with the real possibility that, once you add enough functionality, you'll have given up Dapper's edge in performance compared to EF.

Using Dapper requires writing a little more code than EF+LINQ (provided, of course, you ignore EF's upfront work in creating the context object). However, you write less code than you would with ADO.NET while getting ADO.NET-like performance.

Working with Dapper
The simplest way to use Dapper is to pass an SQL statement or stored procedure name to Dapper's Query extension method. The Query method attaches itself to any class that implements the IDBConnection interface so it's "database agnostic" and should work with any of the ADO.NET Connection classes. Query is also a generic method: You must specify the entity class that the returned result will have its columns mapped to.

This example uses the Query method to retrieve rows from the Employees table and create a collection of Employee objects:

Dim emps As List(Of Employee)
Using con = New SqlConnection(strConnection)
  emps = con.Query(Of Employee)("Select * from Employee")
  For Each emp As EmployeeBases In emps
    MessageBox.Show(emp.Name)
  Next
End Using

You can submit parameterized queries by including parameters in your SQL statement (flagged with the @ sign, as in T-SQL). You can provide values for those parameters through an anonymous object passed as the second parameter to the Query method. That anonymous object must have property names matching the parameter names you used in the query and those properties must be set to the values you want used in the query.

This code, for example, finds all the employees that have "Vogel" in their Name column:

emps = con.Query(Of Employee)("Select * from Employees Where Name Like ' percent'+ @Name", 
     New With {.Name = "Vogel"})

You can also pass a List of objects (useful when working with SQL's In operator), Dapper's DynamicParameters Dictionary collection (useful when re-using parameters), or Dapper's DbString object (which gives you more control over String values). If you want to submit multiple SQL statements at once, you can use Dapper's QueryMultiple method.

If you're only interested in the first item returned by the query, you can use the QueryFirst method to retrieve a single object:

Dim emp As Employee
emp = con.QueryFirst(Of Employee)("Select * from Employees Where Id = @Id, 
     New With {.Id = 431})

Dapper also provides QuerySingle, QueryFirstOrDefault, and QuerySingleOrDefault for retrieving a single object.

For submitting SQL Update, Insert and Delete statements, Dapper has the Execute method.

There is no real equivalent to EF's navigation properties so, if you want to retrieve data from multiple tables, you'll need to write your own SQL Join statement. You can map the results of a Join statement to multiple classes by providing a lambda expression to the Query method that describes which columns are to be used with which object and which column marks the rows that belong to the same "parent" object (typically, whatever column is used to join the tables). At this point (for me, at any rate) the Dapper code starts to get a little ugly -- I'll exhaust all my other options before I'll be willing to give up EF's navigation properties to get Dapper's speed.

Caveats
I do have a couple of warnings. When reading comparisons between ADO.NET and Entity Framework, it's always worthwhile to remember that the two technologies do different things. ADO.NET, for example, won't give you objects (or, at best, will only give you a pseudo object like a DataTable). EF will, when joining tables, do clever things when creating objects when working with repeated data that ADO.NET will not. Most of the Dapper to EF performance comparisons also use very vanilla EF+LINQ code. You can, if you're willing to invest the time, improve EF's performance by using AsNoTracking and writing more complex LINQ and lambda expressions than Dapper-to-EF+LINQ examples I've seen.

But, I have to admit, I think that writing more complex EF-related code to get better performance is missing the point: You use EF for its functionality, settling for performance that will usually be, at least, "good enough" (which is all you want). When you do need to squeeze the last ounce of speed out of your database accesses, rather than beat up on EF, the smart thing to do might be to shift to a tool that focuses on performance rather than functionality. And that could be Dapper.