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

asp.net-core asp.net-core-3.1 entity-framework-core stored-procedures xunit.net

Question

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;
    }
}
1
0
4/1/2020 12:53:08 PM

Accepted Answer

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);
    }
}
0
4/1/2020 4:28:28 PM

Popular Answer

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



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow