No such table - EF Core with Sqlite in memory

Question

I'm trying to set up my testing environment, but I have trouble with Sqlite adapter. Each created context has the same connection so, in-memory databse should be built properly for each context.

But when I'm trying to add something new, it throws error: "No such table: %here_is_my_tablename%".

I think my configuration should be good.

Base:

public abstract class BaseServiceTests : IDisposable
{
    protected readonly SqliteConnection Connection;

    public BaseServiceTests()
    {
        Connection = new SqliteConnection("DataSource=:memory:");
        Connection.Open();
        Assert.NotNull(Connection);

        using (var ctx = BuildCoreContext())
        {
            ctx.Database.EnsureCreated();
        }   

        using (var ctx = BuildWebContext())
        {
            ctx.Database.EnsureCreated();
        }     
    }

    public void Dispose()
    {
        Connection.Close();
    }

    public DbContextOptions<TContext> Options<TContext>() where TContext: DbContext
    {
        var options = new DbContextOptionsBuilder<TContext>()
            .UseSqlite(Connection)
            .Options;

        return options;
    }

    public ServiceRequestCoreContext BuildCoreContext()
    {
        var ctx = new ServiceRequestCoreContext(Options<ServiceRequestCoreContext>(), null);
        ctx.Database.OpenConnection();

        return ctx;
    }

    public ServiceRequestWebContext BuildWebContext()
    {
        var ctx = new ServiceRequestWebContext(Options<ServiceRequestWebContext>(), null);
        ctx.Database.OpenConnection();

        return ctx;
    }
}

Test

public class RequestServiceTests : BaseServiceTests
{
    public async Task Prepare()
    {
        using (var ctx = BuildCoreContext())
        {
            await ctx.RequestTypes.AddAsync(new RequestType((int)RequestTypes.Order, "TestType - test"));
            await ctx.RequestStatuses.AddAsync(new RequestStatus((int)RequestStatuses.AcceptedForVeryfication, "test - test", "test - test"));
            await ctx.Companies.AddAsync(new CustomerCompany(1, "test - test", "Test - test"));
            await ctx.SaveChangesAsync();
        }
    }

    [Fact]
    public async Task when_creating_new_request_it_should_not_be_null()
    {
        //Arrange 
        await Prepare();
        var customerId = 1;
        var iGen = new IdentifyGenerator();

        //Act
        using (var webCtx = BuildWebContext())
        {
            webCtx.Database.OpenConnection();
            var service = new RequestService(webCtx, BuildCoreContext(), iGen);
            await service.CreateAsync(customerId);
            await webCtx.SaveChangesAsync();
        }

        //Assert
        using (var ctx = BuildWebContext())
        {
            ctx.ServiceRequests.Should().HaveCount(1);
            ctx.ServiceRequests.FirstOrDefault().Should().NotBeNull();                
        }
    }
}

Answer 1

To answer OP, I believe the problem is multiple contexts accessing the database, which doesn't work without a shared cache. Change your connection string to: "DataSource=file::memory:?cache=shared".

For those who wander here from Google, here are some other things to keep in mind:

• At least one connection has to stay open to the database.
• If the database will be accessed from multiple connections, you must use a shared cache as follows: string connectionString = "DataSource=file::memory:?cache=shared";
• If using ADO.NET, the official provider does not work properly with Entity Framework. I ran into "no such table" errors while testing even though I had everything configured correctly, but once I changed from: System.Data.SQLite.SQLiteConnection(System.Data.SQLite.Core nuget package) to the Microsoft provider: Microsoft.Data.Sqlite.SqliteConnection then the errors disappeared.
• The official Sqlite ADO.NET adapter is 2x times faster than the Microsoft ones! So unless you absolutely have to, it's better to use the official Sqlite adapter instead.

Example code:

[Fact]
public async Task MyTest()
{
    var dbContext = new MyDbContext("DataSource=file:memdb1?mode=memory&cache=shared");

    try
    {
        await dbContext.Database.OpenConnectionAsync();
        // Test your dbContext methods, which has an open connection to the in-memory database
        // If using ADO.NET to make new connections, use Microsoft.Data.Sqlite.SqliteConnection
    }
    finally
    {
        await dbContext.Database.CloseConnectionAsync();
    }
}

Also remember to read the documentation! :)

UPDATE: To make testing easier with the in-memory database, I created a TestContext that inherits from my DbContext in order to automatically handle the connections and db creation: (using NUnit this time)

public sealed class TestContext : UsersContext
{
    public TestContext(string connectionString) : base(connectionString)
    {
        Database.OpenConnection();
        Database.EnsureCreated();
    }

    public override void Dispose()
    {
        Database.CloseConnection();
        base.Dispose();
    }
}

Usage:

[Test]
public void AddingUsersShouldIncrementId()
{
    using (var dbContext = new TestContext("DataSource=file::memory:?cache=shared"))
    {
        dbContext.UsersDbSet.Add(new UserEntity());
        dbContext.SaveChanges();

        uint actualId = dbContext.Users.Single().Id;
        Assert.AreEqual(1, actualId);
    }
}

Answer 2

The SQLite In-memory database only exists while the connection is open. You can use different DbContext, but if the connection is closed, the memory db will be deleted.

Example