So you have a great idea for web application and you decided to use “ASP.NET” (Core I hope 😉 ). Now, you’re wondering how you will handle the communication between your application and your database. There are of course a lot of possibility for you:
- Hard code SQL queries using standard BCL (“SqlCommand”, “SqlDataAdapter”).
- Mini ORM.
- Full featured ORM.
A while back, I had to face this decision and I decided to go with the full featured ORM “Entity Framework Core”. At first, this product seems very appealing… You create some classes, then you’re using LINQ to query your database and you get typed data. You don’t really care how it works as long as it works. If you’re a bit curious, you’ll setup a SQL profiler to check the generated SQL queries and realize that… Well, it’s not that bad for simple queries, so what the hell, let’s enjoy and save a lot of time by using that tool.
You’re two weeks after this choice and you lost half of your hair and one kilograms in tears… Indeed, EF (Core or not) is actually not that easy to use when it comes to handle relationships, especially the many to many ones. You have to create three classes (the ones for your main tables and the one for the mapping one), then you have to use the Fluent API to configure the mappings and you never remember if it’s “HasOne”, “HasMany”, “WithOne”, “WithMany” that you have to use… And how you have to use them… etc… Then when you finally think that you have something viable, you execute your join query and then you get an obscure error talking about cyclic references and so on… Pfiouh, not that easy…
That’s actually the situation I’ve been in after having chosen Entity Framework as my ORM and as I didn’t wanted to waste my time on learning all the quirks of it, I decided to look for an alternative to EF. Let’s be honest, maybe I should have persevered and spent more time using it but I was really looking for something easy that wouldn’t add complexity to the basic task of managing data.
I didn’t wanted a full feature ORM anymore as I thought (maybe wrongly, I guess it’s subjective) that it’s not possible to find a ORM without quirks when you think about the complexity of the task they have to fulfill. So I started to ask Google what would be an alternative and I ended up on the GitHub page of Dapper (https://github.com/StackExchange/Dapper).
I checked a bit the documentation and the tool seemed actually pretty nice, then I noticed the “StackExchange” part of the URL so I did a bit of digging and I discover that “Dapper” was the ORM used by StackExchange web sites… Well… Do I really need to continue explaining why I chose this tool? I don’t think so…
Spoiler alert! If you’re looking for a tool that will handle ALL aspects of your database management (tables creation based on class definitions, insert based on .NET objects, etc…), “Dapper” is not what you’re looking for. It only tries to ease the database manipulation by giving you a powerful way to query your data and convert them in .NET objects. This means that you’ll have to type the actual SQL queries, “Dapper” won’t generate them for you 😉 But fear not, my child ! It’s totally worth it (unless you really don’t like SQL).
Back to basics
Dapper comes as a NuGet package that you can install by running:
For the sake of this article, we’ll use the following database.
Nothing very fancy… There are some formula one drivers, some tracks and some relation between these two tables. This is basic but it’s only there to see how to use “Dapper”. To store these objects, we’ll need the following two classes:
As you can see, even though we have three tables in our SQL database, we only have two classes. I don’t know you but… I prefer this way better than having a third one just to contain somehow the relationship, but once again, it’s subjective 😉
Let’s insert a new driver and as I’m pretty self-centered, I’ll start with my favorite one. Note that I’ll skip the connection establishment and just show the piece of code used to actually insert or query the data. In order to learn how to connect to a database in C#, I’m pretty sure that there are gazillions of articles about that on Internet.
And that’s it… The record is inserted in the database. It is also possible to retrieve the ID of the newly added record like this:
As this time we need to fetch information from the database (the newly created driver ID), we need to use “QueryAsync” instead of “ExecuteAsync” (that is used only to execute query that don’t return data other than the number of affected records). “QueryAsync” is a generic method expecting the type of the data it retrieves, in this case, an integer.
Note that the object that you pass in the second parameter of “QueryAsync” defines the parameters of the query that will be inserted in the safe way in the original query. For example, in the query above, we have a “@FirstName” parameter. This value will be replaced by the value of the “FirstName” property of the object passed as the second parameter of the method. This means that you could easily use an anonymous object to do so:
How do I query data?
Querying data is pretty easy, for example, here is how you can retrieve all the drivers from the database:
“Dapper” will automatically tries to map the fields of the database with a property in the class type you pass as the the generic parameter. Therefore, you have to ensure that the name of your properties and the name of your table fields match, otherwise, it won’t work.
How do I insert data?
Inserting data is as easy as fetching them. Let’s spice things up a bit by inserting a new track and setting “Fernando Alonso” as the fastest driver for it (totally not objective, I know).
I know, I know, we could have done that in only one query, but where’s the fun in that? The goal of the article is to show how to use Dapper, so let’s do that 😉
First, we start by retrieving the ID of “Fernando Alonso”, then, we simply execute an “INSERT INTO” query in order to add a new track. As you can see, this time, we used an anonymous object to define the query parameters.
If the line with the “if” does not work for you, it could be that you’re not using C# 7. Normally, you can use the light bulb to enable it. This line just ensures that the id is not 0 (default of an integer) as this would mean that “Fernando Alonso” was not found in the database.
What about joins?
Joins are also fairly simple to use as long as you know how to do them in SQL:
Okay… Maybe I oversold it when I said that it was fairly simple, but don’t worry, it’s going to get easier. The SQL query is pretty standard, we’re just joining the “Drivers” table based on the “BestLastTimeDriverId” foreign key. Then we have the “QueryAsync” method… When you’re using “joins”, you have to tell “Dapper” how to split the result in the different properties of your class.
The generics parameters must match the class names in the order they are selected and the last one must be the returned type and… Wow wow wow… Even I have difficulties to understand what I’m saying… Ok, let’s use the example. Here, we first select the fields of the “Tracks” table, so the first generic parameter will be the “Track” type. Then we select the fields of the “Drivers” table, so, the second generic parameter must be “Driver”. Finally, we want to return a “Track” object, so the last generic parameter must be “Track”.
Okay, now what’s this funky “Func” in second parameter of the “QueryAsync” method? Well, it’s simply a delegate used to distribute the data. As we just said, “Dapper” will deserialize the selected fields in objects of type specified as the generic parameters. So, we’ll have an object of type “Track” and an object of type “Driver”. Note that “Dapper” is automatically looking for an “Id” property to split the object. You can change this by specifying the parameter “splitOn” of “QueryAsync”.
So, the “Func” will get these two objects in parameters, which is why we have “(t, d)”. “t” will be the “Track” object while “d” will be the “Driver” one and as you know, we have to return a “Track” object, so we just define the property “BestLapTimeDriverId” property of “t” and returns it (it’s pointless to create a new “Track” object as we already have one).
Ok, I know what you’re going to say… It’s pretty complicated and also boring as most of the time, this “Func” will always just assign properties of the original object. You’re right but there’s nothing we can do ! Unless…
Dapper… Mapper… beams are gonna blind me…
The previous problem can actually be solved by using “Dapper.Mapper” package. To install it, just run:
Then add the following “using” statement:
Now, let’s modify our code like this:
I told you it would get easier 😉 While the “SQL” query remains the same, the “QueryAsync” usage is pretty different. Indeed, this time, we’re not using the one of “Dapper” but the one of “Dapper.Mapper” and this one automatically tries to split and distribute the fields in the correct object.
Note that the returned object will be the same type as the first generic parameter. For example, in the above example, the returned object will be of type “Track” as it is the first generic parameter passed to “QueryAsync”. The nice thing is that “Dapper.Mapper” can go depper than one level. Indeed, if the “Driver” had another property fetched from the database, “Dapper.Mapper” would be able to fill it as well. Don’t hesitate to play with “QueryAsync” to test this.
Note that you can’t pass more than seven generic parameters to “QueryAsync”, so if you have a complex query, you’ll have to use a different overload of the method. As it’s pretty hard to find a simple example that requires 7 joins, I’ll just show the usage of the overload:
In the example above, we’re selecting fields of 9 different objects. “Dapper.Mapper” will split them based on the “Id” fields and distribute them in 9 objects. The types of these objects are actually defined as the second parameter, the array of type, so for the example below, we’ll end up with an array of an “A” object, a “B” object, a “C” object, etc… This array is then passed to the third parameter of the method that defines how the different objects will be distributed (so basically… assigning the different properties).
Can I fetch multiple result at once?
In big applications, it’s not that often that we can retrieve all the data we need in one request. For example, in the schema we’re using, it would be pretty difficult to fetch all the races including all the winner of them without having redundancy. The way to do so would be to retrieve the data of the race, then retrieve the list of all the winners of this race, then define the property “Winners” of the object. Here is how this could be achieved using “Dapper”:
So here, we do 2 different requests:
- One the retrieve the data about the track
- One that retrieves the data of all winners of that track
Then, to be able to fetch these data by hitting the database only once, we’re using “QueryMultipleAsync”. This methods returns a reader (that must be disposed, which is why we’re using a “using” clause) that expose “Read” method to read the different data. The first call to this method reads the data of the first results set (so the track information), then the second reads the data of the second results set (so the winners).
This is of course pretty subjective but I’m pretty pleased by “Dapper”. What I don’t like with full featured ORMs is that you don’t really have the control of all that happens… It just happens and you have to pray for it to work from A to Z. With “Dapper”, you don’t really have this as the only part that you don’t have control on is the deserialization of your data in typed objects and most of the time, this is a pretty standard process that you don’t have to temper with.
Besides, you also have the possibility to define the SQL query by yourself which, according to me is a pretty big benefit as well. I think that at the end of the day, you’re the one who knows your database schema the best, no the tool you’re using, so you’re the one who is capable of crafting the most performant queries (well, normally 😀 ), so you can benefit from this by creating performant queries without having to deserialize them into typed objects by yourself.
So if you’re still hesitating between EF and Dapper, I would warmly recommend you to give a shot to the later one, you won’t regret it.