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.
Table of Contents
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 I typically use for 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 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> and get the property 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)Dealing with QueryMultiple()
Added this section 2023-08-21 after a reader asked how to do it in the comments.
Dapper’s QueryMultiple() method is rather tough to deal with in unit tests because it returns a GridReader, which has no public constructor. One complicated option is to use reflection to call its internal constructor, configuring and passing in its dependencies. But that means you’ll have to know precisely how GridReader is implemented to get it configured properly.
I have a simpler solution:
- Wrap QueryMultiple() (using the same wrap-and-mock technique shown throughout this article).
- Don’t return a GridReader from the wrapper method.
- Instead, read the multiple results with GridReader in the wrapper method and return them in a tuple.
- In the unit test, mock out the wrapper method.
This eliminates the need to deal with the complicated GridReader class. I’ll show an example of how to do this.
1 – Wrap QueryMultiple() in DapperWrapper
In the DapperWrapper class:
- Add a QueryMultiple() method with at least two generic type parameters.
- Call Dapper’s QueryMultiple() to get the GridReader.
- Use GridReader.Read() to get the IEnumerable for each result set.
- Return the two IEnumerables in a tuple.
Like this:
using Dapper;
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);
}
public (IEnumerable<T1>, IEnumerable<T2>) QueryMultiple<T1, T2>(IDbConnection connection, string sql)
{
var reader = connection.QueryMultiple(sql);
return (reader.Read<T1>(), reader.Read<T2>());
}
}
Code language: C# (cs)Note: Also put this method in the IDapperWrapper interface.
2 – Use the wrapper method in the repository class
Use DapperWrapper.QueryMultiple() in the repository class. In this example, it’s returning the multiple results in a named tuple.
public (IEnumerable<Movie> Movies, IEnumerable<Show> Shows) GetMoviesAndShows()
{
using(var connection = new SqlConnection(ConnectionString))
{
return DapperWrapper.QueryMultiple<Movie, Show>(connection, "SELECT * FROM Movies;SELECT * FROM Shows");
}
}
Code language: C# (cs)3 – Mock out QueryMultiple() in a unit test
Now in the unit test, you can mock out the DapperWrapper.QueryMultiple() wrapper method to return multiple results in a tuple, like this:
[TestMethod()]
public void QueryMultipleTest()
{
//arrange
var mockDapper = new Mock<IDapperWrapper>();
string expectedConnectionString = @"Server=SERVERNAME;Database=TESTDB;Integrated Security=true;";
string expectedQuery = "SELECT * FROM Movies;SELECT * FROM Shows";
var repo = new MovieRepository(expectedConnectionString, mockDapper.Object);
var expectedMovies = new List<Movie>() { new Movie() { Name = "TestMovie" } };
var expectedShows = new List<Show>() { new Show() { Name = "TestShow" } };
mockDapper.Setup(t => t.QueryMultiple<Movie, Show>(
It.Is<IDbConnection>(db => db.ConnectionString == expectedConnectionString),
expectedQuery))
.Returns((expectedMovies, expectedShows));
//act
var results = repo.GetMoviesAndShows();
//assert
Assert.AreSame(expectedMovies, results.Movies);
Assert.AreSame(expectedShows, results.Shows);
}
Code language: C# (cs)Because you mocked out the wrapper method, you don’t need to deal with the complicated GridReader.
Comments are closed.