Mocking a DbConnection with NUnit/NSubstitute/AutoFixture and InsightDatabase

Question

We're using Nunit, NSubstitute and AutoFixture to test a repository class that's built on top of Insight database...

[TestFixture]
public class CalculationResultsRepositoryTests
{
    private IFixture _fixture;

    private IDbConnection _connection;
    private CalculationResultsRepository _calculationResultsRepository;

    [SetUp]
    public void Setup()
    {
        _fixture = new Fixture().Customize(new AutoConfiguredNSubstituteCustomization());
        _connection = _fixture.Freeze<IDbConnection>();
        _calculationResultsRepository = _fixture.Create<CalculationResultsRepository>();
    }

    [Test]
    public void TestReturnsPagedCalculationResults()
    {
        //Arrange
        var financialYear = _fixture.Create<int>();
        var pagedResults = _fixture.Create<PagedResults<ColleagueCalculationResult>>();
        _connection.QueryAsync(Arg.Any<string>(), Arg.Any<object>(), Arg.Any<IQueryReader<PagedResults<ColleagueCalculationResult>>>()).Returns(pagedResults);

        //Act
        var result = _calculationResultsRepository.PagedListAsync(financialYear);

        //Assert
        Assert.IsInstanceOf<PagedResults<ColleagueCalculationResult>>(result);
    }
}

However, when running the test we're seeing the following exception:

System.Reflection.TargetInvocationException : Exception has been thrown by the target of an invocation. ----> NSubstitute.Exceptions.UnexpectedArgumentMatcherException : Argument matchers (Arg.Is, Arg.Any) should only be used in place of member arguments. Do not use in a Returns() statement or anywhere else outside of a member call. Correct use: sub.MyMethod(Arg.Any()).Returns("hi") Incorrect use: sub.MyMethod("hi").Returns(Arg.Any())

We're at a bit of a loss with how to resolve this, however at a guess it seems to be something to do with the return type being defined as a generic within a parameter on this particular overload of the QueryAsync() extension method within InsightDatabase:

public static Task<T> QueryAsync<T>(this IDbConnection connection, string sql, object parameters, IQueryReader<T> returns, CommandType commandType = CommandType.StoredProcedure, CommandBehavior commandBehavior = CommandBehavior.Default, int? commandTimeout = default(int?), IDbTransaction transaction = null, CancellationToken? cancellationToken = default(CancellationToken?), object outputParameters = null);

Does anybody know how to successfully mock this?

For completeness the method call we're trying to substitute is this:

var results = await _connection.QueryAsync("GetCalculationResults", new { FinancialYearId = financialYearId, PageNumber = pageNumber, PageSize = pageSize },
                Query.ReturnsSingle<PagedResults<ColleagueCalculationResult>>()
                    .ThenChildren(Some<ColleagueCalculationResult>.Records));

Answer 1

You can substitute IDbConnection (also DbConnection for async API) using NSubstitute.Community.DbConnection.

 var mockConnection = Substitute.For<IDbConnection>().SetupCommands();

From now on quoting documentation:

You can mock queries:

mockConnection.SetupQuery("select * from MyTable").Returns(
        new { Foo = 1, Bar = "abc" },
        new { Foo = 2, Bar = "def" }
    );

Provide parameters to be passed to the query:

mockConnection.SetupQuery("select * from table where Id = @id")
        .WithParameter( "id", 1)
        .Returns(new { Id = 1, Name = "The first one"});

Setup data for a multiple Reader calls:

mockConnection.SetupQuery("select * from MyTable")
        .Returns(new { Never = 1, Eat = 1 })
        .ThenReturns(new { Shredded = 3, Wheat = 4 });
    );

