xUnit测试数据库插入用户时遇System.NotSupportedException错误求助

问题场景 #

作为单元测试新手，编写测试UserRepository数据库插入用户的xUnit测试时，遇到以下错误：

System.NotSupportedException : 要模拟的类型(SqlConnection)必须是接口、委托或非密封、非静态类。

原UserRepository代码 #

public class UserRepository : IUserRepository
{
    public async Task<int> InsertUserAsync(User user, SqlConnection connection, SqlTransaction transaction)
    {
        if (!await UserExistAsync(user.Email!, connection, transaction))
        {
            using (SqlCommand cmd = new SqlCommand("INSERT INTO users (FLO, email) VALUES (@Flo, @Email);SELECT SCOPE_IDENTITY();", connection, transaction))
            {
                cmd.Parameters.AddWithValue("@Flo", user.Flo);
                cmd.Parameters.AddWithValue("@Email", user.Email);

                object? res = await cmd.ExecuteScalarAsync();
                return Convert.ToInt32(res);
            }
        }
        else
        {
            using (SqlCommand cmd = new SqlCommand("SELECT user_id FROM users WHERE email = @Email", connection, transaction))
            {
                cmd.Parameters.AddWithValue("@Email", user.Email);
                object? res = await cmd.ExecuteScalarAsync();
                return Convert.ToInt32(res);
            }
        }
    }

    public async Task<bool> UserExistAsync(string userEmail, SqlConnection connection, SqlTransaction transaction)
    {
        using (SqlCommand cmd = new SqlCommand("SELECT COUNT(1) FROM users WHERE email = @Email", connection, transaction))
        {
            cmd.Parameters.AddWithValue("@Email", userEmail);
            int count = Convert.ToInt32(await cmd.ExecuteScalarAsync());
            return count > 0;
        }
    }
}

原测试代码 #

public class UserRepositoryTests
{
    private readonly Mock<SqlConnection> _mockConnection;
    private readonly Mock<SqlTransaction> _mockTransaction;
    private readonly UserRepository _userRepository;

    public UserRepositoryTests()
    {
        _mockConnection = new Mock<SqlConnection>();
        _mockTransaction = new Mock<SqlTransaction>();
        _userRepository = new UserRepository();
    }

    [Fact]
    public async Task UserRepository_InsertUserAsync_ShouldInsertNewUser_WhenUserDoesNotExist()
    {
        // Arrange
        var mockCommand = new Mock<SqlCommand>();
        mockCommand.Setup(cmd => cmd.ExecuteScalarAsync()).ReturnsAsync(1);

        var mockUser = new User { Flo = "Иванов Иван Иванович", Email = "abc@email.com" };

        var mockConnection = new Mock<SqlConnection>();
        var mockTransaction = new Mock<SqlTransaction>();

        var userRepository = new Mock<UserRepository>();
        userRepository.Setup(repo => repo.UserExistAsync(It.IsAny<string>(), It.IsAny<SqlConnection>(), It.IsAny<SqlTransaction>()))
                      .ReturnsAsync(false); // 用户不存在

        // Act
        int result = await userRepository.Object.InsertUserAsync(mockUser, mockConnection.Object, mockTransaction.Object);

        // Assert
        Assert.Equal(1, result);
    }
}

错误原因 #

1. 密封类无法被Moq模拟：SqlConnection、SqlTransaction、SqlCommand都是.NET提供的密封类（sealed），Moq等模拟框架依赖继承目标类型生成动态代理，密封类不允许继承，因此无法直接模拟。
2. 测试逻辑错误：原测试代码Mock了UserRepository本身，导致调用的是Mock的方法而非真实业务逻辑，无法验证实际代码的正确性。

解决方案 #

步骤1：重构UserRepository，依赖抽象接口 #

将方法参数中的具体数据库类型替换为ADO.NET的抽象接口（IDbConnection、IDbTransaction），并通过IDbConnection.CreateCommand()创建命令，而非直接实例化SqlCommand，这样就能支持模拟。

重构后的代码：

