efcore: Migrations doesn't work with SQLite in-memory database

I am using Code First to build my databse. When I run the initial ‘dotnet ef database update’ command, I get the following error:

PS D:\Projects\NetCoreDummy\NetCoreDummy.Database> dotnet ef database update
Database context created.
Applying migration '20170918143246_Initial'.
Failed executing DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
VALUES ('20170918143246_Initial', '2.0.0-rtm-26452');
Microsoft.Data.Sqlite.SqliteException (0x80004005): SQLite Error 1: 'no such table: __EFMigrationsHistory'....

When I call ‘dotnet ef migrations script’, the generated sql file contains the required command to create the __EFMigrationsHistory table. Should not these two be identical? If the script contains the table creation then the generated Migration class should too.

My DbContextFactory looks like this:

public class DummyDbContextFactory : IDesignTimeDbContextFactory<DummyDbContext>
{
	private ConfigurationBuilder _configBuilder = new ConfigurationBuilder();
	private IConfigurationRoot _configRoot;
	private string _jsonConfigFile => AppContext.BaseDirectory + "appsettings.json";
	private string _connectionStringPath => "ConnectionStrings:DummySqlite";

	public string ConnectionString => "Data Source=Dummy.db; Mode=Memory; Cache=Default";

	public DummyDbContextFactory()
	{
		//_configBuilder.AddJsonFile(_jsonConfigFile);
		//_configRoot = _configBuilder.Build();

		//ConnectionString = _configRoot[_connectionStringPath];
	}

	public DummyDbContext CreateDbContext(string[] args)
	{
		var optionsBuilder = new DbContextOptionsBuilder<DummyDbContext>();
		if (args.Length == 0)
		{
			optionsBuilder.UseSqlite(ConnectionString);
		}
		else
		{
			optionsBuilder.UseSqlite(args[0]);
		}

		return new DummyDbContext(optionsBuilder.Options);
	}
}

The csproj:

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <TargetFramework>netcoreapp2.0</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.0" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration" Version="2.0.0" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="2.0.0" />
  </ItemGroup>
  
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.0" />
  </ItemGroup>
  
</Project>

Is there something that I am missing or is this a bug?

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 16 (7 by maintainers)

Commits related to this issue

Running UserDbContext testwith inmemory SQLite instances That took hours to accomplish. SQLite supports inmemory databases. Every connection gets a separate inmemory instance of a database. DbContext... — committed to AtanasBunchev/GameSalad by AtanasBunchev 7 months ago

Most upvoted comments

No, this does not make any sense - dont spend your time on it - just using EnsureCreated should be fine

ErikEJ on Sep 20, 2017

How does one use an in-memory db for unit testing a method which uses a dbcontext? It seems the whole point of supporting in-memory for EF is pointless.

jholovacs on Jul 13, 2018

@gabor182 This is happening because the SQLite database is in in-memory mode. The in-memory mode database only persists as long as a connection to it is open, so I’m not sure if this can be made to work correctly with Migrations or not–we will discuss. Everything worked correctly for me when I changed to using an on-disk database. You might also consider using context.Database.EnsureCreated() for testing with an in-memory database that has been created with your current Code First model.

ajcvickers on Sep 19, 2017

It’s no problem to use migrations with the in-memory SQLite DB as long as you keep the connection open:

var services = new ServiceCollection();
using var dbConn = new SqliteConnection("Filename=:memory:");
await dbConn.OpenAsync();
services.AddDbContext<MyDbContext>(dbOptions => dbOptions.UseSqlite(dbConn));

var sp = services.BuildServiceProvider();

var context = sp.GetRequiredService<MyDbContext>();
await context.Database.MigrateAsync();

// do more stuff here

When you provide an open connection to the UseSqlite method, EF will reuse the open connection and doesn’t create a new one or close the connection. You are responsible for closing the connection and for disposing it correctly.

berhir on Jul 14, 2023

Thanks for the response. I hit this issue first, but just found another one that explains why.

lhengen on Mar 6, 2019

We discussed this in triage and decided we won’t fix it. Even if the database update command completed without error, the database would still be immediately thrown away before any tests could use it. EnsureCreated can be used for in-memory testing, or an on-disk SQLite database can be used if Migrations are needed.

ajcvickers on Sep 20, 2017