Using in-memory databases for unit testing EF Core applications

While writing the book Entity Framework Core in Action I wrote over 600 unit tests, which taught me a lot about unit testing EF Core applications. So, when I wrote the chapter on unit testing, which was the last in the book,  I combined what I had learn into a library called EfCore.TestSupport.

Note: EfCore.TestSupport is an open-source library (MIT licence) with a NuGet package available. Documentation is available in the project’s Wiki.

In this article I want to look at using in-memory database to simulate the database when you are unit testing. I also look at how to properly test your ASP.NET Core database update code, which has to handle what is called the disconnected state (explained later), with an in-memory database.

Your options for in-memory databases

There are several ways to simulate the database when unit testing, but one of the simplest ways is to create an in-memory database. They are easy to create and are quicker to create than a real database, but they do have a few limitations, which I will describe at the end. First I want to show you how they work.

The most obvious in-memory database is EF Core’s InMemory database provider, which was written specifically for unit testing. But it turns out the better option is using the SQLite database provider, with an in-memory database. Why is that?

Its because SQLite is a true relational database while EF Core’s InMemory database provider isn’t a real relational database. This means that EF Core’s InMemory database won’t pick up on data that breaks referential integrity, such as foreign keys that don’t match the appropriate primary key. About the only good thing about EF Core’s InMemory is that its slightly faster on setup than SQLite (about 40%), but both are very fast anyway, so the extra checking makes SQLite my choice.

Note My EfCore.TestSupport library has support for both InMemory and SQLite in-memory, so you can choose. But in this article I am only going to describe the SQLite in-memory.

How to configure an in-memory SQLite database?

My EfCore.TestSupport library provides a pre-configured static method that will set up the options for creating a SQLite database that is in-memory. The code below contains a simplified version of that method

public static DbContextOptions CreateOptions()
    where T : DbContext
{
    //This creates the SQLite connection string to in-memory database
    var connectionStringBuilder = new SqliteConnectionStringBuilder
            { DataSource = ":memory:" };
    var connectionString = connectionStringBuilder.ToString();

    //This creates a SqliteConnectionwith that string
    var connection = new SqliteConnection(connectionString);

    //The connection MUST be opened here
    connection.Open();

    //Now we have the EF Core commands to create SQLite options
    var builder = new DbContextOptionsBuilder<T>();
    builder.UseSqlite(connection);

    return builder.Options;
}

Note: The EfCore.TestSupport version has a few extra checks and options, but I left them out to focus on the in-memory bit.

How to use an in-memory database in a unit test

Having shown you the CreateOptions method now I want to show you how you can use that to create an in-memory version of the database that EF a simple unit test that uses the CreateOptions method to create an in-memory database.

Note: I am using the xUnit unit test package with fluent asserts.

[Fact]
public void TestSQLiteOk()
{
    //SETUP
    //Here create the options using SQLite CreateOptions
    var options = SqliteInMemory         
        .CreateOptions<MyContext>();
    //I create an instance of my DbContext
    using (var context = new MyContext(options))
    {
        //You have to create the database
        context.Database.EnsureCreated();
        context.SeedDatabaseFourBooks(); 

        //ATTEMPT
        context.Books.First().Title = "New Title";
        context.SaveChanges();

        //VERIFY
        context.Books.First().Title.ShouldEqual("New Title");
    }
}

Using the pattern shown in the code just shown you can test all manner of EF Core code. But let me point out some parts of the code

1. Lines 6-7: This is using my EfCore.TestSupport methods to set up the options to use a in-memory SQLite database.
2. Line 9: This is where you create an instance of your application’s DbContext.
3. Line 12: The context.Database.EnsureCreated(), is very important. This creates the in-memory database using the current entity classes and EF Core configuration of your application. In this case it will return an empty database.
4. Line 13: You often need to fill the database (often called seeding the database) with appropriate data for a test. It turns out seeding the database can be quite complex in a big application. I tend to spend time creating helper methods, like the SeedDatabaseFourBooks method in this example, to set up specific database states.
5. Lines 16-17: This is where you put the code you want to test. I showed a very simple example – yours is likely to call methods from you own code.
6. Line 20: Here is the assert to check the method did what you expected it to do.

Using SQLite in-memory database for testing disconnected state updates

If you are using EF Core in an application such as ASP.NET Core, then you will have to test update methods that have to work across what is called the disconnected state. The disconnected state happens when an operation, normally an update, is split into two separate parts.

