11

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?

Community
  • 1
  • 1
Necoras
  • 6,743
  • 3
  • 24
  • 45

2 Answers2

16

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.

Community
  • 1
  • 1
Joel Harkes
  • 10,975
  • 3
  • 46
  • 65
  • 2
    Hrm. If I'm going to have to go with an external datasource that almost pushes this into the realm of integration testing rather than unit testing. If that's the case, I'll see what can be done with something like SqlLite (http://stackoverflow.com/a/38630232/3691973) rather than a full sql database that would need to be reset for every test run. I'll mark this as the answer for now as there doesn't seem to be a wholly internal way to do this yet. Hopefully this will become easier if/when DataSets are made available. As it is this seems like a significant problem with using Dapper + .net core. – Necoras Apr 21 '17 at 16:30
  • 1
    @Necoras testing sql is basically always an integration test. but yes, sql lite is a good alternative. but know that if you use, sql quite heavenlh you might use features that are not supported in sqlLite or behave differently, and that would not give you a proper view of the production environment. – Joel Harkes Apr 22 '17 at 15:52
  • Yeah, I'm aware that it could function a bit differently from our production system, which is why I was hoping for a more direct in memory representation. Thankfully most of our sql is pretty straightforward. Hopefully there won't be significant divergence issues between SqLite and Sql Server in our use cases. – Necoras Apr 24 '17 at 15:21
  • 1
    Spinning up external resources for a unit test seems over excessive. – Phil Peace Jun 29 '17 at 08:22
  • 1
    @PhilPeace true but is testing sql queries in your code unit tests as well? since these sql queries are not parsed in code but actually only parsed by a external sql server/resource? – Joel Harkes Jun 29 '17 at 09:26
  • is this still valid after core 2.0 as of today? – dragonfly02 Nov 02 '17 at 22:37
  • Yea only way to test query logic in c# is if you use (EF) lambda functions. They provided in code testing support – Joel Harkes Nov 03 '17 at 06:00
1

Another option to test you database access code (queries etc.) is use a local SQL database instance but instead recreate it every time you can start a database transaction as part of your unit-test setup and rollback the transaction in tear down. Depending on the isolation level you have chosen this also addresses concurrency issues when tests / fixtures are executed in parallel.

Marc
  • 4,715
  • 3
  • 27
  • 34