Unit Testing a repo class gives error System.NotSupportedException because of SqlMapper.Execute Exte-CodePudding

I'm pretty new to c# (and fwiw, moq) and I'm trying learn our product by writing unit tests. We have the following method in a database repository class:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Newtonsoft.Json;
using Serilog;

    public bool UpdateInactiveWidgets(List<Widgets> widgets)
    {
        int rowsUpdated;
        using (var conn = new SqlConnection(connectionString))
        {              
            rowsUpdated= conn.Execute(@"UPDATE dbo.Widgets
                            SET [Status] = 'Inactive'
                            WHERE ID = @ID", widgets);
        }
        return rowsUpdated == widgets.Count ? true : false;         
    }

I'd like to write a unit test for this, but I can't seem to get the mock connection going. Based on other posts about mocking a connection, I've added the following to my unit test:

    [Fact]
    public void Update_Inactive_Widgets()
    {
        var repo = new WidgetRepository();
        var inActiveWidgets = new List<Widgets>()
        { //logic to create 2 widgets };

        var connectionMock = new Mock<IDbConnection>();
        connectionMock.Setup(m => m.Execute(It.IsAny<string>(),It.IsAny<object>(),null,null,null)).Returns(2);

        var result = repo.UpdateInactiveWidgets(inActiveWidgets );
        Assert.Equal(inActiveWidgets.Count,result);

The unit test fails on the connectionMock.Setup line with this error:

System.NotSupportedException: 'Unsupported expression: m => m.Execute(It.IsAny(), It.IsAny(), null, null, null) Extension methods (here: SqlMapper.Execute) may not be used in setup / verification expressions.'

I've found this article and am still making my way through to see how I can apply the points to my example: https://taurit.pl/moq-extension-methods-may-not-be-used-in-setup-verification-expressions/

But if you have any suggestions I'd appreciate it. I apologize in advance if I'm missing something basic.

Edit 2

If I made the connection a global variable in the class... and move the logic that connects outside the method under test would that help?

Sample pseudocode:

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Dapper;
using Newtonsoft.Json;
using Serilog;

    public UpdateInactiveWidgets()
    {
      var _conn;
    }
    public Connect_db()
    {
        _conn = new SqlConnection(connectionString);
    }
    public DisConnect_db()
    {
        //some logic to kill the db connection?
    }

    public bool UpdateInactiveWidgets(List<Widgets> widgets)
    {
        int rowsUpdated;
        //would the using() still be applicable? 
        using (_conn) 
        {              
            rowsUpdated= _conn.Execute(@"UPDATE dbo.Widgets
                            SET [Status] = 'Inactive'
                            WHERE ID = @ID", widgets);
        }
        return rowsUpdated == widgets.Count ? true : false;         
    }

CodePudding user response：

This does not answer your question directly, however it is futile anyway. Because you are actually creating the SqlConnection in your method under test, you cannot pass the mocked connection into the method. This means, that you would actually create a potentially live connection to the database, which is not advisable.

After seeing Edit2:

No, this in itself does not help. To be able to mock something, you have to pass the object in from outside. This can either be done in the constructor, or in the method itself.

Also, this would break when called a second time, as the connection is disposed at the end of the using block.

Your method could look something like:

public bool UpdateInactiveWidgets(List<Widgets> widgets, IDbConnection connection)
{
    int rowsUpdated;
    using (connection) 
    {              
        rowsUpdated= _conn.Execute(@"UPDATE dbo.Widgets
            SET [Status] = 'Inactive'
            WHERE ID = @ID", widgets);
    }
    return rowsUpdated == widgets.Count ? true : false;         
}

Now you run back into the problem of not being able to mock extension methods. You might look into the Execute method, and how it works. Perhaps you see a possible solution without using this unmockable function (something like CreateCommand() and Query()). Otherwise, the easiest possibility would be to wrap the SqlConnection in a custom class and mock this new wrapper class. See answers like this one

About the using statement: I am not sure entirely, but I think it should be removed, and added to the caller of the method. But please don't take my word for it.

CodePudding user response：

Just to make it Visible - it is possible to mock Methods using moq only if the source/instance is provided from outside and you have control over it from outside. In your case SqlConnection Instance is created within the UpdateInactiveWidgets (from Edit 1) and on (Edit 2) it's also not an good (by the way raw sql queries are also not good to have like this, you want to replace them by stored procedures or in best case Entity Framework and filter data by Linq) approach since there exists pattern for this i.e: Unit of Work

see reference: https://learn.microsoft.com/en-us/aspnet/mvc/overview/older-versions/getting-started-with-ef-5-using-mvc-4/implementing-the-repository-and-unit-of-work-patterns-in-an-asp-net-mvc-application

Hope that helped a little to get started :)