Unit Testing a repo class gives error System.NotSupportedException because of SqlMapper.Execute Extension methods

Question

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;         
    }

You can not mock `SqlConnection` because that class instance is not provided via Unity Container and/or not provided by any Param - therefore it's not possible to mock it from outside -> you can create some Interface which creates the instance of `SqlConnection` this interface you can register in unity container or provide it as param in the method.. then you can mock it :) — Rampage64, Nov 16 '22 at 14:19
@DevFlamur can you provide me with a quick sample? or let me know what you think about edit 2? — dot, Nov 16 '22 at 14:20
is Connect_db() also on Repository Interface? then yes you can mock it like this: `Mock connectionMock = new Mock();` `repo.Setup(x=> x.Connect_db()).Returns(connectionMock )` `connectionMock.Setup(a=>a.Execute(It.isAny<>......).Returns(2)` hope that helps — Rampage64, Nov 16 '22 at 14:24
@DevFlamur having said that, is this a good approach in general for managing db connections? — dot, Nov 16 '22 at 14:27
no, not really - use UnitOfWork instead, check this 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 — Rampage64, Nov 16 '22 at 14:41

Jeanot Zubler · Answer 1 · 2022-11-16T14:35:50.720

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.

ah! makes sense! I'll see about refactoring the underlying class to have a separate method that returns a connection. That would work right? Please see Edit 2 — dot, Nov 16 '22 at 14:13

score 0 · Answer 2 · answered Nov 16 '22 at 14:49

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 :)

Unit Testing a repo class gives error System.NotSupportedException because of SqlMapper.Execute Extension methods

2 Answers2