How can I test Dapper queries in .net core?

Question

With code targeting the full .net Framework I could mock up an IDbConnection and point it at a mocked DataSet in order to test that my queries are executing correctly. Similarly if I were using EntityFramework 6 I could have a mocked DbSet return IQueryables and test my data layer logic against that.

However .net core doesn't support DataSets (though that may change in the future?).

In the meantime, is there a way to create a collection of objects which dapper can query using an IDbConnection in order to test the query logic?

Answer 1

No, all dapper is, are extension methods on top of the IDbConnection class.

There is no InMemory implementation for this (IDbConnection) (that understands SQL strings).

Your best bet however, if you want to run it completely autonomous, would be to spin up a new sql server for each time you run unit tests. This can easily be done with the docker image that Microsoft has made for sqlserver: https://hub.docker.com/r/microsoft/mssql-server-linux/

or...

Or migrate to Entity framework, they allow you to unit test against an in-memory backing store.

why?

Dapper just contains some useful features to generate SQL. It by no means abstracts away from SQL. And sql is just plain text for C# code. it does not parse it, nor execute it. Thus you cant unit test your sql/dapper code without using a database behind it.

Entity framework does it differently. it tries to make, everything that you would want to do in a database into C# code/abstraction (eg the IDbCollection). Then they make 1 implementation that generates sql code and one implementation that uses in-memory backing store. this way you can unit test your code.

Microsofts solution

Microsoft often advertises using the Repository Pattern. This is basically an expensive word for abstracting all your database calls/commands into a separate class and interfacing these classes, and use the interfaces everywhere in code (using dependency injection). Now you can write unit tests that test all your code expect for the sql queries, for this interface you make a mock to test if the method is actually called.