How to write unit test case for ExecuteSqlRawAsync

Question

I want to write a unit test case using NUnit and Moq for the below method. The method has a ExecuteSqlRawAsync which insert data from one table to another. Here in case of any exception I will rollback the transaction. In my unit test case, I am hoping to somehow mock the DbContext.Database.ExecuteSqlRawAsync() function.

public async Task<bool> InsertxxyyyFromStagingDataAsync(CancellationToken cancellationToken)
{
    try
    {
        using (var transaction = _connectionFactory.CreateConnection().BeginTransaction())
        {
            try
            {
                await _dbContext.Database.ExecuteSqlRawAsync($"INSERT INTO xxxxx SELECT * FROM STAGING_xxxx", cancellationToken);
                await _dbContext.Database.ExecuteSqlRawAsync($"INSERT INTO yyyyy SELECT * FROM STAGING_yyyy", cancellationToken);

                return true;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                return false;
            }
        }
    }
    catch (Exception ex)
    {
        return false;
    }

}

For the above method, I tried below test and getting exception in unit test case. System.NotSupportedExceptionUnsupported expression....ExecuteSqlRawAsync(It.IsAny<string>(), It.IsAny<CancellationToken>()) Extension methods (here: RelationalDatabaseFacadeExtensions.ExecuteSqlRawAsync) may not be used in setup / verification expressions.

[Test]
public async Task InsertDataFromStagingDataAsync_Error()
{
    var options = new DbContextOptionsBuilder<MYDBContext>().UseInMemoryDatabase(databaseName: "InMemoryDb")
                                           .ConfigureWarnings(x => x.Ignore(InMemoryEventId.TransactionIgnoredWarning))
                                           .Options;

    var connectionFactory = new Mock<IDbConnectionFactory>();
    var connectionMock = new Mock<IDbConnection>();

    var dbContextMock = new Mock<MYDBContext>();

    // Set up expectations for ExecuteSqlRawAsync
    dbContextMock.Setup(db => db.Database.ExecuteSqlRawAsync(It.IsAny<string>(), It.IsAny<CancellationToken>()))
        .ReturnsAsync(1); // Assuming 1 row affected for successful insert

    var repositoryObj = new Repository(_mockHttpClientFactory.Object, _mockAppContainerOption.Object, dbContextMock.Object, logger.Object, null, connectionFactory.Object);

    var result = repositoryObj.InsertxxyyFromStagingDataAsync(new CancellationToken());
    // Assert
    Assert.IsNotNull(result);

}

This is how I start the unit test case, which is getting failed on execution. In my unit test case, I am hoping to somehow mock the DbContext.Database.ExecuteSqlRawAsync() function. I am a beginner to unit test. Can someone help me out?.

Answer 1

You can use Test Containers which can handle SQL/HTTP requests.

It uses Docker on the behind also you can use on CI/CD.

For ASP.Net, you can check this example

Answer 2

You can't replace non-interface and non-virtual/abstract members with Test Doubles. This is true for Moq, too. As far as I can tell from the documentation, the various ExecuteSqlRawAsync overloads are static method that are not part of an interface. This means that you can't override their implementation.

You may be able to gain access to the underlying source code of the overload in question in order to replace the underlying virtual or interface method invocations with Test Doubles, but you'd then be testing against an implementation detail of a library that you don't control. This means that if/when Microsoft changes the implementation of that method, your test would break. You'd be locked in Framework Whac-A-Mole.

Instead, either write integration tests or apply the Dependency Inversion Principle.