Why table not exists while I create a in-memory sqlite database with entityframework core?

Question

I want to create an in-memory SQLite database.

Here is startup.cs:

 public void ConfigureServices(IServiceCollection services)
        {
            services.AddControllersWithViews();            
            services.AddDbContext<TestDBContext>().AddEntityFrameworkSqlite();
        }

Here is the Model of database:

 public class TestModel
    {
        public string UserName { get; set; }        
        [Key]
        public string id { get; set; }
    }

Here is the DBContext of database:

 public class TestDBContext : DbContext
    {
        public virtual DbSet<TestModel> Test { get; set; }
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlite("Data Source=:memory:");
        }
    }

And here is the controller:

private readonly TestDBContext TestDBContext;

 public HomeController(ILogger<HomeController> logger,TestDBContext _TestDBContext)
        {
            _logger = logger;
            this.TestDBContext = _TestDBContext;
        }

    public IActionResult Index()
    {
        TestDBContext.Database.EnsureCreated();            
        TestDBContext.SaveChanges();
        TestDBContext.Test.Add(new TestModel() { User = DateTime.Now.ToString(),id=Guid.NewGuid().ToString() });
        TestDBContext.SaveChanges();
        return View(TestDBContext.Test.ToList());
    }

Every time it runs, it will report an error:

Inner Exception 1:
SqliteException: SQLite Error 1: 'no such table: Test'.

I have used the EnsureCreated and the EnsureCreated runs without any error. Why it still be like this?

Answer 1

EF Core's DbContext always opens and closes connections to the database automatically, unless you pass an already open connection. And when the connection gets closed, the Sqlite In-memory database will be removed. So I modified your code a little bit like this.

public void ConfigureServices(IServiceCollection services)
{
    var connection = new SqliteConnection("datasource=:memory:");
    connection.Open();

    services.AddControllersWithViews();
    services.AddDbContext<TestDBContext>(options =>
    {
        options.UseSqlite(connection);
    });
}

And the Database Context class - I added the constructors so that I can provide the parameters.

public class TestDBContext : DbContext
{
    public TestDBContext(DbContextOptions options) : base(options)
    {
    }

    protected TestDBContext()
    {
    }

    public virtual DbSet<TestModel> Test { get; set; }
}

And instead of creating the database in the Index action method, create it in the startup.