I'm new to writing unit testing I'm trying to write a unit test for inserting a user into a database but I'm getting an error Error: System.NotSupportedException : Type to mock (SqlConnection) must be an interface, a delegate, or a non-sealed, non-static class.
I have a UserRepository class:
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;
}
}
}
I need to test his methods
I wrote the following class for tests, so far only 1 method, but received an error message
Test class:
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 = "[email protected]" };
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);
}
}
System.NotSupportedException : Type to mock (SqlConnection) must be an interface, a delegate, or a non-sealed, non-static class.
This error is telling you that Moq is not able to create a mock for SqlConnection as it is a sealed class.
SqlCommand, SqlConnection and SqlTransaction are all sealed classes and Moq will not be able to mock them.
You can work against the interfaces and in your UserRepository class you could use IDbConnection and IDbTransaction instead.
You create the command from the connection then instead await using (var cmd = (SqlCommand)_connection.CreateCommand()){...}
The problem is Moq, when creating the setup for CreateCommand() Moq creates Castle.Proxies.IDbCommandProxy which can't be cast to SqlCommand
I find the easiest is to abstract the command execution away from the repository. Your repository class has logic that needs to be tested but you don't need to test the command execution.
You can create a class which runs your commands and inject that into your repository instead. then you can mock that easily and test your logic.
In my example I named the interface ICommandRunner and implementation SqlCommandRunner but you can name it which ever you like best.
I used IDb interfaces but you don't have to.
public interface ICommandRunner
{
Task<int> InsertUserAsync(User user);
Task<int> GetUserIdByEmailAsync(string userEmail);
Task<bool> UserExistAsync(string userEmail);
}
public sealed class SqlCommandRunner : ICommandRunner
{
private readonly IDbConnection _connection;
private readonly IDbTransaction _transaction;
public SqlCommandRunner(IDbConnection connection, IDbTransaction transaction)
{
_connection = connection;
_transaction = transaction;
}
public async Task<int> InsertUserAsync(User user)
{
await using (var cmd = (SqlCommand)_connection.CreateCommand())
{
cmd.CommandText = "INSERT INTO users (FLO, email) VALUES (@Flo, @Email);SELECT SCOPE_IDENTITY();";
cmd.Transaction = (SqlTransaction)_transaction;
cmd.Parameters.AddWithValue("@Flo", user.Flo);
cmd.Parameters.AddWithValue("@Email", user.Email);
var res = await cmd.ExecuteScalarAsync();
return Convert.ToInt32(res);
}
}
public async Task<int> GetUserIdByEmailAsync(string userEmail)
{
await using (var cmd = (SqlCommand)_connection.CreateCommand())
{
cmd.CommandText = "SELECT user_id FROM users WHERE email = @Email";
cmd.Transaction = (SqlTransaction)_transaction;
cmd.Parameters.AddWithValue("@Email", userEmail);
var res = await cmd.ExecuteScalarAsync();
return Convert.ToInt32(res);
}
}
public async Task<bool> UserExistAsync(string userEmail)
{
await using (var cmd = (SqlCommand)_connection.CreateCommand())
{
cmd.CommandText = "SELECT COUNT(1) FROM users WHERE email = @Email";
cmd.Transaction = (SqlTransaction)_transaction;
cmd.Parameters.AddWithValue("@Email", userEmail);
var count = Convert.ToInt32(await cmd.ExecuteScalarAsync());
return count > 0;
}
}
}
You can now inject that into your UserRepository and use it instead.
public class UserRepository : IUserRepository
{
private readonly ICommandRunner _commandRunner;
public UserRepository(ICommandRunner commandRunner)
{
_commandRunner = commandRunner;
}
public async Task<int> InsertUserAsync(User user)
{
if (!await UserExistAsync(user.Email!))
{
return await _commandRunner.InsertUserAsync(user);
}
return await _commandRunner.GetUserIdByEmailAsync(user.Email!);
}
public async Task<bool> UserExistAsync(string userEmail)
{
return await _commandRunner.UserExistAsync(userEmail);
}
}
Your unit tests can now focus on the logic in the repository.
public class UserRepositoryTests
{
private readonly Mock<ICommandRunner> _mockCommandRunner;
private readonly UserRepository _userRepository;
public UserRepositoryTests()
{
_mockCommandRunner = new Mock<ICommandRunner>();
_userRepository = new UserRepository(_mockCommandRunner.Object);
}
[Fact]
public async Task UserRepository_InsertUserAsync_ShouldInsertNewUser_WhenUserDoesNotExist()
{
// Arrange
var user = new User { Flo = "Иванов Иван Иванович", Email = "[email protected]" };
_mockCommandRunner
.Setup(cr => cr.UserExistAsync(user.Email))
.ReturnsAsync(false);
_mockCommandRunner
.Setup(cr => cr.InsertUserAsync(user))
.ReturnsAsync(1);
// Act
var result = await _userRepository.InsertUserAsync(user);
// Assert
Assert.Equal(1, result);
_mockCommandRunner.Verify(cr => cr.UserExistAsync(It.IsAny<string>()), Times.Once);
_mockCommandRunner.Verify(cr => cr.InsertUserAsync(It.IsAny<User>()), Times.Once);
}
}
The above is just an example how to decouple from the command execution and not having to worry about mocking sealed classes or IDb interfaces.
The solution might not fit your specific situation but hopefully helped somewhat, allowing yout to solve the testing problem you encountered.
You still execute the commands in the SqlCommandRunner but that simply executes the commands. You still want integration test but no unit tests are needed for it.