Dapper: Unit Testing SQL Queries

.net c# dapper tdd unit-testing

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

Popular Answer

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.



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why