Using SQLite in memory databases

SQLite has an in-memory mode that makes using SQLite in-memory databases easy to use. The positives are that an in-memory database obeys the 6 rules for EF Core tests plus some extra .

1. Rule 1 - The database is unique, not only to the test class but to the test method.
2. Rule 2 - The database is empty, which is a good starting point for a unit test.
3. Rule 3 - Because you create a new database every time, it matches the current EF Core's Model.
4. Your unit tests will run successfully in the Test part of a DevOps pipeline without needing a database.
5. Your unit tests are faster.

Should I use SQLite in-memory database or a database like my production system?

You can use SQLite in-memory databases for unit testing your EF Core code, even if your production database is of a different type, say SQL Servers. BUT(!) if your production database isn't using a SQLite database, then you can have problems, mainly around SQL commands and some types such as decimal. So, if in doubt, always test using the same database type as your production system uses.

NOTE: I give a lot more information on how to decide if you can use a SQLite in-memory database in chapter 17 of my book, Entity Framework Core in Action, second edition.

How to create options for SQLite in-memory database

The SqliteInMemory.CreateOptions<T> method will create options that will provide a SQLite, in-memory database for unit testing. The code below shows how is can be used to create .

[Fact]
public void TestSqliteOk()
{
    //SETUP
    var options = SqliteInMemory.CreateOptions<EfCoreContext>(); 
    using var context = new EfCoreContext(options))
    
    context.Database.EnsureCreated();

    //... rest of unit test goes here

Ability to add extra options to the DbContextOptionsBuilder<T>

The SQLite in-memory options extension methods have an optional parameter that allows you to set extra options at the DbContextOptionsBuilder<T> level. Below is part of the unit tests showing how to add/override options.

//... previous code removed to focus on the feature
var options = SqliteInMemory.CreateOptions<BookContext>(
    //sets a tracking behavior
    builder => builder.UseQueryTrackingBehavior(QueryTrackingBehavior.NoTracking)); 
using (var context = new BookContext(options))
{
    //VERIFY
    var book = context.Books.First();
    context.Entry(book).State.ShouldEqual(EntityState.Detached);
}

Obtaining EF Core logging data

Its often useful to see what EF Core is doing when something isn't working properly. The `SqliteInMemory.CreateOptionsWithLogTo method returns the logs. Below is a simple version that captures the logs into a list, but more complex options are possible - see Tools for capturing EF Core logging for more details.

var logs = new List<string>();
var options = SqliteInMemory.CreateOptionsWithLogTo<BookContext>(log => logs.Add(log));
using var context = new BookContext(options);
//... rest of test left out

How to get an empty database with the correct schema

Because an SQLIte in-memory database is created within the test method the best way (only way) is to call EF Core's EnsureCreated method as shown below.

using var options = SqliteInMemory.CreateOptions<BookContext>();
using var context = new BookContext(options);

context.Database.EnsureCreated();

Working with multiple context instances - WARNING!

Rule 5, says you must make sure your database test match your real-world usage. This means you need to you have to find a way to stop EF Core from tracking entities across the the three stages of a test, i.e. SETUP, ATTEMPT, and VERIFY. The classic way to do this is to have a using block e.g., using (var context = new BookContext(options)) {...} for each stage. This doesn't work so well with a SQLite in-memory database since version 5.0.0 of this library. This section shows how to get around this iss

The SqliteInMemory.CreateOptions<MyDbContext>() method returns a class that implements DbContextOptions<MyDbContext> needed by the database, but now also implements IDisposable. This is done to dispose SQLite's connection (which contains the in-memory data) when the context is disposed. (I didn't do that in the version 3.2.0, and I should have because the database should be disposed at the end.)

This means when the application DbContext using that option is disposed the Dispose method in the options class is also called and the connection is disposed. This means if you create multiple contexts, then you need to do something extra to stop the the options variable being disposed, otherwise the database will be lost to subsequent DbContext instances.

For example, the following code will fail.

public void TestSqliteTwoInstancesBAD()
{
    //SETUP
    var options = SqliteInMemory.CreateOptions<BookContext>();
    using (var context = new BookContext(options))
    {
        context.Database.EnsureCreated();
        context.SeedDatabaseFourBooks(); 
    }
    using (var context = new BookContext(options))
    {
        //ATTEMPT
        //THIS WILL FAIL!!!! THIS WILL FAIL!!!! THIS WILL FAIL!!!!
        var books = context.Books.ToList();

        //VERIFY
        books.Last().Reviews.ShouldBeNull();
    }
}

There are three ways around this:

1. Best approach: Have one instance of the application DbContext and use ChangeTracker.Clear().
2. Keep your Using(var...: Have two instances of the application DbContext and call options.StopNextDispose()
3. Lots of DbContext instances: Turn off Dispose and manually dispose at the end

1. Best approach: One instance and use ChangeTracker.Clear()

In EF Core 5 there is new feature triggered by context.ChangeTracker.Clear(). This clears out all the tracked entities in the current instance of the application DbContext. This means you don't need multiple DbContexts to check that your second part worked property.

public void TestSqliteOneInstanceWithChangeTrackerClearOk()
{
    //SETUP
    var options = SqliteInMemory.CreateOptions<BookContext>();
    using var context = new BookContext(options);
    context.Database.EnsureCreated();
    context.SeedDatabaseFourBooks();

    context.ChangeTracker.Clear(); //NEW LINE ADDED

    //ATTEMPT
    var books = context.Books.ToList();

    //VERIFY
    books.Last().Reviews.ShouldBeNull();
}

NOTE: It also allows you to use using var context = ... - see line 5. This makes the code simpler, and quicker to write. That's why I recommend this version

2. Keep your Using(var...: Use options.StopNextDispose()

If you want to keep the two instances of the application DbContext, then you need to use the options.StopNextDispose() to stop the dispose on the first application DbContext instance. You can call the StopNextDispose method any time before the first application DbContext instance is disposed, but I tend to do it right under the creating of the option, as shown in line 5 of the code below

public void TestSqliteTwoInstancesGood()
{
    //SETUP
    var options = SqliteInMemory.CreateOptions<BookContext>();
    options.StopNextDispose();
    using (var context = new BookContext(options))
    {
        context.Database.EnsureCreated();
        context.SeedDatabaseFourBooks(); 
    }
    using (var context = new BookContext(options))
    {
        //ATTEMPT
        var books = context.Books.ToList();

        //VERIFY
        books.Last().Reviews.ShouldBeNull();
    }
}

3. Lots of DbContext instances: Turn off Dispose and manually dispose at the end

If you have multiple instances of the application DbContext, then you can use the options.TurnOffDispose() and call the options.ManualDispose() methods at the end of the unit test.

public void TestSqliteThreeInstancesOk()
{
    //SETUP
    var options = SqliteInMemory.CreateOptions<BookContext>();
    options.TurnOffDispose();
    using (var context = new BookContext(options))
    {
        context.Database.EnsureCreated();
        context.SeedDatabaseFourBooks(); 
    }
    using (var context = new BookContext(options))
    {
        //ATTEMPT
        var books = context.Books.ToList();

        //VERIFY
        books.Last().Reviews.ShouldBeNull();
    } 
    using (var context = new BookContext(options))
    {
        //ATTEMPT
        var books = context.Books.ToList(); 

        //VERIFY
        books.Last().Reviews.ShouldBeNull();
    }
    options.ManualDispose();
}

NOTE the call to options.TurnOnDispose(); before the last application DbContext.