So the answer would look like this:

      [TestFixture]    
            public class CalculationResultsRepositoryTests    
        {
        
            private IFixture _fixture;
            private IDbConnection _connection;
            private CalculationResultsRepository _calculationResultsRepository;
    
        [SetUp]
        public void Setup()
        {
            _fixture = new Fixture().Customize(new AutoConfiguredNSubstituteCustomization());
            _connection = Substitute.For<IDbConnection>()
            .SetupCommands(); // Important change here
            _calculationResultsRepository = _fixture.Create<CalculationResultsRepository>();
        }
    
        [Test]
        public void TestReturnsPagedCalculationResults()
        {
            //Arrange
            var financialYear = _fixture.Create<int>();
            var pageNumber= _fixture.Create<int>();
            var pageSize = _fixture.Create<int>();
            var pagedResults = _fixture.Create<PagedResults<ColleagueCalculationResult>>();

            _connection.SetupQuery("GetCalculationResults")
            .WithParameters(("FinancialYearId", financialYear), ("PageNumber", pageNumber), ("PageSize",pageSize))
            .Returns(pagedResults);
    
            //Act
            var result = _calculationResultsRepository.PagedListAsync(financialYear, pageNumber, pageSize);
    
            //Assert
            Assert.IsInstanceOf<PagedResults<ColleagueCalculationResult>>(result);
        }
    }

Answer 2

This probably isn't the best approach but as you can't mock extension methods and I don't have the time to write a test implementation of Insight this seems to be an acceptable solution for now...

Created IInsightDatabase interface:

public interface IInsightDatabase
{
    Task<T> QueryAsync<T>(string sql, object parameters, IQueryReader<T> returns, CommandType commandType = CommandType.StoredProcedure, CommandBehavior commandBehavior = CommandBehavior.Default, int? commandTimeout = default(int?), IDbTransaction transaction = null, CancellationToken? cancellationToken = default(CancellationToken?), object outputParameters = null);
}

Created concrete implementation of IInsightDatabase:

public class InsightDatabase : IInsightDatabase
{
    private readonly IDbConnection _connection;

    public InsightDatabase(IDbConnection connection)
    {
        _connection = connection;
    }

    public async Task<T> QueryAsync<T>(string sql, object parameters, IQueryReader<T> returns, CommandType commandType = CommandType.StoredProcedure, CommandBehavior commandBehavior = CommandBehavior.Default, int? commandTimeout = default(int?), IDbTransaction transaction = null, CancellationToken? cancellationToken = default(CancellationToken?), object outputParameters = null)
    {
        return await _connection.QueryAsync(sql, parameters, returns, commandType, commandBehavior, commandTimeout, transaction, cancellationToken, outputParameters);
    }
}

The concrete implementation is now injected into the repository class allowing that to be tested by mocking IInsightDatabase:

private IFixture _fixture;

private IInsightDatabase _insightDatabase;
private CalculationResultsRepository _calculationResultsRepository;

[SetUp]
public void Setup()
{
    _fixture = new Fixture().Customize(new AutoConfiguredNSubstituteCustomization());
    _insightDatabase = _fixture.Freeze<IInsightDatabase>();
    _calculationResultsRepository = _fixture.Create<CalculationResultsRepository>();
}

[Test]
public async Task PagedListAsync_ReturnsPagedResults()
{
    //Arrange
    var financialYearId = _fixture.Create<int>();
    var pagedResults = _fixture.Create<PagedResults<ColleagueCalculationResult>>();
    _insightDatabase.QueryAsync(Arg.Any<string>(), Arg.Any<object>(), Arg.Any<IQueryReader<PagedResults<ColleagueCalculationResult>>>()).Returns(pagedResults);

    //Act
    var result = await _calculationResultsRepository.PagedListAsync(financialYearId);

    //Assert
    result.Should().NotBeNull();
    result.Should().BeOfType<PagedResults<ColleagueCalculationResult>>();
    result.Should().Be(pagedResults);
}

Tah-dah! The repository class is now testable and Insights dealings with IDbConnection, calls to extension methods and all other nastiness is nicely tucked away in something that, although not testable, should be fairly difficult to break.

Answer 3

I did a few changes based on your test. See if it helps.

[Test]
public async Task TestReturnsPagedCalculationResults()
{
    //Arrange
    var financialYear = _fixture.Create<int>();
    var pagedResults = _fixture.Create<PagedResults<ColleagueCalculationResult>>();
    _connection.QueryAsync(null, null, null).ReturnsForAnyArgs(Task.FromResult(pagedResults));

    //Act
    var result = await _calculationResultsRepository.PagedListAsync(financialYear);

    //Assert
    Assert.IsInstanceOf<PagedResults<ColleagueCalculationResult>>(result);
}