C# – How to unit test code that uses Dapper

Dapper makes your code difficult to unit test. The problem is that Dapper uses static extension methods, and static methods are difficult to mock out.

One approach is to wrap the Dapper static methods in a class, extract out an interface for that wrapper class, and then dependency inject the wrapper interface. In the unit tests, you can then mock out the wrapper interface.

In this article, I’ll show how to do this approach.

First, the repository code using Dapper

Let’s start by looking at repository code that is using Dapper to execute a query:

public class MovieRepository { private readonly string ConnectionString; public MovieRepository(string connectionString) { ConnectionString = connectionString; } public IEnumerable<Movie> GetMovies() { using(var connection = new SqlConnection(ConnectionString)) { return connection.Query<Movie>("SELECT Name, Description, RuntimeMinutes, Year FROM Movies"); } } }
Code language: C# (cs)

To make this code unit testable, we need to mock out the static connection.Query() method. Right now, this is actually connecting to the database and executing the query.

We can use the technique explained in this article about mocking out static methods:

  • Wrap the static method calls in a class and extract out an interface for the wrapper.
  • Dependency inject the interface into the repository.
  • In the unit tests, mock out the wrapper interface and pass it into the repository.

Wrap the static Dapper method

Create a class and wrap the static Query() method:

using Dapper; public class DapperWrapper : IDapperWrapper { public IEnumerable<T> Query<T>(IDbConnection connection, string sql) { return connection.Query<T>(sql); } }
Code language: C# (cs)

Notice this is not passing in all the optional parameters that the Dapper method uses. This simplifies things a little bit. If you really aren’t using the other parameters, you might as well leave them out of the wrapper class.

Now extract an interface from the wrapper class:

public interface IDapperWrapper { IEnumerable<T> Query<T>(IDbConnection connection, string sql); }
Code language: C# (cs)

Dependency inject the wrapper interface into the repository

Add IDapperWrapper as a constructor parameter in MovieRepository:

private readonly IDapperWrapper DapperWrapper; public MovieRepository(string connectionString, IDapperWrapper dapperWrapper) { ConnectionString = connectionString; DapperWrapper = dapperWrapper; }
Code language: C# (cs)

Write a unit test and mock out the wrapper

The following test verifies that the repository is using DapperWrapper to execute the expected SQL Query with a properly built IDbConnection object:

