Automatic Testing With an In-Memory Database and EF7

If you have ever written automatic tests that runs against a real database, you might have noticed that the tests ran quite slow. To get around this problem, we instead target some kind of fake database which is kept in-memory.

To my knowledge, you could have done this in two ways while working with Entity Framework (EF).

One way has been to hide the DbContext behind some kind of abstraction. This makes it possible to replace the DbContext with simple list-objects when running an automatic test. For example the Effort project, or the more simplistic Nethouse UoW Pattern use this approach.

Another way has been to use a SQLite in-memory database, but I have never seen a solution with working code first migrations in EF. At least not until now.

New Capabilities of Entity Framework 7

With the coming arrival of EF7, code first migrations are finally supported with SQLite. Well? As long as you keep within the technical limitations of SQLite that is.

Another nice feature is the brand new InMemory database. Microsoft designed it to be a general purpose in-memory test replacement. Since it is not a relational database, things like migrations or referential integrity constraints will not work.

Target an In-memory Database

It’s relatively straight forward how to configure a DbContext derivate to target either SQLite or InMemory. But the way you configure it in your production code might prevent you do that in your tests. In the case of the .NET framework version of EF, the usual way to configure the DbContext is by overriding a method inside the class.

So how can you tackle the problem of how to configure the DbContext to use a test database? For instance, you can’t simply solve this by setting up a configurable connection string.

The InMemory database is a different sort of provider and has to be activated by code. The SQLite in-memory could actually work with just an updated connection string, in theory that is. But a SQLite in-memory database resets each time the connection to it is closed, and the DbContext open and close the connection a lot.

One could of course do this by adding extra code to the DbContext derivate, where one could inject configuration code to target the test database. But as always, isn’t it a bad practice to keep test code in the production code?

Luckily, in EF7 it’s possible to configure the DbContext when adding Entity Framework to a ServiceCollection instance. This configuration is rather verbose, but can without much effort be reused it in all your tests. This is my approach:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
using System;
using System.Data.Common;
using Microsoft.Data.Entity;
using Microsoft.Data.Sqlite;
using Microsoft.Extensions.DependencyInjection;

namespace EF.TestUtils
{
    public sealed class TestContextFactory : IDisposable
    {
        private IDisposable _connection;
        private IDisposable _scope;

        private int _index;
        private static readonly object Locker = new object();

        public TContext CreateInMemoryDatabase<TContext>() where TContext : DbContext
        {
            var serviceCollection = new ServiceCollection();
            serviceCollection.AddEntityFramework().AddInMemoryDatabase()
                .AddDbContext<TContext>(c => c.UseInMemoryDatabase());
            var serviceProvider = serviceCollection.BuildServiceProvider();
            var scope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope();
            _scope = scope;
            return scope.ServiceProvider.GetService<TContext>();
        }

        public TContext CreateInMemorySqlite<TContext>(bool migrate = true) where TContext : DbContext
        {
            string connectionString = CreateSqliteSharedInMemoryConnectionString();
            DbConnection connection = OpenConnectionToKeepInMemoryDbUntilDispose(connectionString);
            var dbContext = CreateSqliteDbContext<TContext>(connection);
            if (migrate)
                dbContext.Database.Migrate();
            return dbContext;
        }

        private string CreateSqliteSharedInMemoryConnectionString()
        {
            string name = GetUniqueName();
            return $"Data Source={name};Mode=Memory;Cache=Shared";
        }

        private string GetUniqueName()
        {
            lock (Locker)
            {
                return $"testdb{++_index}.db";
            }
        }

        private DbConnection OpenConnectionToKeepInMemoryDbUntilDispose(string connectionString)
        {
            var connection = new SqliteConnection(connectionString);
            connection.Open();
            _connection = connection;
            return connection;
        }

        private TContext CreateSqliteDbContext<TContext>(DbConnection connection) where TContext : DbContext
        {
            var serviceCollection = new ServiceCollection();
            serviceCollection.AddEntityFramework().AddSqlite()
                .AddDbContext<TContext>(c => c.UseSqlite(connection));
            IServiceProvider serviceProvider = serviceCollection.BuildServiceProvider();
            var scope = serviceProvider.GetRequiredService<IServiceScopeFactory>().CreateScope();
            _scope = scope;
            return scope.ServiceProvider.GetService<TContext>();
        }

        public void Dispose()
        {
            _connection?.Dispose();
            _scope?.Dispose();
        }
    }
}

Note that my utility class references types from both the EntityFramework.InMemory and EntityFramework.Sqlite NuGet packages. If you have a problem with that you can split up the class, but I find it rather convenient to have both capabilities in a utils-package that I reuse in all my test projects. And since we are dealing with test-code here, I do not think it matters that much if a NuGet-package to much is brought in.

To make it possible to switch out the DbContext derivative instance when running an automatic test, make sure you inject it in your production code instead of instantiating it there.

Creating an instance of the DbContext with test configuration can be done similar to this.

1
2
3
4
5
6
7
8
9
10
11
using (var factory = new TestContextFactory())
using (var context = factory.CreateInMemorySqlite<MyContext>(migrate: true))
{
    RunScenario(context);
}
// Or...
using (var factory = new TestContextFactory())
using (var context = factory.CreateInMemoryDatabase<MyContext>())
{
    RunScenario(context);
}

Avoid Configuring Two Database Providers During Test Runs

If you use the .NET framework version of EF, and override the OnConfiguring method to configure the DbContext, that method will be called even if you inject the configuratoin with help of the TestContextFactory. Therefore it’s a good practice to prevent cofiguration of multiple database providers in the following way.

1
2
3
4
5
6
7
8
9
10
11
12
internal class MyContext : DbContext
{
    // ...
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured) // This prevents multiple configurations
        {
            optionsBuilder.UseSqlite(@"Data Source=c:\temp\mydatabase.sqlite;");
        }
        // ...
    }
}

Entity Framework Code First Migrations

Note that you do not need to run migrations when using an InMemory database. In fact, it does not even support migrations at all. If you want to test migrations, you have to use a real database such as SQLite.

Migrations are very specific to the type of database that they are written for. This probably makes it a bad idea to test SQL Server migrations on a SQLite database. EF migrations will most likely contain raw scripts when data is affected by the requested change. Remember that the DbContext does not work in a migration? Therefore, you better use the same database technology in your migration tests as you intend to use in your production code.

When you think about what database to choose, in my opinion, you should definitely consider to use SQLite. Not only because its free of charge, but also because you will have a better testing experience with it. Not all apps need the extra horsepower that SQL server brings.

A great thing with EF is that your workflow will be quite similar, whatever database you happen to choose. Now go and test that database logic!