Misleading/incomplete documentation re SQLite in-memory DB

[Yaevh]

The documentation page on Using SQLite to test an EF Core application https://docs.microsoft.com/en-us/ef/core/testing/sqlite contains a tutorial on how to create and use a DbContext with SQLite in-memory database. It however only describes how to instantiate the DbContext explicitly, and not how to use IoC container to do it.

In many integration-testing scenarios you do not instantiate DbContext explicitly, but rather re-configure your ServiceProvider, replacing the original .AddDbContext() call with a call to .AddDbContext() using SQLite in-memory DB, and then let your application resolve the DbContext through the IoC container (either Microsoft.Extensions.DependencyInjection or some other library).

Here is when it becomes misleading: in most cases in both .NET Core itself and various third-party libraries, when using extension methods on ServiceCollection like this:

services.AddSomeService(options => options.ReadConfigFromFile("someSettings.json"));

the options callback is executed only once, during configuration phase. On the other hand, when you call

services.AddDbContext<SomeDbContext>(options => options.UseSqlite(CreateInMemoryDatabase()))

the method .AddDbContext() behaves completely different - options.UseSqlite() is called every time DbContext is resolved. Thus, if you pass another callback to options (in this case CreateInMemoryDatabase()), it is invoked every time you resolve DbContext from the ServiceProvider. As a result, if you follow the directions from https://docs.microsoft.com/en-us/ef/core/testing/sqlite, you end up with each DbContext working with their own, fresh in-memory database - and thus all the changes made in previous DbContexts are lost, which is not you expect when trying to work with a DbContext backed by an in-memory database in testing scenarios.

Consider the following repo: https://github.com/Yaevh/SqliteInMemoryConnectionReuseBug. Test method DbContext_with_callback_connection_fails() fails with exception (details attached below), even though it is based on the example from the official tutorial (https://docs.microsoft.com/en-us/ef/core/testing/sqlite). Also observe that during the execution of the aforementioned test method, BuildConnection() is called not once (during the configuration phase of the ServiceProvider), but two times (once for each of the two ServiceProvider.CreateScope() calls). On the other hand, test method DbContext_with_explicit_connection_succeeds() works as expected, because the options are configured using a single pre-instantiated instance of DbConnection - but the documentation not only makes no mention of configuring DbContext this way, it also actually encourages you to do it the wrong way (i.e. to pass CreateInMemoryDatabase() callback to .AddDbContext()).

The solution would be to:

1. rework .AddDbContext() so that it resolves DbContextOptions only once, instead of once-per-request
2. improve the API documentation for .AddDbContext, so that it states explicitly that the options callback is called once-per-resolution, not once-per-configuration
3. improve https://docs.microsoft.com/en-us/ef/core/testing/sqlite to cover the aforementioned use case and show how to pre-instantiate DbConnection at the SetUp part of your test fixture

I know that 1. is probably too much to ask, as it would be a major breaking change affecting the entire EF Core, but 2. and 3. should be rather simple to do and may save some people a lot of time otherwise spent on debugging and digging through the source code (like I had to do before figuring out the solution).

Related to dotnet/efcore#16103 and dotnet/efcore#22018

Stack trace

The following exception can be observed in https://github.com/Yaevh/SqliteInMemoryConnectionReuseBug, method CustomerDbContextTests.DbContext_with_callback_connection_fails():

Message: 
    Microsoft.Data.Sqlite.SqliteException : SQLite Error 1: 'no such table: Customers'.

  Stack Trace: 
    SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
    SqliteCommand.PrepareAndEnumerateStatements(Stopwatch timer)+MoveNext()
    SqliteCommand.GetStatements(Stopwatch timer)+MoveNext()
    SqliteDataReader.NextResult()
    SqliteCommand.ExecuteReader(CommandBehavior behavior)
    SqliteCommand.ExecuteReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
    SqliteCommand.ExecuteDbDataReaderAsync(CommandBehavior behavior, CancellationToken cancellationToken)
    RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
    RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)
    AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)
    AsyncEnumerator.MoveNextAsync()
    EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
    EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)
    CustomerDbContextTests.DbContext_with_callback_connection_fails() line 64

Include version information

Microsoft.Data.Sqlite version: 5.0.3 Target framework: .NET 5.0 Operating system: Windows 10

[roji]

@Yaevh just to be clear, does the above concern integration testing or unit testing? Because on the one hand you mentioned integration tests in the beginning, but on the other hand you comments seem relevant to SQLite in-memory, which is a mode used for unit testing.

Also, the docs section on SQLite in-memory does show the pre-instantiation of the DbConnection in the test class's constructor - although this sample doesn't use DI.

[Yaevh]

@roji

just to be clear, does the above concern integration testing or unit testing? Because on the one hand you mentioned integration tests in the beginning, but on the other hand you comments seem relevant to SQLite in-memory, which is a mode used for unit testing.

I use it for testing MediatR request handlers (ports-and-adapters architecture style), which would probably be somewhere between unit and integration tests, though closer to the latter ones.

Also, the docs section on SQLite in-memory does show the pre-instantiation of the DbConnection in the test class's constructor - although this sample doesn't use DI.

Yes, that's the misleading part. When using DbContext with a manually preinstantiated DbConnection, like in the docs you mentioned, the code works perfectly fine. But when you try to use similar code in the context of DI, the DbConnection is instantiated multiple times, which leads to the bug. The docs make no mention of this.