EF Code First: How to delete and recreate database file.

[eric_hewitt]

As part of unit tests, I would like to delete the SQLite data file, and have it recreated. I'm using EF Code First, with DotConnect for SQLite. I'm using an SQLiteConnectionFactory like the one you find here: http://www.devart.com/blogs/dotconnect/ ... qlite.html

I'm using the DropCreateDatabaseIfModelChanges initializer for the database.

Some simple example code looks like this:

Code: Select all

            using (var context = new MyDbContext())
            {
                var item = context.Items.Create();
                item.Name = "Yo";
                context.Items.Add(item);
                context.SaveChanges();
            }
            SQLiteConnection.ClearAllPools(true);
            File.Delete("MyDbContext.db");
            using (var context = new MyDbContext())
            {
                var item = context.Items.Create();
                item.Name = "Yo";
                context.Items.Add(item);
                context.SaveChanges();
            }

I clear all pools so that the database file lock will be released. I then delete the database file, and start over. Unfortunately, I get an exception on the second "context.SaveChanges()":

System.Data.Entity.Infrastructure.DbUpdateException: An error occurred while updating the entries. See the inner exception for details.
Devart.Data.SQLite.SQLiteException: SQLite error\r\nno such table: Items

It correctly recreates the database file, but adds no schema. Why?

Thanks for your help!

Eric

[Shalex]

Creating a database file and creating tables in the database are two different things:
1. This is SQLiteConnection which is responsible for create/delete a db file, which is created when connection string contains "FailIfMissing=False;". This is an ADO.NET level.
2. Tables in an existing database are created basing on a particular database initialization stagery, which is used by Database.Exists(), Database.Delete(), Database.Create() methods. This is an Entity Framework level. The initialization strategy is called the first time a context is used in the application domain. This is an optimization at the Entity Framework level so that resource-demanding database initialization strategy was not called for the future instances of the same DbContext.

We recommend you to try saving database initialization strategy instance and then use it explicitly:

Code: Select all

            var initializator = new DropCreateDatabaseIfModelChanges();
            Database.SetInitializer(initializator);

            // ...

            SQLiteConnection.ClearAllPools(true); 
            File.Delete("MyDbContext.db"); 
            using (var context = new MyDbContext()) 
            { 
                initializator.InitializeDatabase(context);
                var item = context.Items.Create(); 

Or use the Database class methods explicitly which will give a better performance in your case:

Code: Select all

            File.Delete("MyDbContext.db"); 
            using (var context = new MyDbContext()) 
            { 
                context.Database.Create();
                var item = context.Items.Create(); 

[eric_hewitt]

I figured it had something to do with EF optimizations. Your solution worked great. Thanks!