Database Integration Testing with Entity Framework Code First in .NET Framework

Photo by rawpixel.com on Unsplash

You should write integration tests. Period.

Recently it has come to my attention that integration tests are not as common as I thought. I remember vividly one time I found out that a fellow developer friend of mine avoids writing integration tests because of “how expensive they are”. This is just one of the numerous misconceptions surrounding integration tests. This came as a shock to me because I know the projects he works on have thousands of unit test, that don’t actually prevent very naïve, fundamental and game-breaking bugs from slipping into production.

Juval Löwy, in his talk The Zen of Software Architecture in DEVIntersection 2017 Orlando, FL said that, and I’m paraphrasing, “unit tests are borderline useless”, while advocating for the importance of end-to-end and integration testing. He is a pretty radical guy in person (usually not on record, for what I’ve seen), so take that quote with a grain of salt, but when you have thousands of unit test in a project, you successfully ship into production, and your home screen is totally broken for some reason… you can see that he has a point.

Developers usually avoid writing automated tests altogether because of their perceived difficulty. And among those who embrace them, integration tests are ignored for a variety of other reasons when in reality, with the right tools and environment, they can be the easiest to write, and the most rewarding.

This is not really an article about integration tests in general or why you should be writing them, there’s already plenty on that out there, but I just couldn’t resist. For more on that, check out Kent C. Dodds’ article “Write tests. Not too many. Mostly integration.” or the opening arguments of “Writing Tests for Entity Framework” .

The other day I went browsing on the Web to see how other people approach database integration testing with Entity Framework. I stumbled upon several sites, articles, examples and even courses (that I did watch, of course), and I was actually surprised to see there was no consolidated consensus on how to go about it. More shockingly, all of them differed on how I have done it myself. Granted, it is true that there’s no silver-bullet solution in software development, but with common and popular problems there is usually always a preferred route that the community tends to agree upon, and sometimes it is even pushed by some company titans or vendors., no matter how simple the problem domain might be.

That’s what we see happening with technologies like React, which is Facebook’s way of saying “this is how we think you should build UIs on the web”, or to not go too far, Microsoft themselves with Code First in Entity Framework, saying “this is how you should be developing database-centric applications with our technology”. Given how Microsoft is usually so invested in developer productivity, I found it odd that I couldn’t find an official resource that guided you through database integration testing with their technologies.

That said, here I will outline the exact approach, not generalized, that I’ve used with this specific set of technologies. First of all, the following are the basic conditions we should meet:

1. All tests must run against a testing database, not accessed for anything else other than the tests themselves. Not a development database, not a QA database, not a production database.
2. All tests must run always under the same repeatable conditions.
3. All tests must be completely independent and not interfere with each other.
4. The order in which the test run must not affect their outcome.

People have come up with different ways of achieve these conditions. Here’s how I have done it. Keep in mind that this is only achievable with these exact steps with a database that has been built from the ground up using Code First Migrations, since we are going to use that mechanism to set-up our database for testing. You will also notice in the code snippets that I am using NUnit as the testing framework, but I’m sure the same is possible using any other respectable framework by doing just a few minor API changes.

• Before each test suite run, I put the database in a known state. The simplest way is to drop, recreate the database and seed it with necessary data. There is already a database initializer for the drop and recreation part in E.F., but it is lacking since it will fail if there’s an open connection to the database when it is executed, which may cause you trouble in a testing environment. That’s why I’ve extended it with my own initializer that addresses this issue, `DropCreateDatabaseAlwaysAndSeed.cs` and also seeds it right after creation. If you don’t like to have this last part be in the database initializer for some reason, you can just call the seeding logic separately outside.

At this point is probably necessary to point out that the connection string of the database in the testing project should point to a database with a different name than any of your other environments. Some variation of the suffix “Test” should be fine.

• I use a `SetUpFixture` (from NUnit) to not only initialize the database, but also to create a static instance of the `DbContext` and any other dependency that my test fixtures will need, so that they all can share it in a single run. This is necessary because the alternative would have each test recreate dependency instances in their own scope which can result in significantly and unnecessarily slower runs. Don’t rule that out entirely though, because it is possible that this may be exactly what you need, but strive towards this being a last resort.

Keep in mind that because you are sharing the database context among tests, you must explicitly set the Multiple Active Result Sets to true in the connection string, so that the context can execute multiple batches on a single connection, or otherwise an exception will be thrown.

You might see other approaches using a similar class to also include code to delete the database after the tests are finished. I’m not doing this here because I find it useful during development to be able to inspect the data left in the database if I need to, and also there’s basically no price to pay for it because the database will be overwritten anyway in the next test run and there will be no residue.

• Since I am going to be sharing the DbContext among the tests, E.F.’s Change Tracker might bite me for this. For some test in particular the `DbContext` might already be tracking an entity that I need untracked, because of some previously executed tests. To avoid this, I create a base class that all your test fixture classes will extend, and include a `TearDown` method in it that detaches all entities, see `BaseIntegrationFixture.cs`. I also used this base class to define read-only properties that will access the static dependencies that I initialized in the SetUpFixture, just for convenience. This base class can also be used to wrap every test in a transaction scope, but if you test with unique data, you might not ever need this, which is why you don’t see it in my code snippet.
• Now just enjoy. Create test fixture classes that extend your base class, put test methods in it, and access the `DbContext`, and any business logic service or repository that you need to test your application’s database flow.

As you can see, there’s really nothing that much complex about this, but you would be surprised on how creative people can be when they don’t know how to properly use the tools at their disposal. A privilege that we had here though, is that we could recreate and seed the database from migrations, which is not always the case. Many legacy applications use a database-first workflow that would prevent them from setting up the test runs using the Code First API like we did in the first steps. There’s no need to panic though, it can still be easily done with just a little bit more of custom code. In a future post I’ll explore this scenario, just for completeness sake.