Efcore: In-memory Sqlite, DbContext.Database.Migrate() throws exception SQLite Error 1: 'no such table: __EFMigrationsHistory'

[rcbevans]

I am writing a UWP app (with tests) that makes use of EF7 and sqlite, and have run into an issue when the sqlite connection is to an in memory database.

Functional impact

Migration fails when SQLite DataSource is set to :memory:, throwing exception SQLite Error 1: 'no such table: __EFMigrationsHistory'

Minimal repro steps

View project here.

Minimal UWP project with a Datacontext and model, with migration set up. The real project uses an on-disk database file local to the application.

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                string sqliteConnectionString = null;

                try
                {
                    var connectionString = new SqliteConnectionStringBuilder()
                    {
                        DataSource = Path.Combine(ApplicationData.Current.LocalFolder.Path, "RealData.db")
                    };
                    sqliteConnectionString = connectionString.ToString();
                }
                catch (InvalidOperationException)
                {
                    var connectionString = new SqliteConnectionStringBuilder()
                    {
                        DataSource = "RealData.db"
                    };
                    sqliteConnectionString = connectionString.ToString();
                }

                optionsBuilder.UseSqlite(sqliteConnectionString);
            }
        }

Minimal UWP Test project, dependent upon the main project, testing the main project's DbContext, but injecting an in memory sqlite database for testing purposes.

        [TestInitialize]
        public void TestSetUp()
        {
            var _serviceCollection = new ServiceCollection();
            _serviceCollection.AddEntityFramework().AddSqlite();
            _serviceProvider = _serviceCollection.BuildServiceProvider();

            var optionsBuilder = new DbContextOptionsBuilder<AppDataContext>();
            var connectionStringBuilder = new SqliteConnectionStringBuilder()
            {
                DataSource = ":memory:",
                // Using a real file works
                //DataSource = Path.Combine(ApplicationData.Current.LocalFolder.Path, "TestData.db"),
            };
            optionsBuilder.UseSqlite(connectionStringBuilder.ToString());
            _contextOptions = optionsBuilder.Options;

            _ctx = new AppDataContext(_serviceProvider, _contextOptions);
            // Throws exception because of DataSource being :memory:, using a file works
            _ctx.Database.Migrate();
        }

Debug unit test in UnitTest.cs and in TestSetup and observe exception thrown at call to context.Database.Migrate(). Removal of call to migrate and sqlite throws no such table for the DbSet being modified.

Expected result

The migration should complete successfully, allowing tests to run on a successfully migrated, empty, in memory sqlite database.

Actual result

Exception is thrown in call to migrate.

Further information

The issue is caused by setting DataSource of the sqlite connection to :memory:. By using a real file for the DataSource for the sqlite connection, the migration completes without error.

As a workaround, I am able to use a temporary database file in the test project, and manually ensure the file is deleted in the setup for each test method being run.

This is using

project.json

  "dependencies": {
    "EntityFramework.Commands": "7.0.0-rc1-final",
    "EntityFramework.Sqlite": "7.0.0-rc1-final",
    "Microsoft.CodeAnalysis.CSharp": "1.1.1",
    "Microsoft.NETCore.UniversalWindowsPlatform": "5.1.0"
  },
  "frameworks": {
    "uap10.0": {}
  },

package.appxmanifest

  <Dependencies>
    <TargetDeviceFamily Name="Windows.Universal" MinVersion="10.0.0.0" MaxVersionTested="10.0.0.0" />
  </Dependencies>

csproj

    <TargetPlatformVersion>10.0.10586.0</TargetPlatformVersion>
    <TargetPlatformMinVersion>10.0.10240.0</TargetPlatformMinVersion>

If you need any more information, please let me know.

Thanks

[bricelam]

You need to externally manage the lifetime of in-memory SQLite connections. Once all of the connections are closed, the database is destroyed. For example, in your test initialization code, before you do anything with the database, you could call _ctx.Database.OpenConnection(). Then, in your test cleanup, call _ctx.Database.CloseConnection();.

[rcbevans]

Yeah, didn't work quite like that, but along those lines. That sorted it, cheers

[Ciantic]

@o0rebelious0o how did you get this working? It works better if I throw this db.Database.OpenConnection(); on my init db, but if I start using the app it still has closed the connection.

I'm trying to maintain the connection when I use the application (not just when running tests) so it needs to be semi-permanent.

edit I think there are valid use-cases for in-memory SqlLite database _while the app is running_, not just testing short lived databases, and it shouldn't be made harder than it needs be.

[bricelam]

@Ciantic Can you create a new issue for us to consider ways to improve the experience.

[Ciantic]

@bricelam I reopened my issue #5086 and renamed it to mean what I meant: I want to have in-memory SqlLite database to persist while the app is running.

[AdamJachocki]

It makes testing in memory database completely unsuitable. No one keeps connection open through the lifetime of the application. So you should create database before every test. I have additional problem with that, because I have to use some custom steps during migration, which involves opening connection and closing connection (if it was not opened).

You should keep in memory database until user explicitly calls something like EfCore.UnInitInMemoryDatabase(). What about this kind of solution?

[ajcvickers]

@AdamJachocki See #16103

[Timovzl]

I only just spotted this and haven't tried it, but perhaps it's made for this scenario:
Sharable in-memory

Data Source=Sharable;Mode=Memory;Cache=Shared