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 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.

13 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
    • Hi,

      I’m assuming you mean the Mock object is returning nothing.
      1. Check the setup conditions. You can put a breakpoint and debug and see what calls were made on the Mock and figure out why it didn’t match the conditions.

      2. Be sure to have the repository use the thing you’re mocking (DapperWrapper), otherwise yeah, the Mock will do nothing.

      Reply
  1. It doesn’t make sense, you can use repository interface to reach the same purpose. However, to real test a dapper method, you probably have to connect to a database.

    Reply
    • Hi Steven,

      1. No, mocking out the repository is not the same as mocking out just Dapper. To simplify, you have Code -> Repository -> Dapper -> Database. The purpose of mocking out just Dapper is to remove the external dependency on the Database while at the same time keeping the real Repository code so it gets ran in the test. Sure, if you don’t want to unit test the real Repository code, then mock it out.

      2. In unit tests, you don’t want to connect to (and therefore depend upon) external systems. That makes the tests slow and fragile. That’s why you mock out Dapper – to remove the dependency on the database in the unit tests. In integration tests, you actually connect to the database and execute SQL queries. These are slower and fragile compared to unit tests, so you don’t run them as often. It’s a good idea to have multiple levels of testing – unit tests, integration tests, end to end tests, manual QA testing, etc… – they serve different purposes and are all important.

      Reply
    • Hi Marcia,

      I’m not seeing all the details here, so I can’t really give a specific answer. I’m going to guess you’re using the repository in ASP.NET Core already and now it’s failing because it can’t create the repository instance. This is because the repository now has a dependency on DapperWrapper. You’ll need to register DapperWrapper (i.e. with builder.Services.AddSingleton) in the initialization code.

      If this doesn’t answer your question, please provide more details.

      Reply
      • Thanks for the reply. I also was able to add QueryMultiple to the wrapper, but I´m not able to unit test because GridReader is an internal class. Any suggestion?

        Reply
        • Hey Marcia, now that’s a tricky challenge! I looked into this and I believe the simplest option is to use GridReader inside the QueryMultiple() wrapper method to get the results and return them in a tuple. That eliminates the need to deal wit the very-complicated GridReader class in the unit tests.

          I updated the article and added a section showing how to do this approach: Dealing with QueryMultiple()

          Reply

Leave a Reply to J Cancel reply