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?

Anuraj · Accepted Answer · 2020-01-30 14:46:53Z

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.

MidKnight · Accepted Answer · 2023-03-14 06:49:53Z

For the people having problems as I do and that found this post in 2023:

ASP.NET Core / .NET 7

public class ControllerTests
{
    private DbContext _context;

    public ControllerTests()
    {
        var connection = new SqliteConnection("DataSource=:memory:");

        var options = new DbContextOptionsBuilder<DbContext>()
            .UseSqlite(connection).Options;

        _context = new DbContext(options);

        _context.Database.OpenConnection();
        _context.Database.EnsureCreated();

        _context.Database.Migrate();
    }
}

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

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
    }

    public DbSet<UserModel> Users { get; set; }
}

Jas · Accepted Answer · 2020-01-30 16:45:05Z

1

Also, opt to use the DbContext.Database.Migrate() method instead of EnsureCreated else you won't be able to use migrations later down the line.

answered Jan 30, 2020 at 16:45

Jas

112 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

ASP.NET Core / .NET 7

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

ASP.NET Core / .NET 7

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related