I've written before about how unit testing Entity Framework is a contentious and sometimes pointless activity. The TL;DR is that LINQ-to-Objects != Linq-to-Entities and so if you want some useful tests around your data tier then integration tests that actually hit a database are what you want.
However hitting an actual database is has serious implications. For a start you need a database server and you need a database. But the real issue lies around cleanup. When you write a test that amends data in the database you need the test to clean up after itself. If it doesn't then the next test that runs may trip over the amended data and that's your test pack instantly useless.
What you want is a way to wipe the slate clean - to return the database back to the state that it was in before your test ran. Kind of like a database restore - except that would be slow. And this is where SQL Server's snapshot backups have got your back. To quote MSDN:
*Snapshot backups have the following primary benefits:
- A backup can be created quickly, typically measured in seconds, with little or no effect on the server.
- A restore operation can be accomplished from a disk backup just as quickly.
- Backup to tape can be accomplished by another host without an effect on the production system.
- A copy of a production database can be created instantly for reporting or testing.
Just the ticket.
In this post I want to go through the process of taking an existing database, pointing Entity Framework at it, setting up some repositories and then creating an integration test pack that uses snapshot backups to cleanup after each test runs. The code detailed in this post is available in this GitHub repo if you want to have a go yourself.
You can find a whole assortment of databases here. I'm going to use AdventureWorksLT as it's small and simple. So I'll download this and unzip it. I'll drop
AdventureWorksLT2008R2_log.LDF in my data folder and attach AdventureWorksLT2008R2 to my database server. And now I have a database:
Or in English: we want to point Entity Framework at our new shiny database. So let's fire up Visual Studio (I'm using 2013) and create a new solution called "AdventureWorks".
To our solution let's add a new class library project called "AdventureWorks.EntityFramework". And to that we'll add an ADO.NET Entity Data Model which we'll call "AdventureWorks.edmx". When the wizard fires up we'll use the "Generate from database" option, click Next and select "New Connection". In the dialog we'll select our newly attached AdventureWorksLT2008R2 database. We'll leave the "save entity connection settings in App.Config" option selected and click Next. I'm going to use Entity Framework 6.0 - though I think that any version would do. I'm going to pull in all tables / store procs and views. And now Entity Framework is pointing at my database:
In the name of testability let's create a new project to house repositories called "AdventureWorks.Repositories". I'm going to use K. Scott Allen's fine article on MSDN to create a very basic set of repositories wrapped in a unit of work.
In my new project I'll add a reference to the
AdventureWorks.EntityFramework project and create a new
IRepository interface that looks like this:
And a new
IUnitOfWork interface that looks like this:
Now for the implementation of
IRepository. For this we'll need a reference to Entity Framework in our project. Then we'll create a class called
And we also need the implementation of
IUnitOfWork. So we'll create a class called
Let's create a new Unit Test project called "AdventureWorks.Repositories.IntegrationTests". (And just to be clear: this is *not* a unit test project - it is an integration test project.) We'll add a reference back to our
AdventureWorks.Repositories project for the repositories and one back to
AdventureWorks.EntityFramework for our domain models. And finally you'll need a reference to Entity Framework in your IntegrationTest project as well as well.
We'll copy across the
AdventureWorks.Repositories.IntegrationTests as it contains the database connection details. It'll look something like this:
Now we're ready for a test. We'll add ourselves a class called
This is as simple as it gets - our test creates a new unit of work and queries the
BuildVersions table to see what we can see. All it's really doing is demonstrating that we can now hit our database through our repositories. As a side note, we could have the exact same test operating directly on the
DbContext like this:
For the most part we won't be doing this but I wanted to be clear that full power of Entity Framework is available to you as you're putting together your integration tests.
Up until this point we've essentially been laying our infrastructure and doing our plumbing. We now have a database, domain models and data access courtesy of Entity Framework, a testable repository layer and finally an integration test pack. What we want now is to get our database snapshot / backup and restore mechanism set up and integrated into the test pack.
Let's add references to the
System.Configuration assemblies to our integration testing project and then add a new class called
DatabaseSnapshot class exposes 4 methods:
- This method creates 3 stored procedures on the master database:
SnapshotBackup_Delete. These procs do pretty much what their names suggest and the other 3 methods call these stored procedures when creating, restoring and deleting snapshot backups respectively. You can see the (fairly minimal) SQL for these stored procs at the top of the
- This method creates a snapshot backup of the database at this point in time.
- This method restores the database back to state it was in when the snapshot backup was created.
- This method attempts to delete the existing snapshot backup.
In order that we can use the
DatabaseSnapshot class we need to add the following entries to our
These settings allow have the following purposes:
- A connection string that allows
DatabaseSnapshotto connect to the database.
- The logical name of the database you want to backup. (This can be found on the Files tab of the Database Properties in SSMS)
- The location where the snapshot backup is to be stored. You need to make sure that this exists on your machine.
- The name of the snapshot backup that will be created.
Now to make use of
DatabaseSnapshot. Let's add a new class called
At the start of the test run this will create a snapshot in case one doesn't exist already. And at the end of the test run it will be a good citizen and delete the snapshot. We'll also add an extra method to our
This will ensure that after each test runs the database will be restored back to the snapshot created in
SetUpTearDown. Now if you re-run your tests, in between each test the restore back to the snapshot is taking place.
Of course the tests we have in place at present don't actually change the data at all. So I could be lying. I'm not. Let's prove it by adding one more class called
The above test checks that you can look up an existing customer, Mr Jay Adams, and change his name to my name - to John Reilly. If I execute the test above and there was no restore in place then subsequently when I came to exercise this test it should start to fail as it no longer has a Mr Jay Adams to lookup. But with this restore mechanism in place I can execute this test repeatedly without worrying.
And that's us finished - we now have a database snapshot restore mechanism in place. With this we can develop integration tests that thoroughly change the data in our database secure in the knowledge that once the test is complete our database will be restored back to it's initial state.
Obviously there are other alternative approaches for integration testing available to that which I've laid out in this post. But I can imagine that this approach is very useful for applying to legacy applications that you might inherit and need to continue supporting. Also, this approach should fit in well with a continuous integration setup. It would be pretty straightforward to have database that existed purely for testing purposes against which all the integration tests could be set to run at the point of each check in.
Thanks to Marc Talary, Sandeep Deo and Tishul Vadher who all contributed to
DatabaseSnapshot. Credit is also due to Google due to the hundreds of articles the team ended up reading on snapshot backups.