Using in-memory databases for unit testing EF Core applications

Last Updated: July 31, 2020 | Created: December 16, 2017

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.

SQL feature SQLite Support?
Different schemas NONE
SQL Sequences NONE
SQL Computed columns Different
SQL user defined functions (UDFs) Different
SQL fragment default value Different

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.

5 2 votes
Article Rating
Subscribe
Notify of
guest
35 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
Aline
Aline
1 year ago

Hi there!
I’m having issues with testing. in parallel. If I run tests in sequence they pass, but if I run them in parallel, they fail. All I’m doing are some tests based on seeded test data. (see below). What can be wrong?

    [TestMethod]
    public async Task MyTestMethod()
    {
      SqliteConnectionStringBuilder connectionStringBuilder = new SqliteConnectionStringBuilder
      {
        DataSource = “:memory:”,
        Cache = SqliteCacheMode.Private
      };
      SqliteConnection connection = new SqliteConnection(connectionStringBuilder.ToString());

      DbContextOptions<myContext> options = new DbContextOptionsBuilder<myContext>()
        .UseSqlite(connection)
        .Options;

      using (var context = new myContext(options))
      {
        connection.Open();
        context.Database.EnsureCreated();
        await context.Seed();
      }

      using (var context = new myContext(options))
      {
        CountryZone z = context.CountryZones.AsNoTracking()
          .Include(e => e.CountryZoneType)
          .Single(e => e.UniqueId == CountryZoneTestData.TopCZ1);
        Assert.AreEqual(CountryZoneTypeTestData.LEVEL_0_TOP, z.CountryZoneType.UniqueId);
      }
      connection.Close();
      connection.Dispose();
    }
    
    [TestMethod]
    public async Task MyTestMethod2()
    {
      SqliteConnectionStringBuilder connectionStringBuilder = new SqliteConnectionStringBuilder
      {
        DataSource = “:memory:”,
        Cache = SqliteCacheMode.Private
      };
      SqliteConnection connection = new SqliteConnection(connectionStringBuilder.ToString());

      DbContextOptions<myContext> options = new DbContextOptionsBuilder<myContext>()
        .UseSqlite(connection)
        .Options;

      using (var context = new myContext(options))
      {
        connection.Open();
        context.Database.EnsureCreated();
        await context.Seed();
      }

      using (var context = new myContext(options))
      {
        Config setting = context.Configs.AsNoTracking().FirstOrDefault();

        Assert.IsNotNull(setting);
        Assert.AreEqual(ConfigSettings.PasswordMask, setting.Id);
      }
      connection.Close();
      connection.Dispose();
    }

Matt
Matt
1 year ago

I am trying to set up your code example in LinqPad as a unit test project. What I am missing is how you seed the database – there aren’t any details nor code samples so I can’t get this running. Can you help?

Matt
Matt
1 year ago
Reply to  Matt

Tried to do something like

internal void SeedDatabaseFourBooks()
{
var reviews = new List<Review>()
{
new Review() { ReviewId = 1, NumStars = 5 },
new Review() { ReviewId = 2, NumStars = 5 }
};

this.Reviews.AddRange(reviews);
this.Books.Add(new Book() { BookId = 1, Title = “Mostly Harmless”, 
Reviews = this.Reviews.Take(1).ToList() });
this.Books.Add(new Book() { BookId = 2, Title = “The Hobbit”, 
Reviews = this.Reviews.Skip(1).Take(1).ToList() });
this.SaveChanges();
}

but it don’t work with your code (“this” refers to MyContext, SeedDatabaseFourBooks() is a method of MyContext).

Lokesh
2 years ago

How do I get RowVersion field’s value while executing unit test cases?

D Hurtig
D Hurtig
2 years ago

I may have missed something. How to you force the SqlLite InMemory database to start empty for each test? If not how do you keep tests from conflicting when you have hundreds of tests to run if they share same data? I have switched using Bogus to generate unit test data for me.

