Skip to main content

4 posts tagged with "integration testing"

View All Tags

Autofac 6, integration tests and .NET generic hosting

I blogged a little while ago around to support integration tests using Autofac. This was specific to Autofac but documented a workaround for a long standing issue with ConfigureTestContainer that was introduced into .NET core 3.0 which affects all third-party containers that use ConfigureTestContainer in their tests.

I'll not repeat the contents of the previous post - it all still stands. However, with Autofac 6 the approach documented there will cease to work. This is because the previous approach relied upon ContainerBuilder not being sealed. As of Autofac 6 it is.

Happily the tremendous Alistair Evans came up with an alternative approach which is listed below:

/// <summary>
/// Based upon https://github.com/dotnet/AspNetCore.Docs/tree/master/aspnetcore/test/integration-tests/samples/3.x/IntegrationTestsSample
/// </summary>
/// <typeparam name="TStartup"></typeparam>
public class AutofacWebApplicationFactory<TStartup> : WebApplicationFactory<TStartup> where TStartup : class
{
protected override IHost CreateHost(IHostBuilder builder)
{
builder.UseServiceProviderFactory<ContainerBuilder>(new CustomServiceProviderFactory());
return base.CreateHost(builder);
}
}
/// <summary>
/// Based upon https://github.com/dotnet/aspnetcore/issues/14907#issuecomment-620750841 - only necessary because of an issue in ASP.NET Core
/// </summary>
public class CustomServiceProviderFactory : IServiceProviderFactory<ContainerBuilder>
{
private AutofacServiceProviderFactory _wrapped;
private IServiceCollection _services;
public CustomServiceProviderFactory()
{
_wrapped = new AutofacServiceProviderFactory();
}
public ContainerBuilder CreateBuilder(IServiceCollection services)
{
// Store the services for later.
_services = services;
return _wrapped.CreateBuilder(services);
}
public IServiceProvider CreateServiceProvider(ContainerBuilder containerBuilder)
{
var sp = _services.BuildServiceProvider();
#pragma warning disable CS0612 // Type or member is obsolete
var filters = sp.GetRequiredService<IEnumerable<IStartupConfigureContainerFilter<ContainerBuilder>>>();
#pragma warning restore CS0612 // Type or member is obsolete
foreach (var filter in filters)
{
filter.ConfigureContainer(b => { })(containerBuilder);
}
return _wrapped.CreateServiceProvider(containerBuilder);
}
}

Using this in place of the previous approach should allow you continue running your integration tests with Autofac 6. Thanks Alistair!

Concern for third-party containers#

Whilst this gets us back up and running, Alistair pointed out that this approach depends upon a deprecated interface. This is the IStartupConfigureContainerFilter which has been marked as Obsolete since mid 2019. What this means is, at some point, this approach will stop working.

The marvellous David Fowler has said that ConfigureTestContainer issue should be resolved in .NET. However it's worth noting that this has been an issue since .NET Core 3 shipped and unfortunately the wonderful Chris Ross has advised that it's not likely to be fixed for .NET 5.

I'm very keen this does get resolved in .NET. Building tests upon an Obsolete attribute doesn't fill me with confidence. I'm a long time user of Autofac and I'd like to continue to be. Here's hoping that's made possible by a fix landing in .NET. If this is something you care about, it may be worth upvoting / commenting on the issue in GitHub so the team are aware of desire around this being resolved.

Autofac, WebApplicationFactory and integration tests

Updated 2nd Oct 2020: for an approach that works with Autofac 6 see this post.


This is one of those occasions where I'm not writing up my own work so much as my discovery after in depth googling.

Integration tests with ASP.NET Core are the best. They spin up an in memory version of your application and let you fire requests at it. They've gone through a number of iterations since ASP.NET Core has been around. You may also be familiar with the TestServer approach of earlier versions. For some time, the advised approach has been using <a href="https://docs.microsoft.com/en-us/aspnet/core/test/integration-tests?view=aspnetcore-3.1#basic-tests-with-the-default-webapplicationfactory">WebApplicationFactory</a>.

What makes this approach particularly useful / powerful is that you can swap out dependencies of your running app with fakes / stubs etc. Just like unit tests! But potentially more useful because they run your whole app and hence give you a greater degree of confidence. What does this mean? Well, imagine you changed a piece of middleware in your application; this could potentially break functionality. Unit tests would probably not reveal this. Integration tests would.

There is a fly in the ointment. A hair in the gazpacho. ASP.NET Core ships with dependency injection in the box. It has its own Inversion of Control container which is perfectly fine. However, many people are accustomed to using other IOC containers such as Autofac.

What's the problem? Well, swapping out dependencies registered using ASP.NET Core's IOC requires using a hook called ConfigureTestServices. There's an equivalent hook for swapping out services registered using a custom IOC container: ConfigureTestContainer. Unfortunately, there is a bug in ASP.NET Core as of version 3.0: When using GenericHost, in tests ConfigureTestContainer is not executed

This means you cannot swap out dependencies that have been registered with Autofac and the like. According to the tremendous David Fowler of the ASP.NET team, this will hopefully be resolved.

In the meantime, there's a workaround thanks to various commenters on the thread. Instead of using WebApplicationFactory directly, subclass it and create a custom AutofacWebApplicationFactory (the name is not important). This custom class overrides the behavior of ConfigureServices and CreateHost with a CustomServiceProviderFactory:

namespace My.Web.Tests.Helpers {
/// <summary>
/// Based upon https://github.com/dotnet/AspNetCore.Docs/tree/master/aspnetcore/test/integration-tests/samples/3.x/IntegrationTestsSample
/// </summary>
/// <typeparam name="TStartup"></typeparam>
public class AutofacWebApplicationFactory<TStartup> : WebApplicationFactory<TStartup> where TStartup : class {
protected override void ConfigureWebHost(IWebHostBuilder builder) {
builder.ConfigureServices(services => {
services.AddSingleton<IAuthorizationHandler>(new PassThroughPermissionedRolesHandler());
})
.ConfigureTestServices(services => {
}).ConfigureTestContainer<Autofac.ContainerBuilder>(builder => {
// called after Startup.ConfigureContainer
});
}
protected override IHost CreateHost(IHostBuilder builder) {
builder.UseServiceProviderFactory(new CustomServiceProviderFactory());
return base.CreateHost(builder);
}
}
/// <summary>
/// Based upon https://github.com/dotnet/aspnetcore/issues/14907#issuecomment-620750841 - only necessary because of an issue in ASP.NET Core
/// </summary>
public class CustomServiceProviderFactory : IServiceProviderFactory<CustomContainerBuilder> {
public CustomContainerBuilder CreateBuilder(IServiceCollection services) => new CustomContainerBuilder(services);
public IServiceProvider CreateServiceProvider(CustomContainerBuilder containerBuilder) =>
new AutofacServiceProvider(containerBuilder.CustomBuild());
}
public class CustomContainerBuilder : Autofac.ContainerBuilder {
private readonly IServiceCollection services;
public CustomContainerBuilder(IServiceCollection services) {
this.services = services;
this.Populate(services);
}
public Autofac.IContainer CustomBuild() {
var sp = this.services.BuildServiceProvider();
#pragma warning disable CS0612 // Type or member is obsolete
var filters = sp.GetRequiredService<IEnumerable<IStartupConfigureContainerFilter<Autofac.ContainerBuilder>>>();
#pragma warning restore CS0612 // Type or member is obsolete
foreach (var filter in filters) {
filter.ConfigureContainer(b => { }) (this);
}
return this.Build();
}
}
}

I'm going to level with you; I don't understand all of this code. I'm not au fait with the inner workings of ASP.NET Core or Autofac but I can tell you what this allows. With this custom WebApplicationFactory in play you get ConfigureTestContainer back in the mix! You get to write code like this:

using System;
using System.Net;
using System.Net.Http.Headers;
using System.Threading.Tasks;
using FakeItEasy;
using FluentAssertions;
using Microsoft.AspNetCore.TestHost;
using Microsoft.Extensions.DependencyInjection;
using Xunit;
using Microsoft.Extensions.Options;
using Autofac;
using System.Net.Http;
using Newtonsoft.Json;
namespace My.Web.Tests.Controllers
{
public class MyControllerTests : IClassFixture<AutofacWebApplicationFactory<My.Web.Startup>> {
private readonly AutofacWebApplicationFactory<My.Web.Startup> _factory;
public MyControllerTests(
AutofacWebApplicationFactory<My.Web.Startup> factory
) {
_factory = factory;
}
[Fact]
public async Task My() {
var fakeSomethingService = A.Fake<IMySomethingService>();
var fakeConfig = Options.Create(new MyConfiguration {
SomeConfig = "Important thing",
OtherConfigMaybeAnEmailAddress = "[email protected]"
});
A.CallTo(() => fakeSomethingService.DoSomething(A<string>.Ignored))
.Returns(Task.FromResult(true));
void ConfigureTestServices(IServiceCollection services) {
services.AddSingleton(fakeConfig);
}
void ConfigureTestContainer(ContainerBuilder builder) {
builder.RegisterInstance(fakeSomethingService);
}
var client = _factory
.WithWebHostBuilder(builder => {
builder.ConfigureTestServices(ConfigureTestServices);
builder.ConfigureTestContainer<Autofac.ContainerBuilder>(ConfigureTestContainer);
})
.CreateClient();
// Act
var request = StringContent("{\"sommat\":\"to see\"}");
request.Headers.ContentType = MediaTypeHeaderValue.Parse("application/json");
var response = await client.PostAsync("/something/submit", request);
// Assert
response.StatusCode.Should().Be(HttpStatusCode.OK);
A.CallTo(() => fakeSomethingService.DoSomething(A<string>.Ignored))
.MustHaveHappened();
}
}
}

Integration Tests with SQL Server Database Snapshots

Once More With Feeling#

This is a topic that I have written about before.... But not well. I recently had cause to dust down my notes on how to use snapshotting in your integration tests. To my dismay, referring back to my original blog post was less helpful than I'd hoped. Now I've cracked the enigma code that my original scribings turned out to be, it's time to turn my relearnings back into something genuinely useful.

What's the Scenario?#

You have a test database. You want to write integration tests. So what's the problem? Well, these tests will add records, delete records, update records within the tables of the database. They will mutate the data. And that's exactly what they ought to do; they're testing that our code uses the database in the way we would hope and expect.

So how do we handle this? Well, we could handle this by writing code at the end of each test that is responsible for reverting the database back to the state that it was in at the start of the test. So if we had a test that added a record and tested it, we'd need the test to be responsible for removing that record before any subsequent tests run. Now that's a totally legitimate approach but it adds tax. Each test becomes more complicated and requires more code.

So what's another approach? Perhaps we could take a backup of our database before our first test runs. Then, at the end of each test, we could restore our backup to roll the database back to its initial state. Perfect, right? Less code to write, less scope for errors. So what's the downside? Backups are slowwwww. Restores likewise. We could be waiting minutes between each test that runs. That's not acceptable.

There is another way though: database snapshots - a feature that's been nestling inside SQL Server for a goodly number of years. For our use case, to all intents and purposes, database snapshots offers the same functionality as backups and restores. You can backup a database (take a snapshot of a database at a point in time), you can restore a database (roll back the database to the point of the snapshot). More importantly, you can do either operation in *under a second*. As it happens, Microsoft advocate using this approach themselves:

In a testing environment, it can be useful when repeatedly running a test protocol for the database to contain identical data at the start of each round of testing. Before running the first round, an application developer or tester can create a database snapshot on the test database. After each test run, the database can be quickly returned to its prior state by reverting the database snapshot.

Sold!

Talk is cheap, show me the code#

In the end it comes down to 3 classes; DatabaseSnapshot.cs which does the actual snapshotting work and 2 classes that make use of it.

DatabaseSnapshot.cs#

This is our DatabaseSnapshot class. Isn't it pretty?