public class UserRepository : IUserRepository
{
    public async Task<int> InsertUserAsync(User user, IDbConnection connection, IDbTransaction transaction)
    {
        if (!await UserExistAsync(user.Email!, connection, transaction))
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "INSERT INTO users (FLO, email) VALUES (@Flo, @Email);SELECT SCOPE_IDENTITY();";
                cmd.Transaction = transaction;

                var floParam = cmd.CreateParameter();
                floParam.ParameterName = "@Flo";
                floParam.Value = user.Flo;
                cmd.Parameters.Add(floParam);

                var emailParam = cmd.CreateParameter();
                emailParam.ParameterName = "@Email";
                emailParam.Value = user.Email;
                cmd.Parameters.Add(emailParam);

                // 适配异步执行，转换为SqlCommand调用专属异步方法
                if (cmd is SqlCommand sqlCmd)
                {
                    object? res = await sqlCmd.ExecuteScalarAsync();
                    return Convert.ToInt32(res);
                }
                throw new InvalidOperationException("仅支持SqlConnection");
            }
        }
        else
        {
            using (var cmd = connection.CreateCommand())
            {
                cmd.CommandText = "SELECT user_id FROM users WHERE email = @Email";
                cmd.Transaction = transaction;

                var emailParam = cmd.CreateParameter();
                emailParam.ParameterName = "@Email";
                emailParam.Value = user.Email;
                cmd.Parameters.Add(emailParam);

                if (cmd is SqlCommand sqlCmd)
                {
                    object? res = await sqlCmd.ExecuteScalarAsync();
                    return Convert.ToInt32(res);
                }
                throw new InvalidOperationException("仅支持SqlConnection");
            }
        }
    }

    public async Task<bool> UserExistAsync(string userEmail, IDbConnection connection, IDbTransaction transaction)
    {
        using (var cmd = connection.CreateCommand())
        {
            cmd.CommandText = "SELECT COUNT(1) FROM users WHERE email = @Email";
            cmd.Transaction = transaction;

            var emailParam = cmd.CreateParameter();
            emailParam.ParameterName = "@Email";
            emailParam.Value = userEmail;
            cmd.Parameters.Add(emailParam);

            if (cmd is SqlCommand sqlCmd)
            {
                int count = Convert.ToInt32(await sqlCmd.ExecuteScalarAsync());
                return count > 0;
            }
            throw new InvalidOperationException("仅支持SqlConnection");
        }
    }
}

步骤2：编写正确的测试代码 #

现在可以MockIDbConnection、IDbTransaction、IDbCommand，并测试真实的UserRepository实例：

public class UserRepositoryTests
{
    private readonly UserRepository _userRepository;

    public UserRepositoryTests()
    {
        _userRepository = new UserRepository();
    }

    [Fact]
    public async Task InsertUserAsync_ShouldReturnNewUserId_WhenUserDoesNotExist()
    {
        // Arrange
        var mockUser = new User { Flo = "Иванов Иван Иванович", Email = "abc@email.com" };

        // Mock IDbCommand并转换为SqlCommand以支持异步方法
        var mockCommand = new Mock<IDbCommand>();
        mockCommand.Setup(m => m.CreateParameter()).Returns(new Mock<IDbDataParameter>().Object);
        var mockSqlCommand = mockCommand.As<SqlCommand>();
        mockSqlCommand.Setup(m => m.ExecuteScalarAsync(default)).ReturnsAsync(1);

        // Mock IDbConnection
        var mockConnection = new Mock<IDbConnection>();
        mockConnection.Setup(m => m.CreateCommand()).Returns(mockCommand.Object);

        // Mock IDbTransaction
        var mockTransaction = new Mock<IDbTransaction>();

        // 模拟UserExistAsync返回false（用户不存在），同时调用真实的InsertUserAsync逻辑
        var repoMock = new Mock<UserRepository> { CallBase = true };
        repoMock.Setup(m => m.UserExistAsync(It.IsAny<string>(), It.IsAny<IDbConnection>(), It.IsAny<IDbTransaction>()))
                .ReturnsAsync(false);

        // Act
        int result = await repoMock.Object.InsertUserAsync(mockUser, mockConnection.Object, mockTransaction.Object);

        // Assert
        Assert.Equal(1, result);
        mockSqlCommand.Verify(m => m.ExecuteScalarAsync(default), Times.Once);
    }
}

补充说明 #

如果不想引入接口转换的复杂度，也可以使用SQLite内存模式等内存数据库替代模拟，直接测试真实的数据库操作逻辑，这种方式更贴近实际场景，适合集成测试。

内容的提问来源于stack exchange，提问作者dreemyy