DapperDinner Part 2: Improving the Queries

In my last post I demonstrated how easy it was to convert an existing web site (in this case Nerd Dinner) to use the Dapper micro ORM. In this post I will continue with the sample I started and improve the solution, showing that with a few more minor tweaks we can gain a cleaner and faster solution using Dapper.

The solution in my last post did not fully take advantage of Dapper, it merely replaced the Entity Framework repository with a Dapper repository. Whilst this solution works it did not require any changes to code outside the repository; there are a number of huge performance increases we can get if we make some additional changes.

The Nerd Dinner Code Base

Looking at the code base for Nerd Dinner this has changed quite a lot since Scott Guthrie created the original Nerd Dinner tutorial here. A number of improvements have been added such as OAuth, Twitter, iCal support to name a few, and as such the code base is not as 'clean' as it once was.

The first step in improving the implementation is to find out what type of queries are performed in the current application. Even though a number of different queries are performed against the database we can simplify these down to two distinct types:

1. Returning a single Dinner with the list of RSPV's;
2. Return a paged list of Dinners with their respective RSVP count.

The existing Nerd Dinner application does have some data access logic within the controller. As the Entity Framework repository returns an IQueryable object, additional filtering can be done within the controller, which in turn modifies the Sql statement so no unnecessary results are returned from the database.

I personally like to keep the MVC Controllers as simple as possible, and when calling the repository layer I prefer the data to already be filtered and/or paged. This I think allows for the creation of simpler and cleaner unit tests. All the Controller tests need to do is make sure the correct view is returned, as all the logic for filtering and paging is testing within the repository unit test.

Knowing all this we can now start on modifying the code base to take full advantage of Dapper.

The Dapper Dinner Code Base

The following are the changes I make to the Nerd Dinner code base:

Before any changes can be made to the repository I first need to store the RSVP count somewhere. As most views only use the RSVP count rather than displaying each individual one it is wasteful to return all from the database and adding them up. To get round this I've added a nullable integer property onto the Dinner object the will store the value.

As Dapper cannot take advantage of IQueryable I'm going to create a more robust repository that allows a greater flexibility in the filtering and paging than the existing repository. Below is the interface for the new repository:

As you can see I'm taking advantage of the new C# 4.0 feature optional arguments, this will allow the calls for the Controller to be simplified and more readable while still giving flexibility needed.

To be able to return a paged list I'm taking advantage of the ROW_NUMBER function within Sql, more details about it can be found here. This allows efficient queries to the database to be called without returning unnecessary data. The actual sql used to query the database for the Dinners and RSVP count is as follows:

This uses nested select statements for the paging so the results can also be sorted by the RSVP count. If this functionality was not needed it would only require one level of nested queries. The actual code used to call the query is as follows:

This takes advantage of the Dapper Sql Builder; it allows the building up of queries which is useful when a number of different criteria need to be in the where clause but the rest of the query remains the same. A second query is also performed; this is to get the total number of Dinners and is used for the paged object so we know if there are any additional pages when displaying the results.

Now that the repository has been changed all the controller calls to the repository need to be changed as well. This is relatively simple because all the logic has been moved out of the controller which means that the majority of code can be replaced with a single line of code to call the method in the repository.

Conclusion

As you can see with a few more additional changes to an application, the power of Dapper really starts to show.

Looking at the DapperDinner solution now, it is a much cleaner implementation. The code in the controllers has been simplified, the repository layer is now more flexible to handle different queries, and all in all the solution is a lot cleaner and easier to follow.

Using Dapper I have not encountered any major hurdles, and have not yet found anything that it cannot do. This solution is now looking very lean with the exception of adding and editing data, which I will be focusing on in my next post.

As always, feel free the get the code from my GitHub page here and fork it and change/improve it as you see fit.