using System.Data;
using System.Data.SqlClient;
namespace Testing.Shared
{
public class DatabaseSnapshot
{
private readonly string _dbName;
private readonly string _dbSnapShotPath;
private readonly string _dbSnapShotName;
private readonly string _dbConnectionString;
public DatabaseSnapshot(string dbName, string dbSnapshotPath, string dbSnapshotName, string dbConnectionString)
{
_dbName = dbName;
_dbSnapshotPath = dbSnapshotPath;
_dbSnapshotName = dbSnapshotName;
_dbConnectionString = dbConnectionString;
}
public void CreateSnapshot()
{
if (!System.IO.Directory.Exists(_dbSnapshotPath))
System.IO.Directory.CreateDirectory(_dbSnapshotPath);
var sql = $"CREATE DATABASE { _dbSnapshotName } ON (NAME=[{ _dbName }], FILENAME='{ _dbSnapshotPath }{ _dbSnapshotName }') AS SNAPSHOT OF [{_dbName }]";
ExecuteSqlAgainstMaster(sql);
}
public void DeleteSnapshot()
{
var sql = $"DROP DATABASE { _dbSnapshotName }";
ExecuteSqlAgainstMaster(sql);
}
public void RestoreSnapshot()
{
var sql = "USE master;\r\n" +
$"ALTER DATABASE {_dbName} SET SINGLE_USER WITH ROLLBACK IMMEDIATE;\r\n" +
$"RESTORE DATABASE {_dbName}\r\n" +
$"FROM DATABASE_SNAPSHOT = '{ _dbSnapshotName }';\r\n" +
$"ALTER DATABASE {_dbName} SET MULTI_USER;\r\n";
ExecuteSqlAgainstMaster(sql);
}
private void ExecuteSqlAgainstMaster(string sql, params SqlParameter[] parameters)
{
using (var conn = new SqlConnection(_dbConnectionString))
{
conn.Open();
var cmd = new SqlCommand(sql, conn) { CommandType = CommandType.Text };
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}

It exposes 3 methods:

CreateSnapshot
This method creates the snapshot of the database. We will run this right at the start, before any of our tests run.
DeleteSnapshot
Deletes the snapshot we created. We will run this at the end, after all our tests have finished running.
RestoreSnapshot
Restores the database back to the snapshot we took earlier. We run this after each test has completed. This method relies on a connection to the database (perhaps unsurprisingly). It switches the database in use away from the database that is being restored prior to actually running the restore. It happens to shift to the master database (I believe that's entirely incidental; although I haven't tested).

SetupAndTeardown.cs#

This class is responsible for setting up the snapshot we're going to use in our tests right before any of the tests have run (in the FixtureSetup method). It's also responsible for deleting the snapshot once all the tests have finished running (in the FixtureTearDown method). It should be noted that in this example I'm using NUnit and this class is written to depend on the hooks NUnit exposes for running code at the very beginning and end of the test cycle. All test frameworks have these hooks; if you're using something other than NUnit then it's just a case of swapping in the relevant attribute (everything tends to attribute driven in the test framework world).

using NUnit.Framework;
namespace Testing.Shared
{
[SetUpFixture]
public class SetupAndTeardown
{
public static DatabaseSnapshot DatabaseSnapshot;
[SetUp]
public void FixtureSetup()
{
DatabaseSnapshot = new DatabaseSnapshot("MyDbName", "C:\\", "MySnapshot", "Data Source=.;initial catalog=MyDbName;integrated security=True;");
try
{
// Try to delete the snapshot in case it was left over from aborted test runs
DatabaseSnapshot.DeleteSnapShot();
}
catch { /* this should fail with snapshot does not exist */ }
DatabaseSnapshot.CreateSnapShot();
}
[TearDown]
public void FixtureTearDown()
{
DatabaseSnapshot.DeleteSnapShot();
}
}
}

TestBase.cs#

All of our test classes are made to inherit from this class:

using NUnit.Framework;
namespace Testing.Shared
{
public class TestBase
{
[TearDown]
public void TearDown()
{
SetupAndTeardown.DatabaseSnapshot.RestoreSnapShot();
}
}
}

Which restores the database back to the snapshot position at the end of each test. And that... Is that!

Integration Testing with Entity Framework and Snapshot Backups

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.

Our Mission#

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.

We need a database#

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_Data.mdf and AdventureWorksLT2008R2_log.LDF in my data folder and attach AdventureWorksLT2008R2 to my database server. And now I have a database:

Assemble me your finest DbContext#

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:

Let There be Repositories!#

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:

using System;
using System.Linq;
using System.Linq.Expressions;
namespace AdventureWorks.Repositories
{
public interface IRepository<T> where T : class
{
IQueryable<T> FindAll();
IQueryable<T> FindWhere(Expression<Func<T, bool>> predicate);
T Add(T newEntity);
T Remove(T entity);
}
}

And a new IUnitOfWork interface that looks like this:

using AdventureWorks.EntityFramework;
namespace AdventureWorks.Repositories
{
public interface IUnitOfWork
{
public IRepository<ErrorLog> ErrorLogs { get; }
public IRepository<Address> Addresses { get; }
public IRepository<Customer> Customers { get; }
public IRepository<CustomerAddress> CustomerAddresses { get; }
public IRepository<Product> Products { get; }
public IRepository<ProductCategory> ProductCategories { get; }
public IRepository<ProductDescription> ProductDescriptions { get; }
public IRepository<ProductModel> ProductModels { get; }
public IRepository<ProductModelProductDescription> ProductModelProductDescriptions { get; }
public IRepository<SalesOrderDetail> SalesOrderDetails { get; }
public IRepository<SalesOrderHeader> SalesOrderHeaders { get; }
public IRepository<BuildVersion> BuildVersions { get; }
void Commit();
}
}

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 SqlRepository:

using System;
using System.Data.Entity;
using System.Linq;
using System.Linq.Expressions;
namespace AdventureWorks.Repositories
{
public class SqlRepository<T> : IRepository<T> where T : class
{
public SqlRepository(DbContext context)
{
_dbSet = context.Set<T>();
}
public IQueryable<T> FindAll()
{
return _dbSet;
}
public IQueryable<T> FindWhere(Expression<Func<T, bool>> predicate)
{
return _dbSet.Where(predicate);
}
public T Add(T newEntity)
{
return _dbSet.Add(newEntity);
}
public T Remove(T entity)
{
return _dbSet.Remove(entity);
}
protected DbSet<T> _dbSet;
}
}

And we also need the implementation of IUnitOfWork. So we'll create a class called SqlUnitOfWork:

using System;
using System.Linq;
using System.Data.Entity;
using AdventureWorks.EntityFramework;
namespace AdventureWorks.Repositories
{
public class SqlUnitOfWork : IUnitOfWork
{
public SqlUnitOfWork()
{
_context = new AdventureWorksLT2008R2Entities();
}
public IRepository<ErrorLog> ErrorLogs
{
get
{
if (_errorLogs == null) _errorLogs = new SqlRepository<ErrorLog>(_context);
return _errorLogs;
}
}
public IRepository<Address> Addresses
{
get
{
if (_addresses == null) _addresses = new SqlRepository<Address>(_context);
return _addresses;
}
}
public IRepository<Customer> Customers
{
get
{
if (_customers == null) _customers = new SqlRepository<Customer>(_context);
return _customers;
}
}
public IRepository<CustomerAddress> CustomerAddresses
{
get
{
if (_customerAddresses == null) _customerAddresses = new SqlRepository<CustomerAddress>(_context);
return _customerAddresses;
}
}
public IRepository<Product> Products
{
get
{
if (_products == null) _products = new SqlRepository<Product>(_context);
return _products;
}
}
public IRepository<ProductCategory> ProductCategories
{
get
{
if (_productCategories == null) _productCategories = new SqlRepository<ProductCategory>(_context);
return _productCategories;
}
}
public IRepository<ProductDescription> ProductDescriptions
{
get
{
if (_productDescriptions == null) _productDescriptions = new SqlRepository<ProductDescription>(_context);
return _productDescriptions;
}
}
public IRepository<ProductModel> ProductModels
{
get
{
if (_productModels == null) _productModels = new SqlRepository<ProductModel>(_context);
return _productModels;
}
}
public IRepository<ProductModelProductDescription> ProductModelProductDescriptions
{
get
{
if (_productModelProductDescriptions == null) _productModelProductDescriptions = new SqlRepository<ProductModelProductDescription>(_context);
return _productModelProductDescriptions;
}
}
public IRepository<SalesOrderDetail> SalesOrderDetails
{
get
{
if (_salesOrderDetails == null) _salesOrderDetails = new SqlRepository<SalesOrderDetail>(_context);
return _salesOrderDetails;
}
}
public IRepository<SalesOrderHeader> SalesOrderHeaders
{
get
{
if (_salesOrderHeaders == null) _salesOrderHeaders = new SqlRepository<SalesOrderHeader>(_context);
return _salesOrderHeaders;
}
}
public IRepository<BuildVersion> BuildVersions
{
get
{
if (_buildVersions == null) _buildVersions = new SqlRepository<BuildVersion>(_context);
return _buildVersions;
}
}
public void Commit()
{
_context.SaveChanges();
}
SqlRepository<ErrorLog> _errorLogs = null;
SqlRepository<Address> _addresses = null;
SqlRepository<Customer> _customers = null;
SqlRepository<CustomerAddress> _customerAddresses = null;
SqlRepository<Product> _products = null;
SqlRepository<ProductCategory> _productCategories = null;
SqlRepository<ProductDescription> _productDescriptions = null;
SqlRepository<ProductModel> _productModels = null;
SqlRepository<ProductModelProductDescription> _productModelProductDescriptions = null;
SqlRepository<SalesOrderDetail> _salesOrderDetails = null;
SqlRepository<SalesOrderHeader> _salesOrderHeaders = null;
SqlRepository<BuildVersion> _buildVersions = null;
readonly DbContext _context;
}
}

And Now Let's Start Integration Testing!#

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 app.config from AdventureWorks.EntityFramework to AdventureWorks.Repositories.IntegrationTests as it contains the database connection details. It'll look something like this:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
<!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
</configSections>
<connectionStrings>
<add name="AdventureWorksLT2008R2Entities"
connectionString="metadata=res://*/AdventureWorks.csdl|res://*/AdventureWorks.ssdl|res://*/AdventureWorks.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=AdventureWorksLT2008R2;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;"
providerName="System.Data.EntityClient" />
</connectionStrings>
<entityFramework>
<defaultConnectionFactory type="System.Data.Entity.Infrastructure.SqlConnectionFactory, EntityFramework" />
<providers>
<provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
</providers>
</entityFramework>
</configuration>

Now we're ready for a test. We'll add ourselves a class called BuildVersionTests:

using System;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace AdventureWorks.Repositories.IntegrationTests
{
[TestClass]
public class BuildVersionTests
{
[TestMethod]
public void BuildVersions_should_return_the_correct_version_information()
{
// Arrange
var uow = new SqlUnitOfWork();
// Act
var buildVersions = uow.BuildVersions.FindAll().ToList();
// Assert
Assert.AreEqual(1, buildVersions.Count);
Assert.AreEqual("10.00.80404.00", buildVersions[0].Database_Version);
Assert.AreEqual(new DateTime(2008, 4, 4), buildVersions[0].ModifiedDate);
Assert.AreEqual(1, buildVersions[0].SystemInformationID);
Assert.AreEqual(new DateTime(2008, 4, 4), buildVersions[0].VersionDate);
}
}
}

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:

[TestMethod]
public void DbContext_BuildVersions_should_return_the_correct_version_information()
{
// Arrange
var dbContext = new AdventureWorks.EntityFramework.AdventureWorksLT2008R2Entities();
// Act
var buildVersions = dbContext.BuildVersions.ToList();
// Assert
Assert.AreEqual(1, buildVersions.Count);
Assert.AreEqual("10.00.80404.00", buildVersions[0].Database_Version);
Assert.AreEqual(new DateTime(2008, 4, 4), buildVersions[0].ModifiedDate);
Assert.AreEqual(1, buildVersions[0].SystemInformationID);
Assert.AreEqual(new DateTime(2008, 4, 4), buildVersions[0].VersionDate);
}

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.

Database Snapshotting Time#

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.Data and System.Configuration assemblies to our integration testing project and then add a new class called DatabaseSnapshot:

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace AdventureWorks.Repositories.IntegrationTests
{
public static class DatabaseSnapshot
{
private const string SpCreateSnapShotName = "SnapshotBackup_Create";
private const string SpCreateSnapShot =
@"CREATE PROCEDURE [dbo].[" + SpCreateSnapShotName + @"]
@databaseName varchar(512),
@databaseLogicalName varchar(512),
@snapshotBackupPath varchar(512),
@snapshotBackupName varchar(512)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql varchar(500)
SELECT @sql = 'CREATE DATABASE ' + @snapshotBackupName +
' ON (NAME=[' + @databaseLogicalName +
'], FILENAME=''' + @snapshotBackupPath + @snapshotBackupName +
''') AS SNAPSHOT OF [' + @databaseName + ']'
EXEC(@sql)
END";
private const string SpRestoreSnapShotName = "SnapshotBackup_Restore";
private const string SpRestoreSnapShot =
@"CREATE PROCEDURE [dbo].[" + SpRestoreSnapShotName + @"]
@databaseName varchar(512),
@snapshotBackupName varchar(512)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql varchar(500)
SET @sql = 'ALTER DATABASE [' + @databaseName + '] SET SINGLE_USER WITH ROLLBACK IMMEDIATE'
EXEC (@sql)
RESTORE DATABASE @databaseName
FROM DATABASE_SNAPSHOT = @snapshotBackupName
SET @sql = 'ALTER DATABASE [' + @databaseName + '] SET MULTI_USER'
EXEC (@sql)
END";
private const string SpDeleteSnapShotName = "SnapshotBackup_Delete";
private const string SpDeleteSnapShot =
@"CREATE PROCEDURE [dbo].[" + SpDeleteSnapShotName + @"]
@snapshotBackupName varchar(512)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @sql varchar(500)
SELECT @sql = 'DROP DATABASE ' + @snapshotBackupName
EXEC(@sql)
END";
private static string _masterDbConnectionString;
private static string _dbName;
private static ConnectionStringSettings _dbConnectionStringSettings;
private static ConnectionStringSettings DbConnectionStringSettings
{
get
{
if (_dbConnectionStringSettings == null)
_dbConnectionStringSettings = ConfigurationManager.ConnectionStrings["SnapshotBackup"];
return _dbConnectionStringSettings;
}
}
/// <summary>
/// Stored procedures should be executed against master database
/// </summary>
private static string MasterDbConnectionString
{
get
{
if (string.IsNullOrEmpty(_masterDbConnectionString))
{
var sqlConnection = new SqlConnection(DbConnectionStringSettings.ConnectionString);
_masterDbConnectionString = DbConnectionStringSettings.ConnectionString.Replace(sqlConnection.Database, "master");
}
return _masterDbConnectionString;
}
}
private static string DbName
{
get
{
if (string.IsNullOrEmpty(_dbName))
_dbName = new SqlConnection(DbConnectionStringSettings.ConnectionString).Database.TrimStart('[').TrimEnd(']');
return _dbName;
}
}
public static void SetupStoredProcedures()
{
using (var conn = new SqlConnection(MasterDbConnectionString))
{
conn.Open();
// Drop the existing stored procedures
SqlCommand cmd;
const string dropProcSql = "IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'P', N'PC')) DROP PROCEDURE [dbo].[{0}]";
foreach (var spName in new[] { SpCreateSnapShotName, SpDeleteSnapShotName, SpRestoreSnapShotName })
{
cmd = new SqlCommand(string.Format(dropProcSql, spName), conn);
cmd.ExecuteNonQuery();
}
// Create the stored procedures anew
foreach (var createProcSql in new[] { SpCreateSnapShot, SpDeleteSnapShot, SpRestoreSnapShot })
{
cmd = new SqlCommand(createProcSql, conn);
cmd.ExecuteNonQuery();
}
conn.Close();
}
}
public static void CreateSnapShot()
{
var databaseName = new SqlParameter { ParameterName = "@databaseName", SqlValue = SqlDbType.VarChar, Value = DbName };
var databaseLogicalName = new SqlParameter { ParameterName = "@databaseLogicalName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["DatabaseLogicalName"] };
var snapshotBackupPath = new SqlParameter { ParameterName = "@snapshotBackupPath", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["SnapshotBackupPath"] };
var snapshotBackupName = new SqlParameter { ParameterName = "@snapshotBackupName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["SnapshotBackupName"] };
ExecuteStoredProcAgainstMaster(SpCreateSnapShotName, new[] { databaseName, databaseLogicalName, snapshotBackupPath, snapshotBackupName });
}
public static void DeleteSnapShot()
{
var snapshotBackupName = new SqlParameter { ParameterName = "@snapshotBackupName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["SnapshotBackupName"] };
ExecuteStoredProcAgainstMaster(SpDeleteSnapShotName, new[] { snapshotBackupName });
}
public static void RestoreSnapShot()
{
var databaseName = new SqlParameter { ParameterName = "@databaseName", SqlValue = SqlDbType.VarChar, Value = DbName };
var snapshotBackupName = new SqlParameter { ParameterName = "@snapshotBackupName", SqlValue = SqlDbType.VarChar, Value = ConfigurationManager.AppSettings["SnapshotBackupName"] };
ExecuteStoredProcAgainstMaster(SpRestoreSnapShotName, new[] { databaseName, snapshotBackupName });
}
private static void ExecuteStoredProcAgainstMaster(string storedProc, SqlParameter[] parameters)
{
using (var conn = new SqlConnection(MasterDbConnectionString))
{
conn.Open();
var cmd = new SqlCommand(storedProc, conn) { CommandType = CommandType.StoredProcedure };
cmd.Parameters.AddRange(parameters);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
}

The DatabaseSnapshot class exposes 4 methods:

SetupStoredProcedures
This method creates 3 stored procedures on the master database: SnapshotBackup_Create, SnapshotBackup_Restore and 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 theDatabaseSnapshot class.
CreateSnapShot
This method creates a snapshot backup of the database at this point in time.
RestoreSnapShot
This method restores the database back to state it was in when the snapshot backup was created.
DeleteSnapShot
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 app.config:

<configuration>
<connectionStrings>
<add name="SnapshotBackup"
connectionString="data source=.;initial catalog=AdventureWorksLT2008R2;Trusted_Connection=true;Connection Timeout=200" />
</connectionStrings>
<appSettings>
<add key="DatabaseLogicalName" value="AdventureWorksLT2008_Data" />
<add key="SnapshotBackupPath" value="C:\DbSnapshots\" />
<add key="SnapshotBackupName" value="AdventureWorksLT2008R2_Snapshot" />
</appSettings>
</configuration>

These settings allow have the following purposes:

SnapshotBackup
A connection string that allows DatabaseSnapshot to connect to the database.
DatabaseLogicalName
The logical name of the database you want to backup. (This can be found on the Files tab of the Database Properties in SSMS)
SnapshotBackupPath
The location where the snapshot backup is to be stored. You need to make sure that this exists on your machine.
SnapshotBackupName
The name of the snapshot backup that will be created.

Now to make use of DatabaseSnapshot. Let's add a new class called SetUpTearDown:

using Microsoft.VisualStudio.TestTools.UnitTesting;
namespace AdventureWorks.Repositories.IntegrationTests
{
[TestClass]
public static class SetUpTearDown
{
[AssemblyInitialize]
public static void TestRunInitialize(TestContext context)
{
try
{
// Try to delete the snapshot in case it was left over from aborted test runs
DatabaseSnapshot.DeleteSnapShot();
}
catch { /* this should fail with snapshot does not exist */ }
DatabaseSnapshot.SetupStoredProcedures();
DatabaseSnapshot.CreateSnapShot();
}
[AssemblyCleanup]
public static void TestRunCleanup()
{
DatabaseSnapshot.DeleteSnapShot();
}
}
}

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 BuildVersionTests class:

namespace AdventureWorks.Repositories.IntegrationTests
{
[TestClass]
public class BuildVersionTests
{
// ...
[TestCleanup]
public void TestCleanup()
{
DatabaseSnapshot.RestoreSnapShot();
}
}
}

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.

Prove it!#

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 CustomerTests:

using System;
using System.Linq;
using System.Linq.Expressions;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using AdventureWorks.EntityFramework;
namespace AdventureWorks.Repositories.IntegrationTests
{
[TestClass]
public class CustomerTests
{
[TestMethod]
public void Should_change_a_customers_first_and_last_name()
{
// Arrange
var uow = new SqlUnitOfWork();
// Act
var customer = uow.Customers.FindWhere(x => x.FirstName == "Jay" && x.LastName == "Adams").First();
var customerId = customer.CustomerID;
customer.FirstName = "John";
customer.LastName = "Reilly";
uow.Commit();
// Assert
Assert.IsNotNull(uow.Customers.FindWhere(x => x.FirstName == "John" && x.LastName == "Reilly" && x.CustomerID == customerId).SingleOrDefault());
}
[TestCleanup]
public void TestCleanup()
{
DatabaseSnapshot.RestoreSnapShot();
}
}
}

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.

Rounding off#

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.