Using SQLite to test an EF Core application

Warning

Using SQLite can be an effective way to test an EF Core application. However, problems can arise where SQLite behaves differently from other database systems. See Testing code that uses EF Core for a discussion of the issues and trade-offs.

This document builds uses on the concepts introduced in Sample showing how to test applications that use EF Core. The code examples shown here come from this sample.

Using SQLite in-memory databases

Normally, SQLite creates databases as simple files and accesses the file in-process with your application. This is very fast, especially when using a fast SSD.

SQLite can also use databases created purely in-memory. This is easy to use with EF Core as long as you understand the in-memory database lifetime:

  • The database is created when the connection to it is opened
  • The database is deleted when the connection to it is closed

EF Core will use an already open connection when given one, and will never attempt to close it. So the key to using EF Core with an in-memory SQLite database is to open the connection before passing it to EF.

The sample achieves this with the following code:

public class SqliteInMemoryItemsControllerTest : ItemsControllerTest, IDisposable
{
    private readonly DbConnection _connection;

    public SqliteInMemoryItemsControllerTest()
        : base(
            new DbContextOptionsBuilder<ItemsContext>()
                .UseSqlite(CreateInMemoryDatabase())
                .Options)
    {
        _connection = RelationalOptionsExtension.Extract(ContextOptions).Connection;
    }

    private static DbConnection CreateInMemoryDatabase()
    {
        var connection = new SqliteConnection("Filename=:memory:");

        connection.Open();

        return connection;
    }

    public void Dispose() => _connection.Dispose();
}

Notice:

  • The CreateInMemoryDatabase method creates a SQLite in-memory database and opens the connection to it.
  • The created DbConnection is extracted from the ContextOptions and saved.
  • The connection is disposed when the test is disposed so that resources are not leaked.

Note

Issue #16103 is tracking ways to make this connection management easier.