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.

EF Core in-memory databases (as opposed to SQLite in-memory) don’t have the behavior you describe. Can you point me to the document where you found this so I can fix it? Thanks. — divega
– divega, Commented May 30, 2019 at 15:16

divega · Accepted Answer · 2019-05-31 08:24:28Z

52

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.

edited May 31, 2019 at 8:24

answered May 29, 2019 at 20:07

divega

6,4861 gold badge33 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

BaltoStar Over a year ago

why would DbContext.Database.EnsureCreated(); involve "(using) the same SQLite in-memory database across multiple calls in EF Core" ? And in any event (as I noted in my post) I've already tried explicitly creating a var connection = new SqliteConnection("DataSource=:memory:"); instance and wrapping my code in a using block and passing the connection instance options.UseSqlite(connection); , but DbContext.Database.EnsureCreated(); still doesn't create any db-objects.

BaltoStar Over a year ago

DbContext.Database.EnsureCreated(); is a framework method on DatabaseFacade and I'd expect it only makes a single call into the in-memory SQLite instance to execute a create script identical to that returned by DbContext.Database.GenerateCreateScript();

divega Over a year ago

I did read your question closely. That part of my answer is for everyone else that ever finds this answer because they are trying to do similar things (but not exactly the same) as you. Using SQLite in-memory databases has this specific pitfall that it is worth calling out.

BaltoStar Over a year ago

Your gist is totally artificial. My code is within the context of a CustomWebApplicationFactory that is necessary to simulate the web app for integration testing.

worldwildwebdev Over a year ago

Note that Mode=Memory is not working on 2.1 but fixed in 3.1. I got it working with this connection string "DataSource=file::memory:?cache=shared"

|

Collectives™ on Stack Overflow

EntityFrameworkCore SQLite in-memory db tables are not created

1 Answer 1

Using a non-shared SQLite in-memory database

Using a shared SQLite in-memory database

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Using a non-shared SQLite in-memory database

Using a shared SQLite in-memory database

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related