Unable to replace AppDbContext settings for production with in-memory database for integration test

Question

I have made a minimalized code to illustrate the problem at my github repo: https://github.com/suugbut/MiniTest/tree/main.

I am learning integration test to test my minimal api. I cannot replace AppDbContext settings for production with in-memory database for integration test. I get the following errors:

 Api.Test.TodoEndpoint_IntegrationTest.NumberOfTodos_MustBe_Two
   Source: TodoEndpoint_IntegrationTest.cs line 44
   Duration: 1.1 sec

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

  Stack Trace: 
SqliteException.ThrowExceptionForRC(Int32 rc, sqlite3 db)
SqliteCommand.PrepareAndEnumerateStatements()+MoveNext()
SqliteCommand.GetStatements()+MoveNext()
SqliteDataReader.NextResult()
SqliteCommand.ExecuteReader(CommandBehavior behavior)
SqliteCommand.ExecuteDbDataReader(CommandBehavior behavior)
RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
Enumerator.InitializeReader(Enumerator enumerator)
<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
NonRetryingExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
Enumerator.MoveNext()
Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
lambda_method157(Closure, QueryContext)
QueryCompiler.Execute[TResult](Expression query)
EntityQueryProvider.Execute[TResult](Expression expression)
TodoEndpoint_IntegrationTest.NumberOfTodos_MustBe_Two() line 50
RuntimeMethodHandle.InvokeMethod(Object target, Void** arguments, Signature sig, Boolean isConstructor)
MethodBaseInvoker.InvokeWithNoArgs(Object obj, BindingFlags invokeAttr)

AppDbContext for production

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddDbContext<AppDbContext>(options =>
{
    var constr = builder.Configuration.GetConnectionString("DefaultConnection");
    options.UseSqlite(constr);
});

builder.Services.AddScoped<TodoRepo>();

var app = builder.Build();
// Others are removed for the sake of simplicity. 

"ConnectionStrings": {
  "DefaultConnection": "DataSource=Api.db"
}

If you want to inspect Program.cs, navigate to https://github.com/suugbut/MiniTest/blob/main/Api/Program.cs.

AppDbContext for integration test

public sealed class CustomWebApplicationFactory : WebApplicationFactory<Program>
{
    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
        base.ConfigureWebHost(builder);
        builder.ConfigureServices(isc =>
        {
            var descriptor = isc.SingleOrDefault(d => d.ServiceType == typeof(DbContextOptions<AppDbContext>));

            if (descriptor != null)
            {
                isc.Remove(descriptor);
            }

            isc.AddDbContext<AppDbContext>(options =>
            {
                var connection = new SqliteConnection("DataSource=:memory:");
                connection.Open();
                options.UseSqlite(connection);
            });

        });
    }

    // Others are removed for the sake of simplicity.
}

If you want to inspect CustomWebApplicationFactory.cs, navigate to https://github.com/suugbut/MiniTest/blob/main/Api.Test/CustomWebApplicationFactory.cs

Integration test

// Dependencies are removed for the sake of simplicity.

[Theory]
[InlineData(1)]
[InlineData(2)]
public async Task GetTodoById_Returns_OK(int id)
{
    // act
    var response = await _client.GetAsync($"/todos/{id}");

    // assert
    Assert.Equal(HttpStatusCode.OK, response.StatusCode);

    var content = await response.Content.ReadAsStringAsync();

    Assert.NotNull(content);
}

[Theory]
[InlineData(3)]
public async Task GetTodoById_Returns_NotFound(int id)
{
    // act
    var response = await _client.GetAsync($"/todos/{id}");

    // assert
    Assert.Equal(HttpStatusCode.NotFound, response.StatusCode);
}

[Fact]
public void NumberOfTodos_MustBe_Two()
{
    using (var scope = _factory.Services.CreateScope())
    {
        if (scope.ServiceProvider.GetRequiredService<AppDbContext>() is AppDbContext context)
        {
            var count = context.Todos.Count();
            Assert.Equal(2, count);
        }
    }
}

You can also inspect this test at https://github.com/suugbut/MiniTest/blob/main/Api.Test/TodoEndpoint_IntegrationTest.cs

Answer 1

The following:

isc.AddDbContext<AppDbContext>(options =>
{
    var connection = new SqliteConnection("DataSource=:memory:");
    connection.Open();
    options.UseSqlite(connection);
});

Will result in new connection created multiple times and SQLite in-memory is transient, i.e. as the docs state:

The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other.

So all the setup you have done (i.e. creating database and seeding) will not be persisted to the next opened connection.

You can fix this for example by simply moving connection creation outside the context setup lambda:

var connection = new SqliteConnection("DataSource=:memory:");
connection.Open();
isc.AddDbContext<AppDbContext>(options =>
{
                
    options.UseSqlite(connection);
});