No such table - EF Core with Sqlite in memory

Question

No such table - EF Core with Sqlite in memory

c# entity-framework-core

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();                
        }
    }
}

bielu000

View more on Stack Overflow

Shahin Dohan · Accepted Answer · 3/13/2020 11:20:53 AM

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! :)

No such table - EF Core with Sqlite in memory

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related