Dapper: Unit Testing SQL Queries

Question

I am starting with Dapper, the micro-ORM, and i use the Dapper Rainbow. I want to test the queries and the data retrieved by them.

I mean, for example, i have the UserService with the method GetAll(), and i want to test that the sql query is retrieving all the users from some List (not from the database because i want the tests to be fast). Do you know how can i do that?

My service class (and the method i want to test):

public static class UserService{
    public static IEnumerable<User> GetAll(){
        return DB.Users.All();
    }
}

Do you have any advice about unit testing queries and data retrieving?

Thanks

Answer 1

I would suggest reading up on dependency injection and the repository pattern. If you take the approach that you have in the code above you will have a hard time mocking out the dependencies because the class and method is static.

Here is a better approach.

public interface IUserRepository
{
   IEnumerable<User> GetAll()
}

public class UserRepository : IUserRepository
{
  public IEnumerable<User> GetAll()
  { 
    return DB.Users.All();
  }
}

public class UserService
{
    IUserRepository _userRepository;
    public UserService(IUserRepository userRepository)
    {
      _userRepository = userRepository
    }

    public Enumerable<User> GetAll(){
        return _userRepository.GetAll();
    }
}

Now for testing you can mock out your repository. I use a mocking framework called NSubstitute which in my mind is a lot simpler than the others mentioned above, but that is a personal preference. To start with here is how you could write your test without any mocking framework.

public class FakeUserRepository : IUserRepository
{
  public IEnumerable<User> GetAll()
  { 
    return new List<User> { new User {FirstName='Bob', LastName='Smith'}, };
  }
}

And in your test

[Test]
public void GetAll_ShouldReturnAllFromFake()
{
   // Arrrange
   var userService = new UserService(new FakeUserRepository())
   // Act
   var result = userService.GetAll();
   // Assert
   var user = result[0];
   Assert.AreEqual("Bob", user.FirstName);
   Assert.AreEqual("Smith", user.LastName);   
}

This example is a little contrived as it doesn't really make sense to test that you can get data back from a fake repository. How you would use this in reality is if you had some business logic in your service that say got back a user and then check that they were older than a certain age or something. e.g. an IsLegalDrivingAge method on UserService.

Answer 2

With Dapper, your SQL is likely in string literals, perhaps mixed with C# conditionals, syntax not validated, DB references possibly wrong. Your instinct to test is a good one. However, running your code against your real DB is the only way of telling if it outputs a valid query. So the test you need here is an integration test. This is not hard and you can use your unit test framework to do it, but since the test must hit the real DB, you may not want to run it everywhere you run unit tests, not on your build server for instance.

Then, since Dapper is extension methods to ADO, to unit test the code that consumes your query you'll need to wrap it in the repository pattern. Dapper Wrapper seems to be the tool here.

If all this seems unnecessarily difficult, please try QueryFirst (disclaimer: I wrote it). You write your SQL in a real sql window, connected to your DB, sql validated as you type. Your query is integration tested against your DB every time you save the file, without you lifting a finger. Then, if the query runs, QueryFirst generates the wrapper code to let you use it, including an interface so you can easily mock the real query when unit testing the consuming code. That has to be a step forward, no?