No such table Exception on SaveChanges() - SQLite in memory

[ScottKane]

To Reproduce

I have been trying to set up EF Core inside Unity game engine with an in memory Sqlite provider but I'm getting a "SqliteException: SQLite Error 1: no such table" when I call SaveChanges().

public class Bootstrap : MonoBehaviour
{
    void Awake()
    {
        var services = new ServiceCollection();

        services.AddDbContext<GameDbContext>(options => options.UseSqlite("Data Source=:memory:"));

        var provider = services
            .BuildServiceProvider()
            .UpdateDatabase(); // I have to do this as I can't run `dotnet ef` against the Unity generated project and can't use the Package Manager Tools either.
    }
}

public static class ServiceProviderExtensions
{
    public static IServiceProvider UpdateDatabase(this IServiceProvider provider)
    {
        using (var scope = provider.GetRequiredService<IServiceScopeFactory>().CreateScope())
        using (var context = scope.ServiceProvider.GetService<GameDbContext>())
            context.Database.Migrate();

        return provider;
    }
}

public class GameDbContext : DbContext
{
    public GameDbContext(DbContextOptions<GameDbContext> options) : base(options) { }

    public virtual DbSet<PlayerModel> Players { get; set; }
}

public class PlayerModel
{
    public Guid Id { get; set; }
}

public class Player : MonoBehaviour
{
    void Awake()
    {
        var model = new PlayerModel{ Id = gameObject.Id() };
        
        _db.Players.Add(model);
        _db.SaveChanges();
    }
}

Produces the following Exception:

SqliteException: SQLite Error 1: 'no such table: Players'.
Microsoft.Data.Sqlite.SqliteException.ThrowExceptionForRC (System.Int32 rc, SQLitePCL.sqlite3 db) (at <69c3d7c4d06b42918fa7f49d899a761c>:0)
Microsoft.Data.Sqlite.SqliteCommand+<PrepareAndEnumerateStatements>d__64.MoveNext () (at <69c3d7c4d06b42918fa7f49d899a761c>:0)
Microsoft.Data.Sqlite.SqliteCommand+<GetStatements>d__54.MoveNext () (at <69c3d7c4d06b42918fa7f49d899a761c>:0)
Microsoft.Data.Sqlite.SqliteDataReader.NextResult () (at <69c3d7c4d06b42918fa7f49d899a761c>:0)
Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader (System.Data.CommandBehavior behavior) (at <69c3d7c4d06b42918fa7f49d899a761c>:0)
Microsoft.Data.Sqlite.SqliteCommand.ExecuteDbDataReader (System.Data.CommandBehavior behavior) (at <69c3d7c4d06b42918fa7f49d899a761c>:0)
System.Data.Common.DbCommand.ExecuteReader () (at <290425a50ff84a639f8c060e2d4530f6>:0)
(wrapper remoting-invoke-with-check) System.Data.Common.DbCommand.ExecuteReader()
Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader (Microsoft.EntityFrameworkCore.Storage.RelationalCommandParameterObject parameterObject) (at <e24c1ec81e4d42878599c70b53cf654a>:0)
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute (Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection) (at <e24c1ec81e4d42878599c70b53cf654a>:0)
Rethrow as DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Microsoft.EntityFrameworkCore.Update.ReaderModificationCommandBatch.Execute (Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection) (at <e24c1ec81e4d42878599c70b53cf654a>:0)
Microsoft.EntityFrameworkCore.Update.Internal.BatchExecutor.Execute (System.Collections.Generic.IEnumerable`1[T] commandBatches, Microsoft.EntityFrameworkCore.Storage.IRelationalConnection connection) (at <e24c1ec81e4d42878599c70b53cf654a>:0)
Microsoft.EntityFrameworkCore.Storage.RelationalDatabase.SaveChanges (System.Collections.Generic.IList`1[T] entries) (at <e24c1ec81e4d42878599c70b53cf654a>:0)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges (System.Collections.Generic.IList`1[T] entriesToSave) (at <fae6ba2219e148cab926c1ee75b2e33b>:0)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges (Microsoft.EntityFrameworkCore.DbContext _, System.Boolean acceptAllChangesOnSuccess) (at <fae6ba2219e148cab926c1ee75b2e33b>:0)
Microsoft.EntityFrameworkCore.Storage.Internal.NoopExecutionStrategy.Execute[TState,TResult] (TState state, System.Func`3[T1,T2,TResult] operation, System.Func`3[T1,T2,TResult] verifySucceeded) (at <fae6ba2219e148cab926c1ee75b2e33b>:0)
Microsoft.EntityFrameworkCore.ChangeTracking.Internal.StateManager.SaveChanges (System.Boolean acceptAllChangesOnSuccess) (at <fae6ba2219e148cab926c1ee75b2e33b>:0)
Microsoft.EntityFrameworkCore.DbContext.SaveChanges (System.Boolean acceptAllChangesOnSuccess) (at <fae6ba2219e148cab926c1ee75b2e33b>:0)
Microsoft.EntityFrameworkCore.DbContext.SaveChanges () (at <fae6ba2219e148cab926c1ee75b2e33b>:0)
Player.Awake () (at Assets/Scripts/SqliteTest.cs:46)

Additional context

Microsoft.Data.Sqlite version: 3.1.6
Target framework: .NET 4.6.1
Operating system: Windows 10 x64

[smitpatel]

SQLite inmemory database cease to exist once the connection is closed. You need to use an open connection with DbContext rather than connection string to keep database alive whole throughout your application. See our documentation for more details on how to test with SQLite inmemory database.

[ScottKane]

@smitpatel Awesome thank you!

For anyone who comes across the same problem here is my updated implementation:

public class Bootstrap : MonoBehaviour
{
    void Awake()
    {
        var services = new ServiceCollection();

        services.AddSingleton<DbConnection, SqliteConnection>(serviceProvider =>
        {
            var connection = new SqliteConnection("Data Source=:memory:");
            connection.Open();
            return connection;
        });

        services.AddDbContext<GameDbContext>((serviceProvider, options) =>
        {
            var connection = serviceProvider.GetRequiredService<DbConnection>();
            options.UseSqlite(connection);
        });

        var provider = services
            .BuildServiceProvider()
            .UpdateDatabase();
    }
}

public static class ServiceProviderExtensions
{
    public static IServiceProvider UpdateDatabase(this IServiceProvider provider)
    {
        using (var scope = provider.GetRequiredService<IServiceScopeFactory>().CreateScope())
        using (var context = scope.ServiceProvider.GetService<GameDbContext>())
            context.Database.EnsureCreated();

        return provider;
    }
}