[TestMethod()] public void GetMoviesTest_ReturnsMoviesFromQueryUsingExpectedSQLQueryAndConnectionString() { //arrange var mockDapper = new Mock<IDapperWrapper>(); var expectedConnectionString = @"Server=SERVERNAME;Database=TESTDB;Integrated Security=true;"; var expectedQuery = "SELECT Name, Description, RuntimeMinutes, Year FROM Movies"; var repo = new MovieRepository(expectedConnectionString, mockDapper.Object); var expectedMovies = new List<Movie>() { new Movie() { Name = "Test" } }; mockDapper.Setup(t => t.Query<Movie>(It.Is<IDbConnection>(db => db.ConnectionString == expectedConnectionString), expectedQuery)) .Returns(expectedMovies); //act var movies = repo.GetMovies(); //assert Assert.AreSame(expectedMovies, movies); }
Code language: C# (cs)

At first this test will fail because the code hasn’t been updated to actually use DapperWrapper, so it’s still trying to actually connect to the database (which times out after 15 seconds and throws an exception).

Ok, let’s update the code to use DapperWrapper:

public IEnumerable<Movie> GetMovies() { using(var connection = new SqlConnection(ConnectionString)) { return DapperWrapper.Query<Movie>(connection, "SELECT Name, Description, RuntimeMinutes, Year FROM Movies"); } }
Code language: C# (cs)

Now the test passes.

Since it’s mocking out Dapper, it’s not really connecting to the database. This makes the test deterministic and fast – two qualities of a good unit test.

Unit test a parameterized query

Update: Added this new section 2021-10-19.

In this section, I’ll show how to do the same approach shown above to unit test a parameterized query.

Let’s say you want to unit test the following parameterized query:

public IEnumerable<Movie> GetMoviesWithYear(int year) { using (var connection = new SqlConnection(ConnectionString)) { return connection.Query<Movie>("SELECT * FROM Movies WHERE Year=@year", new { year }); } }
Code language: C# (cs)

1 – Wrap the Query() method

When you’re executing a parameterized query with Dapper, you have to pass in the object param parameter. So in DapperWrapper, wrap this variation of the Query() method:

public class DapperWrapper : IDapperWrapper { public IEnumerable<T> Query<T>(IDbConnection connection, string sql) { return connection.Query<T>(sql); } public IEnumerable<T> Query<T>(IDbConnection connection, string sql, object param) { return connection.Query<T>(sql, param); } }
Code language: C# (cs)

Note: ‘object param’ is an optional parameter of Query() in Dapper. To keep the wrapper as simple as possible, it’s better to not have optional parameters. Add overloads with the parameter instead.

2 – Update the method to use the wrapper

Replace the call to connection.Query() with DapperWrapper.Query():

public IEnumerable<Movie> GetMoviesWithYear(int year) { using (var connection = new SqlConnection(ConnectionString)) { return DapperWrapper.Query<Movie>(connection, "SELECT * FROM Movies WHERE Year=@year", new { year }); } }
Code language: C# (cs)

3 – Mock the wrapper method

Normally when you execute parameterized queries with Dapper, you pass in an anonymous type with the query parameters. This keeps things nice and clean. However, this makes it a little tricky to set up the mock.

There are three options you can do for specifying the object param parameter in the mock setup.

Option 1 – Use It.IsAny<object>()

If you aren’t concerned about precisely matching the object param parameter, you can use It.IsAny<object>() in the mock setup:

mockDapper.Setup(t => t.Query<Movie>(It.Is<IDbConnection>(db => db.ConnectionString == expectedConnectionString), expectedQuery, It.IsAny<object>())) .Returns(expectedMovies);
Code language: C# (cs)

Option 2 – Use It.Is<object> + reflection

If you want to check the values on the anonymous type, you can use It.Is<object> with reflection:

mockDapper.Setup(t => t.Query<Movie>(It.Is<IDbConnection>(db => db.ConnectionString == expectedConnectionString), expectedQuery, It.Is<object>(m => (int)m.GetType().GetProperty("year").GetValue(m) == 2010))) .Returns(expectedMovies);
Code language: C# (cs)

Option 3 – Pass in a non-anonymous type

The difficulty with setting up the mock is caused by dealing with the anonymous type. You can pass in a non-anonymous type instead, which simplifies the mock setup.

First, change the code in the repository by passing in a non-anonymous type. In this example, the existing Movie class can be used for this.

public IEnumerable<Movie> GetMoviesWithYear(int year) { using (var connection = new SqlConnection(ConnectionString)) { return DapperWrapper.Query<Movie>(connection, "SELECT * FROM Movies WHERE Year=@year", new Movie() { Year = year }); } }
Code language: C# (cs)

The mock setup can then check this parameter directly:

mockDapper.Setup(t => t.Query<Movie>(It.Is<IDbConnection>(db => db.ConnectionString == expectedConnectionString), expectedQuery, It.Is<Movie>(m => m.Year == 2010))) .Returns(expectedMovies);
Code language: C# (cs)

3 thoughts on “C# – How to unit test code that uses Dapper”

    • I updated the article to answer your question. Please see section Unit test a parameterized query.

      In short, you have three options for setting up the mock to match the ‘object param’ parameter: 1) Use It.IsAny<object>() 2) Use It.Is<object> + reflection or 3) Pass in a non-anonymous type instead and then use, for example, It.Is<Movie>(m => m.Year == 2010) to directly check the parameter

      Reply

Leave a Comment