With the InMemory Database you can just give database a different name. Not sure if this could cause memory issues though with hundreds of tests in a single project.

Björn
Björn
2 years ago
Reply to  Jon P Smith

Hello Jon,

I’m afraid that theSQLite tests do seem to be sharing data. I’m using the SqliteInMemory exactly as you are doing, with a local variable holding the options.

I have three tests. When I run them all the first one succeeds, the second one detects more rows than it should detect (since it finds rows inserted in the first test as well), and the third test complains about a duplicate insert (because it is inserting data that was already inserted in the second test). However, when I run all test one by one, they all succeed.

EDIT: I am using version 5.0.0 of EfCore.TestSupport.

Last edited 2 years ago by Björn
Björn
Björn
2 years ago
Reply to  Björn

I have found the culprit. A certain testobject was a static instance and that caused the databases to be kept alive. When I converted it to method to create new instances each call this behavior stopped.

mu88
2 years ago

Thank you for sharing your experiences and thoughts, Jon

Right in the beginning you mention that you wrote about 600 automated tests. Could you please tell us how long it takes to execute them? Beside the unit vs. integration test discussion, one big issue when it comes to testing is performance. With a standard mocking library and a mocked DbSet, I could always reach 100 tests per second. However, getting rid of the mocks is tempting so I’d be interested what the performance of EF Core with an in-memory database is.

tiagocesar
3 years ago

Great stuff and the package is really useful. As a token of appreciation AND also because it looks really nice, I bought your book 🙂

Keep pushing!

Jon P Smith
3 years ago
Reply to  tiagocesar

