Dapper.Net coexistence with Entity Framework and caveats

Posted on: 2015-12-15

Dapper.Net is a micro ORM (objection relational mapping) that StackOverFlow is using. It's open source and it still maintained by the team, mostly by Marc Gravell who is a top 5 users in StackOverflow too. The project I am working on is getting slowed down by Entity Framework (EF) since few months and the introduction to an alternative solution was required. This is why I introduced Dapper.Net in the solution. The goal is to slowly switch toward something more under my control and Dapper.Net offers this by letting you write queries in SQL directly. In theory, it looks good since it's used by a top website, still maintained and is less intrusive by having less magic behind the scene. That said, most problem that I was hitting with Entity Framework was with DbContext and Dapper.Net simply doesn't have central object that they to be intelligent. In this process of introducing Dapper.Net, I'll keep Entity Framework generating the database and keep this one to read the data. I will just not use it to save problematic entities.

Using Dapper.Net is simple. You use a Nuget package, the DLL is downloaded and you can use it. Not a lot of knowledge is require to use the basic because you are using a basic DbConnection which is enhanced with static method (by extension) to let you query and execute SQL code. However, this assumption that it does not require a lot of knowledge ends soon when you try to do something a little bit out of scope of a quick get and set. The first caveat is that the documentation is very slim. One can say that it doesn't need more, but this is only true with basic scenario. To help explaining some limitations, this article will use an example where we will use Dapper.Net to save an entity called Contest. It's a class that has properties which are object. Some of them are saved directly in the "Contest" table -- this is called "Complex Object" in Entity Framework. And some others are saved in other tables with a foreign key that link them. It as 3 optional properties in a 0 to 1 relationship which go to other table, also it has two 0 to many (0-*) relationship to other classes. It also have a 1 to 1 relationship that is required. The number of columns in the "Contest" class is about 20. Here is a high level of the class diagram of the Contest class. I added some blue inside the aggregation to illustrate complex object and the white one are for classes that are from different tables than the contest ones.

The second Dapper.Net limitation you will get is about the concept of complex object. Dapper.Net doesn't know about it, which is fair since it's a EF concept, but the problem is how can you tell this ORM to map a specific syntax into an object instead of a field directly into the main object (Contest). This is no where in the documentation. Here is two examples that you can see in almost every system: name that is localized and money with currency.

Dapper.Net is expecting to have properties named Name_French, Name_English, InitialCapital_Value, InitialCapital_CurrencyTypeId. But, the problem is that Contest has 1 property called Name of type LocalizedString which has 2 properties called French and English and 1 property called InitialCapital of type Money which has 2 properties Value and CurrencyTypeId. This doesn't work at all. The way to work with Complex Type (and also relationship tables) with Dapper is to use the multi mapping feature. This multi mapping allows you to divide a single database rows into different objects by defining a key pattern which is by default id. In our example, we could specify that the pattern is Name_French and InitialCapital_Value to be the cue to create a new object. The problem is not obvious first but is the limitation that Dapper.Net ORM decided to be built. You cannot have more than 7 mappings. The problem is even more huge when you learn that multi mapping is also the way to work with relationship (join). In our case, just the 1-1, the three 0-1 and the two 0-* take 6 of the multi mapping. This constraint is even worse when you think that some objects in relationship to the main one (contest) may also need to be loaded (multiple inner joins). For example, you are loading a Contest, that as a relationship to a list of User that participate which has a relation to a list of reputations which has a complex object for the type of reputation. Right there, we are using 3 mappings (contest->user, user->reputation, reputation->type). Very fast you hit the 7 multi mapping limitation of Dapper. The limitation of 7 multi-mappings is very arbitrary and could and should be unlimited. In SQL, you can create as much joins as you want and the ORM should follow that principle.

So, a way to get around limitation, you can create multiple select queries at a performance price. What is surprising is that few years ago, this limitation was raised and even got a pull request sent to the team to fix the 7 mappings. This one got rejected. Stackoverflow.com has a limited set of tables and not a rich business domain of classes as many enterprise software. This may justify that in their opinion that 7 multi-mappings is enough. That said, working with Dapper and Entity Framework can become more challenging than expected.

A second approach to work with a lot of complex type and relationship is to use a different class which flatten every table fields and then, using a more conventional classes mapping like AutoMapper to map the flatten table class into your rich domain class. However, this come with the cost of having more classes, the cost of having more mapping and finally, more unit test to write to ensure that the data goes from one place to the other one correctly.

A third approach is to use the basic dynamic query which return a row of object. You need to cast each fields but also you need to handle multiple rows from your joins. At that point, you are almost doing what you would do with ADO.Net.

A third Dapper.Net limitation is that it is not possible to configure this one to be wiser. Without being bloated like Entity Framework and without having to have a DbContext, Dapper.Net could have been a little more wise for mapping. You know ORM end with a "M" for "Mapping".

Overall, Dapper.Net can work in parallel of Entity Framework as long as you adapt some of your SQL habits. Reducing the number of join, doing multiple queries, etc. I found it easier to start including Dapper.Net for write scenario than read scenario. This is justify by the fact that most of my insert and update scenario where for 1 entity at a time which just require me to create a basic SQL insert or SQL update query, copy and paste it inside a String variable and bind the object into it and execute. Yet, my current experience with Dapper.Net is mitigated and I feel that the .Net environment still have some place for improvement in the ORM area.