EntityFrameworkCore SQLite in-memory db tables are not created

Question

For integration tests I am using an EntityFrameworkCore SQLite in-memory db and creating its schema as per Microsoft docs, but when I attempt to seed data an exception is thrown that tables do not exist.

The mouse-over docs for DbContext.Database.EnsureCreated(); :

Ensure that the database for the context exists. If it exists, no action is taken. If it does not exist then the database and all its schema are created. If the database exists, then no action is made to ensure it is compatible with the model for this context.

I've read that an EntityFrameworkCore in-memory db only exists as long as an open connection exists, and so I tried explicitly creating a var connection = new SqliteConnection("DataSource=:memory:"); instance and wrapping the below code in a using(connection) {} block and passing the connection instance options.UseSqlite(connection);, but DbContext.Database.EnsureCreated(); still doesn't create any db-objects

public class CustomWebApplicationFactory<TStartup> : WebApplicationFactory<Startup>
{
    protected override IWebHostBuilder CreateWebHostBuilder()
    {
        return WebHost.CreateDefaultBuilder()
            .UseStartup<Startup>();
    }

    protected override void ConfigureWebHost(IWebHostBuilder builder)
    {
      using (var connection = new SqliteConnection("DataSource=MySharedInMemoryDb;mode=memory;cache=shared"))
      {
          connection.Open();
          builder.ConfigureServices(services =>
          {
              var serviceProvider = new ServiceCollection()
                  .AddEntityFrameworkSqlite()
                  .BuildServiceProvider();

              services.AddDbContext<MyDbContext>(options =>
              {
                  options.UseSqlite(connection);
                  options.UseInternalServiceProvider(serviceProvider);
              });

              var contextServiceProvider = services.BuildServiceProvider();

              // we need a scope to obtain a reference to the database contexts
              using (var scope = contextServiceProvider.CreateScope())
              {
                  var scopedProvider = scope.ServiceProvider;

                  var logger = scopedProvider.GetRequiredService<ILogger<CustomWebApplicationFactory<TStartup>>>();

                  using (var myDb = scopedProvider.GetRequiredService<MyDbContext>())
                  {
                      // DEBUG CODE
                      // this returns script to create db objects as expected
                      // proving that MyDbContext is setup correctly
                      var script = myDb.Database.GenerateCreateScript();
                      // DEBUG CODE

                      // this does not create the db objects ( tables etc )
                      // this is not as expected and contrary to ms docs
                      var result = myDb.Database.EnsureCreated();

                      try
                      {
                          SeedData.PopulateTestData(myDb);
                      }
                      catch (Exception e)
                      {
                          // exception is thrown that tables don't exist
                          logger.LogError(e, $"SeedData.PopulateTestData(myDb) threw exception=[{e.Message}]");
                      }
                  }
              }
          });
        }
        builder.UseContentRoot(".");
        base.ConfigureWebHost(builder);
    }

Please note that in this post I'm only asking the question why doesn't DbContext.Database.EnsureCreated(); create the schema as expected. I'm not presenting the above code as a general pattern for running integration-tests.

Answer 1

Using a non-shared SQLite in-memory database

SQLite in-memory databases are by default transient. As the documentation states:

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

EF Core's DbContext on the other hand, always opens and closes connections to the database automatically, unless you pass an already open connection.

Therefore, in order to use the same SQLite in-memory database across multiple calls in EF Core, you need to create a SqliteConnection object separately and then pass it to every DbContext.

For example:

  var keepAliveConnection = new SqliteConnection("DataSource=:memory:");
  keepAliveConnection.Open();

  services.AddDbContext<MyContext>(options =>
  {
    options.UseSqlite(keepAliveConnection);
  });

Note that SqliteConnection isn't really thread-safe, so this approach is applicable only to single-threaded scenarios. Any time you want to have a shared database that can be accessed by multiple threads (e.g. in an ASP.NET Core application, servicing multiple requests), you should consider using an on-disk database.

By the way, this is the approach currently used in the EF Core documentation on how to use SQLite in-memory databases for testing.

Using a shared SQLite in-memory database

SQLite also supports named shared in-memory databases. By using the same connection string, multiple SqliteConnection objects can connect to the same database. However:

The database is automatically deleted and memory is reclaimed when the last connection to the database closes.

So it is still necessary to maintain a separate open connection object for the database to be usable across multiple EF Core calls. For example:

  var connectionString = "DataSource=myshareddb;mode=memory;cache=shared";
  var keepAliveConnection = new SqliteConnection(connectionString);
  keepAliveConnection.Open();

  services.AddDbContext<MyContext>(options =>
  {
    options.UseSqlite(connectionString);
  });

Note that this approach isn’t limited to a single thread, because each DbContext gets its own instance of SqliteConnection.