Entity Framework (EF) is a great tool because it helps developers get their data access layer up and running quickly, provides validations, and handles complex write operations gracefully. I end up using it for most CRUD-type web apps I develop, however, there is one area where the EF can unknowingly trip up a lot of developers. This trip-up occurs when the data-access code has performance issues, either because of EF generating a complex query or EF overhead. The result is your data access code starts running slowly, and this becomes noticeable to users. How can developers avoid this? Let me introduce you to one of my favorite new tools: Dapper!
It’s ridiculously fast, easy to use and, more importantly, if you have written ADO.NET or LINQ, it will already be in your wheelhouse to use. Before we dive in, I want to show you a metrics table that describes how long it takes to execute a SELECT statement and map the data to objects. As you can see, Dapper is as close to bare metal as you can get, while providing the mapping you would expect from something like EF. The link to the full table is below which shows in detail more ORMs, methods, and the average time each took.
ORM | Method | Return | Mean | Gen 0 | Gen 1 | Gen 2 |
Hand | SqlCommand | Post | 87.16 us | 2.5000 | 1.0000 | 0.2500 |
Dapper | QueryFirstOrDefault<T> | Post | 91.51 us | 2.8750 | 0.8750 | 0.2500 |
EF | SqlQuery | Post | 143.86 us | 5.2500 | 0.7500 | – |
EF | First | Post | 197.91 us | 6.5000 | – | – |
EF | SqlQuery | Post | 247.25 us | 6.5000 | – | – |
EF | First | Post | 247.53 us | 15.5000 | – | – |
LINQ | ExecuteQuery | Post | 284.74 us | 7.0000 | 1.0000 | 0.5000 |
LINQ | First | Post | 968.14 us | 4.0000 | 1.0000 | – |
From https://github.com/StackExchange/Dapper
For this demo I’ll be using the AdventureWorks 2016 database. You can download that here. After that, the next step is to install the Dapper nuget package to your project.
First let’s get our models written for the sales order and sales-order details objects:
Note you do not need to have every property on your object mapped to the returned dataset. Dapper will ignore any excess columns.
Next, let’s call our endpoint to get a SalesOrderHeader by an ID:
You may notice one problem here; the Details array is empty! Since Dapper isn’t an ‘enterprisey’ ORM it won’t automatically generate the query needed to load child entities. Not to worry. If you can dodge a wrench you…wait. If you can write a stored procedure, you can certainly solve this problem.
Dapper has something called “Multi Mapping.” You can specify the types your query returns, and the identifier it should split the results on, and then it will auto-map the one-to-many relationships for you.
Dapper Code/Query:
Mapped Child Results:
You can see that Dapper is a great addition to your Batman Utility Tooling Belt. My preferred Dapper use case is for building a data-access layer with a relatively simple data schema, or when the Entity Framework performance just doesn’t suit your needs. Give it a try on your next project where it fits the requirements, you’ll be pleasantly surprised by how easy and fast it is to use. Finally, if you have any additional questions around Dapper, please don’t hesitate to reach out to us. We’d love to help you out.