Efcore: no such table ?

[grahamehorner]

Running ASPNETCORE with EFCore using SQLite; a datacontext is loaded which has an in memory connection string in side of a controller method, the tables in the datacontext look to have been created;
querying the datacontext set produces the following stack trace with no exception.

```
Exception message:
No exception is raised

Stack trace:
Hosting environment: Development
Content root path: C:\DEV\MorrisonDataServices\MorrisonDataServices.USS.SupplierWebPortal
Now listening on: http://localhost:3283
Application started. Press Ctrl+C to shut down.
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:3283/
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method MorrisonDataServices.USS.SupplierWebPortal.ViewControllers.HomeController.Index (MorrisonDataServices.USS.SupplierWebPortal) with arguments ((null)) - ModelState is Valid
info: Microsoft.AspNetCore.Mvc.ViewFeatures.Internal.ViewResultExecutor[1]
Executing ViewResult, running view at path /Views/Home/Index.cshtml.
info: Microsoft.Extensions.DependencyInjection.DataProtectionServices[0]
User profile is available. Using 'C:\Users\grahame.horner\AppData\Local\ASP.NET\DataProtection-Keys' as key repository and Windows DPAPI to encrypt keys at rest.
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[2]
Executed action MorrisonDataServices.USS.SupplierWebPortal.ViewControllers.HomeController.Index (MorrisonDataServices.USS.SupplierWebPortal) in 2623.2011ms
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[2]
Request finished in 3224.54ms 200 text/html; charset=utf-8
info: Microsoft.AspNetCore.Hosting.Internal.WebHost[1]
Request starting HTTP/1.1 GET http://localhost:3283/api/v0.1/demo/sse/gas/consumer
info: Microsoft.AspNetCore.Mvc.Internal.ControllerActionInvoker[1]
Executing action method MorrisonDataServices.USS.SupplierWebPortal.Controller.demoController.consumer (MorrisonDataServices.USS.SupplierWebPortal) with arguments ((null)) - ModelState is Valid
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT COUNT(*) FROM "sqlite_master" WHERE "type" = 'table' AND "rootpage" IS NOT NULL;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
CREATE TABLE "Consumer" (
"ID" BLOB NOT NULL CONSTRAINT "PK_Consumer" PRIMARY KEY,
"Forename" TEXT,
"Middlenames" TEXT,
"Surname" TEXT,
"Title" TEXT
);
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
PRAGMA foreign_keys=ON;
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "c"."ID", "c"."Forename", "c"."Middlenames", "c"."Surname", "c"."Title"
FROM "Consumer" AS "c"
fail: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[1]
An exception occurred in the database while iterating the results of a query.
Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such table: Consumer'.
at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__31.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__152.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext() Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such table: Consumer'. at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer) at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__31.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__152.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()
info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
Executed DbCommand (0ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
SELECT "c"."ID", "c"."Forename", "c"."Middlenames", "c"."Surname", "c"."Title"
FROM "Consumer" AS "c"
fail: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[1]
An exception occurred in the database while iterating the results of a query.
Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such table: Consumer'.
at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db)
at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues)
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer)
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__31.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__152.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext() Microsoft.Data.Sqlite.SqliteException: SQLite Error 1: 'no such table: Consumer'. at Microsoft.Data.Sqlite.Interop.MarshalEx.ThrowExceptionForRC(Int32 rc, Sqlite3Handle db) at Microsoft.Data.Sqlite.SqliteCommand.ExecuteReader(CommandBehavior behavior) at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary2 parameterValues, Boolean closeConnection)
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary2 parameterValues) at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.BufferlessMoveNext(Boolean buffer) at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__31.MoveNext()
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__152.MoveNext() at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor1.EnumeratorExceptionInterceptor.MoveNext()

[smitpatel]

From SQLite in-memory documentation.

The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

You need to open the connection to in-memory db and pass it to EF while configuring the model so that connection remains open always, if you don't do it that way and instead give connection string to EF then every time EF opens connection it will get different db and there won't be tables you created using different connection string.

See https://docs.microsoft.com/en-us/ef/core/miscellaneous/testing/sqlite#writing-tests
It has more info on how to create connection and pass it to EF through DbContextOptions when using SQLite in-memory db.