Unit Testing Dapper with Inline Queries

Here is our approach:

1. First of all, you need to have an abstraction on top of IDbConnection to be able to mock it:

   public interface IDatabaseConnectionFactory
   {
       IDbConnection GetConnection();
   }
   

2. Your repository would get the connection from this factory and execute the Dapper query on it:

   public class ProductRepository
   {
       private readonly IDatabaseConnectionFactory connectionFactory;
   
       public ProductRepository(IDatabaseConnectionFactory connectionFactory)
       {
           this.connectionFactory = connectionFactory;
       }
   
       public Task<IEnumerable<Product>> GetAll()
       {
           return this.connectionFactory.GetConnection().QueryAsync<Product>(
               "select * from Product");
       }
   }
   

3. Your test would create an in-memory database with some sample rows and check how the repository retrieves them:

   [Test]
   public async Task QueryTest()
   {
       // Arrange
       var products = new List<Product>
       {
           new Product { ... },
           new Product { ... }
       };
       var db = new InMemoryDatabase();
       db.Insert(products);
       connectionFactoryMock.Setup(c => c.GetConnection()).Returns(db.OpenConnection());
   
       // Act
       var result = await new ProductRepository(connectionFactoryMock.Object).GetAll();
   
       // Assert
       result.ShouldBeEquivalentTo(products);
   }
   

4. I guess there are multiple ways to implement such in-memory database; we used OrmLite on top of SQLite database:

   public class InMemoryDatabase
   {
       private readonly OrmLiteConnectionFactory dbFactory = new OrmLiteConnectionFactory(":memory:", SqliteOrmLiteDialectProvider.Instance);
   
       public IDbConnection OpenConnection() => this.dbFactory.OpenDbConnection();
   
       public void Insert<T>(IEnumerable<T> items)
       {
           using (var db = this.OpenConnection())
           {
               db.CreateTableIfNotExists<T>();
               foreach (var item in items)
               {
                   db.Insert(item);
               }
           }
       }
   }
   

I would like add another perspective on this problem and a solution that takes a different approach to solving it.

Dapper can be considered as a dependency on the repository class as it is an external codebase that we have no control over. Therefore testing it is not really in the realm of responsibility for Unit Testing (More in line with integration testing as you mentioned).

With that said, we cannot really mock Dapper directly because it is really just an extension method set on the IDbConnection interface. We could mock all of the System.Data code until we get down to the IDbCommand where Dapper really does its work. That however would be a lot of work, and in most cases not worth the effort.

We instead can create a simple IDapperCommandExecutor mock-able interface:

public interface IDapperCommandExecutor
{
    IDbConnection Connection { get; }

    T Query<T>(string sql, object? parameters = null);

    // Add other Dapper Methods as required...
}

This interface then can simply be implemented with Dapper:

public class DapperCommandExecutor : IDapperCommandExecutor
{
    public DapperCommandExecutor(IDbConnection connection)
    {
        Connection = connection;
    }

    IDbConnection Connection { get; }

    T Query<T>(string sql, object? parameters = null) 
        => Connection.QueryAsync<T>(sql, parameters);

    // Add other Dapper Methods as required...
}

Then all you would have to do is change the following:

var queryResult = sqlConn.Query<T>(query.RawSql, query.Parameters);

to

var queryResult = commandExecutor.Query<T>(query.RawSql, query.Parameters);

Then in your testing, you can create a mocked Command Executor

public class MockCommandExecutor : Mock<IDapperCommandExecutor>
{

    public MockCommandExecutor()
    {
        // Add mock code here...
    }

}

In summary, we do not need to test the Dapper library, it can, for unit testing, be mocked in. This mocked Dapper Command Executor will reduce the additional dependency requirement for an in-memory database and can reduce the complexity of your tests.

I adapted what @Mikhail did because I had issues when adding the OrmLite packages.

internal class InMemoryDatabase
{
    private readonly IDbConnection _connection;

    public InMemoryDatabase()
    {
        _connection = new SQLiteConnection("Data Source=:memory:");
    }

    public IDbConnection OpenConnection()
    {
        if (_connection.State != ConnectionState.Open)
            _connection.Open();
        return _connection;
    }

    public void Insert<T>(string tableName, IEnumerable<T> items)
    {
        var con = OpenConnection();

        con.CreateTableIfNotExists<T>(tableName);
        con.InsertAll(tableName, items);
    }
}

I've created a DbColumnAttribute so we can specify a specific column name for a classes property.

public sealed class DbColumnAttribute : Attribute
{
    public string Name { get; set; }

    public DbColumnAttribute(string name)
    {
        Name = name;
    }
}

I added some IDbConnection extensions for the CreateTableIfNotExists and InsertAll methods.

This is very rough so I've not mapped types correctly

internal static class DbConnectionExtensions
{
    public static void CreateTableIfNotExists<T>(this IDbConnection connection, string tableName)
    {
        var columns = GetColumnsForType<T>();
        var fields = string.Join(", ", columns.Select(x => $"[{x.Item1}] TEXT"));
        var sql = $"CREATE TABLE IF NOT EXISTS [{tableName}] ({fields})";

        ExecuteNonQuery(sql, connection);
    }

    public static void Insert<T>(this IDbConnection connection, string tableName, T item)
    {
        var properties = typeof(T)
            .GetProperties(BindingFlags.Public | BindingFlags.Instance)
            .ToDictionary(x => x.Name, y => y.GetValue(item, null));
        var fields = string.Join(", ", properties.Select(x => $"[{x.Key}]"));
        var values = string.Join(", ", properties.Select(x => EnsureSqlSafe(x.Value)));
        var sql = $"INSERT INTO [{tableName}] ({fields}) VALUES ({values})";

        ExecuteNonQuery(sql, connection);
    }

    public static void InsertAll<T>(this IDbConnection connection, string tableName, IEnumerable<T> items)
    {
        foreach (var item in items)
            Insert(connection, tableName, item);
    }

    private static IEnumerable<Tuple<string, Type>> GetColumnsForType<T>()
    {
        return from pinfo in typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance)
            let attribute = pinfo.GetCustomAttribute<DbColumnAttribute>()
            let columnName = attribute?.Name ?? pinfo.Name
            select new Tuple<string, Type>(columnName, pinfo.PropertyType);
    }

    private static void ExecuteNonQuery(string commandText, IDbConnection connection)
    {
        using (var com = connection.CreateCommand())
        {
            com.CommandText = commandText;
            com.ExecuteNonQuery();
        }
    }

    private static string EnsureSqlSafe(object value)
    {
        return IsNumber(value)
            ? $"{value}"
            : $"'{value}'";
    }

    private static bool IsNumber(object value)
    {
        var s = value as string ?? "";

        // Make sure strings with padded 0's are not passed to the TryParse method.
        if (s.Length > 1 && s.StartsWith("0"))
            return false;

        return long.TryParse(s, out long l);
    }
}

You can still use it the same way as @Mikhail mentions in Step 3.