Thanks for your kind words Tiago, and the purchase of the book of course! Chapter 15 covers the “whys” of unit testing and uses this library throughout. In addition EfCore.TestSupport Wiki gives the the details and settings (see https://github.com/JonPSmith/EfCore.TestSupport/wiki ).

Since finishing the book I have created four more libraries – have a look at my GitHub site https://github.com/JonPSmith if you haven’t already.

tiagocesar
5 years ago
Reply to  Jon P Smith

Thanks for the extra juice, I bought the physical one at Book Depository, should get it over the weekend. Curious to explore!

Michel Eissa
Michel Eissa
3 years ago

This is an integration test not a unit test. You are not testing a functionality you have written but you are testing how the framework is dealing with a specific implementation details.

Jon P Smith
3 years ago
Reply to  Michel Eissa

Writing unit tests need to be simple and quick. I don’t use a repository pattern (see https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/ ) for basic CRUD (Create, Read, Update, Delete) operations and mocking the DbContext is hard work. Therefore I use an in-memory database.

If I’m writing business logic then I often use a repository pattern, which allows me to isolate the business logic. But CRUD its all about the the interaction with the database, such as did I read the right data or did I update the foreign key properly. In that case I use an in-memory database: its quick to write, quick to run, and checks that my CRUD operations work with a real database.

If that doesn’t suit you, then fine. But for me I actually like the link to the database as it catches errors I wouldn’t have found without it. The example of the disconnected update test in this article is one of many errors I wouldn’t have found without using EF directly.

Michel Eissa
Michel Eissa
4 years ago
Reply to  Jon P Smith

ORM framework such as EF core and EF 6.0 deal with CRUD ( inserting/updating the correct FK and etc) given the model is correct and accurate. If we have to go that deep and handle such operations why we even use an ORM in the first place.
You said tests should be simple and quick but you also mentioned in the post : “In some of my unit tests I need to use three instances of the DbContext to correctly test something”
Do you still consider this simple?

Thanks for the reply.

Sam Danielson
3 years ago
Reply to  Michel Eissa

Microsoft recommends against attempting to mock DbContext or IDbContext so any code the depends on it, such as a repository pattern object, needs to use an actual instance. It is possible to use layers and dependency inversion to insert Mocks, but there will always be this one layer that cannot be mocked. However I don’t see why this disqualifies it from being considered a unit test. I use a lot of real objects in unit tests. And in this case, it really is a mock, just simply implemented using the actual class like they do in monkey patching languages.

Richard
1 year ago
Reply to  Jon P Smith

I don’t believe anyone’s arguing over that, but rather arguing over the definition of this type of test. If you’re hitting an in-memory database, then some would argue it’s an integration test and not a unit test, that’s all.

Warren M Sirota
Warren M Sirota
3 years ago
Reply to  Michel Eissa

It’s an integration test, but integration tests like this can cover a lot of territory that you might otherwise be writing unit tests for, only more efficiently. I think it’s important to have an efficient test strategy and that this can be an important and powerful element of it.

Last edited 3 years ago by Warren M Sirota
Štěpán Eret
Štěpán Eret
3 years ago

Useful info, thanks.
But can you really still call these tests “unit tests”? IMHO when you do this, you test not only the piece of your implementation, but also the sqlite part.

Jon P Smith
3 years ago
Reply to  Štěpán Eret

Hi Štěpán,

While I don’t think I’m testing the Sqlite database provider (someone else has done that), but yes I am checking that my code can run on a SQL database, with all its constraints (well, the constraints that Sqlite has). Personally I find that useful, but I understand if you want to replace the database access with a mock or fake. The problem is that mocking EF is very hard.

Some people use a repository pattern on top of EF which allows you to mock the database. But I don’t use a repository pattern, so accessing a EF database is the next best thing (see my article https://www.thereformedprogrammer.net/is-the-repository-pattern-useful-with-entity-framework-core/ ).

There is no hard and fast way to do unit testing in EF Core, and this article describes one way that is pretty efficient in terms of quickly writing unit tests.

Christian Morin
Christian Morin
3 years ago

Hi Jon, In the Microsoft page [docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite] they close the connection after the unit test, but it seems that you don’t in your example (or repo). Will this cause an issue?

Jon P Smith
3 years ago

Hi Christian,

No, it won’t cause an issue in a unit test as the whole instance is dropped once the options part is disposed at the end of the unit test.

The thing I like about my SQLiteInMemory.CreateOptions() method is it hides all the connection setup and opening of the connection etc. Microsoft tend to write code that allows you to see how it works so that you can follow things, which I think is good. But when it comes to writing unit tests fast then a single method to set it all up in one go is what you want. I hope that answers your question.

PS. My CreateOptions method has an optional throwOnClientServerWarning (defaults to true) so that you get an exception if the LINQ you have written doesn’t convert to ‘good’ SQL. Another hidden, but useful feature.

Christian Morin
Christian Morin
3 years ago
Reply to  Jon P Smith

Thanks Jon. That’s great to hear. I will give your code a try. Thanks for the quick reply.

Warren M Sirota
Warren M Sirota
3 years ago
Reply to  Jon P Smith

I just discovered in-memory for testing (haven’t had a need in a while), and I love it. I love that you don’t have to deal with disposing of your test data at the end of the test, or worrying that someone else put something funky into a testing database somewhere (I have always like the idea of each test creating just the data it needs to test the functionality under test). I don’t have to know where on disk this create files, because it doesn’t. It runs really really fast. If I want my tests to run concurrently and independently, I can just give each one a different connection name, OR I can have them share data, my choice.

It makes mocking the database almost obsolete, in my opinion, and that is a big workload reduction.

Milos Davidovic
Milos Davidovic
3 years ago

Hi Jon and thanks for sharing you experience in working with EF Core.
I’ve installed EfCore.TestSupprot 1.8.0 via Nuget, and just wanted to point out that static class SQLiteInMemory is actually called SqliteInMemory (notice that SQL is capitalized). You might want to correct that in the blog post to make the example work.

Jon P Smith
3 years ago

Good catch! I have changed the post. Thanks for sharing.