The figure below shows an example of the disconnected state where a user updates the publication date of a book. The first stage on the left is where the data is presented to the user to change. The second stage happens when the user presses the UPDATE button, which submits a new HTTP POST request to the web application which uses the information sent back to do the the update.

The important point about the disconnected state is you have two, separate instances of the application’s DbContext. And if you don’t test things in the same way you might miss some errors.

Both the InMemeory and SQLite in-memory databases can handle this problem. In SQLite’s case the database is held in the connection, so, as long as you use the same options you can create new instances of the DbContext that maps to the same database.

Let me give you an example of a bug that would only be picked up by having two instances of the application’s DbContext. In this case I want to add a new review to a book. A Book entity has has zero-to-many Review‘s and a user have add a new review, with a star rating – see figure below.

So, the standard way to add a new Review in a disconnected state is to load the Book entity with all its Reviews, add the review to the collection and then call SaveChanges.

Now, if I get that wrong, and I don’t load the current Reviews I can get a number of possible errors, but only if I replicate the disconnected state properly.

Let me show you the correct way to test this disconnected state, which shows the error.

[Fact]
public void CorrectTestOfDisconnectedState()
{
    //SETUP
    var options = SqliteInMemory
        .CreateOptions<MyContext>();

    //In my first DbContext I make seed the database
    int bookId;
    using (var context = new MyContext(options))
    {
        context.Database.EnsureCreated();
        context.SeedDatabaseFourBooks();
        bookId = context.Books.Last().BookId;
    }

    //In the second DbContext I test my disconnected update code
    using (var context = new MyContext(options))
    {
        //ATTEMPT
        //I load a book BUT FORGET TO LOAD THE REVIEWS COLLECTION
        var book = context.Single(x => x.BookId == bookId);
        book.Reviews.Add( new Review{NumStars = 5});

        //… rest of unit test left out, as has errored
    }
}

Note: the correct code for line 22 would be
        context.Single(x => x.BookId == bookId).Include(x => x.Reviews);

The point of all that is, if I had used one DbContext and seeded the database and ran the test of the Review update code, then it would have passed. That is because EF Core would have already been tracking the Reviews collections as from the SeedDatabaseFourBooks method, so the Reviews collection would already be present.

Note: In some of my unit tests I need to use three instances of the DbContext to correctly test something. One for seeding the database, one for running the test, and a final one to verify that the database has been written correctly.

Limitations of using SQLite to unit test EF Core

It’s likely that the actual database you use isn’t going to be SQLite. In that case you need to understand how SQLite differs from your actual database, especially if you use a feature that SQLite doesn’t support. The table below gives you the major areas that SQLite doesn’t support, or its support is so different that it will cause a problem.

These are all fairly advanced features, so if you don’t recognise them then you aren’t using them, so its fine to use SQLite for unit testing.

Note: There are other differences such as it doesn’t have the same types as other databases, for instance it stores strings in a TEXT type, while SQL Server has 8-bit varchar(nnn) and 16-bit nvarchar(nnn) types. These don’t normally cause a problem, but be aware in case some odd unit test result comes up.

So, if you use any of the features in the table then you can’t use SQLite in-memory database (nor InMemory – it has even more limitations than SQLite). If you can’t use SQLite you have to use the same database type that you are using for the application in your unit tests.

My EfCore.TestSupport library has help for other database types, with extra helpers for SQL Server databases, but that is most likely needs a new article to cover that.

Conclusion

This article shows you how you can simulate your database quickly and easily by using an in-memory database. I have shown you that SQLite is the best choice for an in-memory database, because it is a true relational database and will catch errors such as incorrect primary or foreign keys.

I also showed you how you can produce different instances of the application’s DbContext that all access the same in-memory database. This is useful for checking code that is used in applications such as ASP.NET Core, where you have a disconnected state.

There are some limitations to SQLite’s implementation of the SQL language, which might rule it out in your application. But for any ‘standard’ EF Core application using SQLite in-memory databases should be fine.

I hope this article, and my EfCore.TestSupport library, which is available as a NuGet package, with documentation of all its feature in the project’s Wiki, will help you write unit tests quicker.

Note: The chapter 15 of my book Entity Framework Core in Action is on unit testing EF Core application and goes into the whole topic in much more detail (its 32 pages long!). Manning Publications have offered the coupon to get 37% off the book – use fccefcoresmith (Christmas 2017 – watch out for 50% off offers too).

Happy coding.