How to unit test a function in .NET Core 3.1 that uses ExecuteSqlRawAsync to call a stored procedure?

user_11776483 0
Viewed 3278

I'm writing an API in ASP.NET Core 3.1, using EF Core to access a SQL Server database. I have a function in the API that needs to call a stored procedure with several input parameters and an output parameter. A simplified version of this function is below.

I am using a DbContext with .UseInMemoryDatabase() for other tests, but the in memory database cannot be used with stored procedures.

(This solution is database first, not code first. It would be possible to change the stored procedure if necessary but it would be much better if I didn't have to. I could change my C# function to call the stored procedure a different way though if that helps.)

How do I unit test this function?

public class MyFoo : IFoo
{
    public ApplicationDbContext DbContext { get; }

    public MyFoo(ApplicationDbContext dbContext)
    {
        DbContext = dbContext;
    }

    public async Task<bool> GetMyStoredProcResult(string val1, string val2, string val3, string val4, string val5)
    {
        // input validation removed for brevity
        var p1 = new SqlParameter
        {
            ParameterName = "p1",
            DbType = System.Data.DbType.String,
            Direction = System.Data.ParameterDirection.Input,
            Value = val1
        };
        // p2 - p5 removed for brevity
        var resultParam = new SqlParameter
        {
            ParameterName = "Result",
            DbType = System.Data.DbType.Boolean,
            Direction = System.Data.ParameterDirection.Output
        };
        var sql = "EXEC sp_MyProcedure @p1, @p2, @p3, @p4, @p5, @Result OUTPUT";
        _ = await DbContext.Database.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);
        return (bool)resultParam.Value;
    }
}
asp.net-core-3.1 entity-framework-core asp.net-core xunit.net stored-procedures
3 Answers

My final solution is based on the answer given by Stas Petrov. I wrapped the call to DbContext.Database.ExecuteSqlRawAsync() using an interface with a class that is added to the DI in Startup.ConfigureServices().

I created the following interface and class:

public interface IStoredProcedureExecutor
{
    public Task<int> ExecuteSqlRawAsync(string sql, params object[] parameters);
}

public class StoredProcedureExecutor : IStoredProcedureExecutor
{
    public ApplicationDbContext DbContext { get; }

    public StoredProcedureExecutor(ApplicationDbContext dbContext)
    {
        DbContext = dbContext;
    }

    public Task<int> ExecuteSqlRawAsync(string sql, params object[] parameters)
    {
        return DbContext.Database.ExecuteSqlRawAsync(sql, parameters);
    }
}

In my code from the question, I replaced this call:

_ = await DbContext.Database.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);

With this:

_ = await StoredProcedureExecutor.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);

Then in the test code, I created this class which I instantiate, set a suitable ReturnValue, and then insert into the class I'm testing instead of StoredProcedureExecutor:

class TestStoredProcedureExecutor : IStoredProcedureExecutor
{
    public bool ReturnValue { get; set; }

    public Task<int> ExecuteSqlRawAsync(string sql, params object[] parameters)
    {
        foreach (var param in parameters)
        {
            var p = (SqlParameter)param;
            if (p.Direction == System.Data.ParameterDirection.Output) p.Value = ReturnValue;
        }
        return Task.FromResult(0);
    }
}
user_117764830

In unit testing you can assume that other parts and external calls are always executing ok. So you can mock calls like this. One of way is implement some strategy with moving procedure calls into interface with specific realizations

interface IProcedureExecutor
{
    void MyProcedure();
}

class DefaultProcedureExecutor: IProcedureExecutor
{
    public void MyProcedure()
    {
        //call procedure in database
    }
}

class MockedProcedureExecutor: IProcedureExecutor
{
    public void MyProcedure()
    {
        //do some direct data operations with in memory database
    }
}

Then pass IProcedureExecutor in your MyFoo and replace instance with mocked one during unit testing.

This is just an example. You also can mock any parts like this

user_46400450

The problem is the ExecuteSqlRawAsync method is static.

If there's just one place you need to mock it then it might be overkill to introduce a new type and add it to the container.

It may be sufficient to use the old "extract + override" mocking trick.

"Extract" the static call into this new method:

internal virtual async Task<int> ExecuteSqlRawAsync(MyContext context, string sql, CancellationToken cancellationToken)
{
  return await context.Database.ExecuteSqlRawAsync(sql, cancellationToken);
}

And replace the original call with one to the new method:

//await context.Database.ExecuteSqlRawAsync(sql, p1, p2, p3, p4, p5, resultParam);
await ExecuteSqlRawAsync(context, sql, p1, p2, p3, p4, p5, resultParam);

In the test, you can now "override" (i.e. mock) that method.

In theory, it's not perfect - you don't know that the method is implemented properly (sometimes you can write another test to check the method's behavior, or just accept it works by including it in an integration test). But in many cases this approach is good enough.

user_99714040
Related