Pretty much anyone who knows me will tell you that I'm all about test driven development. It gives you a safety net to refactor code. Unit tests also act as accurate documentation for what tested code does and how it does it. Lately I've been messing with Dapper-dot-net. Since I wanted to use Dapper in one of my recent projects, I had to know how to test its use. This is an example of how to unit test a class encapsulating a database query with Dapper and some other tools.
In this example, I will be using these tools:
- Dapper-dot-net - this is a spiffy, micro OR/M.
- DapperWrapper - a wrapper for Dapper to aid with testing.
- NSubstitute - a handy mock framework.
- FluentAssertions - makes assertions readable.
- NBuilder - makes test data.
- A ContextSpecification class to format the tests.
- NuGet - manages and imports dependencies.
Getting Started
The solution contains two projects: the class library and the unit test library. All the dependencies, save one, were added using NuGet. System.Data had to be added manually. The image below shows the solution layout.
The solution contains two projects: the class library and the unit test library. All the dependencies, save one, were added using NuGet. System.Data had to be added manually. The image below shows the solution layout.
We'll be querying a database which has a table of orders. The entity which represents an entry in the database is the Order class. This is a simple DTO, made to hold the data stored in the table:
public class Order
{
public int OrderId { get; set; }
public int CustomerId { get; set; }
public int ShipperId { get;set; }
}
The First Test
The first test will check the class is returning the expected data:
public static class GetOrdersByCustomerIdTests
{
public class GetOrdersByCustomerIdContext : ContextSpecification
{
protected GetOrdersByCustomerId Sut;
protected IDbExecutor DbExecutor;
protected override void Context()
{
base.Context();
DbExecutor = Substitute.For<IDbExecutor>();
Sut = new GetOrdersByCustomerId();
}
}
[TestClass]
public class WhenThereAreOrdersToReturn : GetOrdersByCustomerIdContext
{
private IEnumerable<Order> result;
private List<Order> expectedResults;
protected override void Context()
{
base.Context();
expectedResults = Builder<Order>.CreateListOfSize(5)
.All().With(order => order.CustomerId = 1)
.Build()
.ToList();
DbExecutor.Query<Order>(Arg.Any<string>(), Arg.Any<object>())
.Returns(expectedResults);
}
protected override void BecauseOf()
{
result = Sut.Execute(DbExecutor);
}
[TestMethod]
public void ItShouldReturnTheOrders()
{
result.Should().BeEquivalentTo(expectedResults);
}
}
}
There are a few things going on in this test class. DapperWrapper is giving us a means to test the query. Fluent Assertions is giving a nice, readable assertion. NSubstitute is being used to create a mock IDbExecutor. NBuilder is making some test data to be returned by our mock.
To make the test pass, we need to create the following class:
public class GetOrdersByCustomerId
{
public IEnumerable<Order> Execute(IDbExecutor connection)
{
var query = connection.Query<Order>("", new object()); return query;
}
}
Adding the Query to the Test
As you can see, it's pretty simple. The next step is to ensure that the correct query is passed to the database. Since my testing is more BDD, I'm going to modify the Context() method. The Context() method represents the 'Given' of the Given-When-Then set.
DbExecutor.Query<Order>("select OrderId, CustomerId, ShipperId from Orders where CustomerId=@CustomerId", Arg.Any<object>()).Returns(expectedResults);
This sets the mock to only return the list when the query string matches our expectations. The second parameter, Arg.Any<object>(), sets the mock to match against any object passed into the method.
public class GetOrdersByCustomerId
{
public IEnumerable<Order> Execute(IDbExecutor connection)
{
var query = connection.Query<Order>("select OrderId, CustomerId, ShipperId from Orders where CustomerId=@CustomerId", new object());
return query;
}
}
Updating the class ensures the test will pass. The query won't really work though, because we're not passing in any parameters. If we were to actually try to use this class, it would throw an exception. So, now we'll start adding parameters to the query.
The First Parameter
With Dapper and DapperWrapper it's possible to simply add the parameter to the Query<T> extension method. The expectation is placed on the mock, and everything works. Here's the test class:
And the query class:
More Than One Parameter
You might notice the second parameter of the Query<T> method is an object. This lets us pass in the parameters using an anonymous object. Pretty spiffy:
However, the test for the class is not as clean as you might expect. When I first started, I thought the following might work:
The First Parameter
With Dapper and DapperWrapper it's possible to simply add the parameter to the Query<T> extension method. The expectation is placed on the mock, and everything works. Here's the test class:
[TestClass]
public class WhenThereAreOrdersToReturn : GetOrdersByCustomerIdContext
{
private IEnumerable<Order> result;
private List<Order> expectedResults;
private const int SampleCustomerId = 42;
protected override void Context()
{
base.Context();
expectedResults = Builder<Order>.CreateListOfSize(5)
.All().With(order => order.CustomerId = 1)
.Build()
.ToList();
DbExecutor.Query<Order>("select * from Orders where CustomerId=@CustomerId", SampleCustomerId)
.Returns(expectedResults);
}
protected override void BecauseOf()
{
Sut.CustomerId = SampleCustomerId;
result = Sut.Execute(DbExecutor);
}
[TestMethod]
public void ItShouldReturnTheOrders()
{
result.Should().BeEquivalentTo(expectedResults);
}
}
And the query class:
public class GetOrdersByCustomerId
{
public int CustomerId { get; set; }
public IEnumerable<Order> Execute(IDbExecutor connection)
{
var query = connection.Query<Order>("select * from Orders where CustomerId=@CustomerId",
CustomerId);
return query;
}
}
More Than One Parameter
You might notice the second parameter of the Query<T> method is an object. This lets us pass in the parameters using an anonymous object. Pretty spiffy:
public class GetOrdersByCustomerId
{
public int CustomerId { get; set; }
public int ShipperId { get; set; }
public IEnumerable<Order> Execute(IDbExecutor connection)
{
var query = connection.Query<Order>(
"select OrderId, CustomerId, ShipperId from Orders where CustomerId=@CustomerId and ShipperId=@ShipperId",
new { CustomerId, ShipperId });
return query;
}
}
However, the test for the class is not as clean as you might expect. When I first started, I thought the following might work:
[TestClass]
public class WhenThereAreOrdersToReturn : GetOrdersByCustomerIdContext
{
private IEnumerable<Order> result;
private List<Order> expectedResults;
private const int SampleCustomerId = 42;
private const int SampleShipperId = 31;
protected override void Context()
{
base.Context();
expectedResults = Builder<Order>.CreateListOfSize(5)
.All().With(order => order.CustomerId = 1)
.Build()
.ToList();
var foo = new { CustomerId = SampleCustomerId, ShipperId = SampleShipperId };
DbExecutor.Query<Order>("select * from Orders where CustomerId=@CustomerId", foo)
.Returns(expectedResults);
}
protected override void BecauseOf()
{
Sut.CustomerId = SampleCustomerId;
Sut.ShipperId = SampleShipperId;
result = Sut.Execute(DbExecutor);
}
[TestMethod]
public void ItShouldReturnTheOrders()
{
result.Should().BeEquivalentTo(expectedResults);
}
}
Unfortunately, this will still fail. NSubstitute fails to match the anonymous type. To get around this, we have to use the Arg.Do<object>() method with a lambda to snag the argument. This argument is assigned to a dynamic type, parameters. A little duck typing allows assertions against the values. The updated test looks like this:
[TestClass]
public class WhenThereAreOrdersToReturn : GetOrdersByCustomerIdContext
{
private IEnumerable<Order> result;
private List<Order> expectedResults;
private const int SampleCustomerId = 42;
private const int SampleShipperId = 31;
private dynamic parameters;
protected override void Context()
{
base.Context();
expectedResults = Builder<Order>.CreateListOfSize(5)
.All().With(order => order.CustomerId = 1)
.Build()
.ToList();
DbExecutor.Query<Order>("select * from Orders where CustomerId=@CustomerId and ShipperId=@ShipperId",
Arg.Do<object>(o => { parameters = o; }))
.Returns(expectedResults);
}
protected override void BecauseOf()
{
Sut.CustomerId = SampleCustomerId;
Sut.ShipperId = SampleShipperId;
result = Sut.Execute(DbExecutor);
}
[TestMethod]
public void ItShouldReturnTheOrders()
{
result.Should().BeEquivalentTo(expectedResults);
}
[TestMethod]
public void ItShouldUseTheCorrectParameters()
{
((int) parameters.CustomerId).Should().Be(SampleCustomerId);
((int) parameters.ShipperId).Should().Be(SampleShipperId);
}
}
While the test is correct, it was still failing. It was throwing an RuntimebinderException:
Test method DapperExample.UnitTests.GetOrdersByCustomerIdTests+WhenThereAreOrdersToReturn.ItShouldUseTheCorrectParameters threw exception: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: 'object' does not contain a definition for 'CustomerId'
It turns out that anonymous types are internal. The test assembly simply didn't have access to the properties of the anonymous type. The solution was pretty simple: expose the internal types to the unit test project. This is done by adding the following line to the AssemblyInfo.cs file:
[assembly: InternalsVisibleTo("DapperExample.UnitTests")]
Epilogue
Dapper is a nifty OR/M with a lot of features. DapperWrapper provides a simple way to do unit tests with projects using Dapper. Using the two, along with a few simple techniques, provides a means of writing good test which will document the code for posterity.
I like your blog because it has some good information regarding DOT NET Development Services which is very useful for me in the future.
ReplyDeleteHire .NET Developer | Custom ASP.NET Development