sql server 2008 - Forcing code-first to always initialize a non-existent database?

Question

Sometimes, I'll delete my development database and run my EF code-first application. I'll get the error:

Cannot open database "AssessmentSystem" requested by the login. The login failed. Login failed for user 'AssessmentAdmin'.

I think this is because the DbContext only runs the DB initialization logic "once per AppDomain when the context is used for the first time", as this page says. This means I need to recycle the IIS application pool to get EF to recreate my database if I drop it.

Is there any way I can get the DB initialization code to run every time I try to access the database? So, it will always check to see whether the DB exists and if not, create it instead of trying to open it, even if it's using the same AppDomain that previously accessed the database that I have now dropped?

Note that I would like this initializtion check to be done on every query, so even having it done in Application_Start isn't often enough; ideally, I'd like to be able to load some DB data, delete the DB, then load some DB data and it would recreate the DB without my even having to restart the application (basically I would just have to reload the web page that loads some DB data).

Answer 1

Initializer is executed when you need to access the database so if you want to create database on app start use anything of the following:

    context.Database.Initialize(true); //If set to true the initializer is run even if it has already been run.       
    context.Database.Create()

http://msdn.microsoft.com/en-us/library/system.data.entity.database.initialize(v=vs.103).aspx

CreateDatabaseIfNotExists An implementation of IDatabaseInitializer that will recreate and optionally re-seed the database with data only if the database does not exist. To seed the database, create a derived class and override the Seed method.

Database.SetInitializer<MyContext>(new CreateDatabaseIfNotExists<MyContext>());

http://msdn.microsoft.com/en-us/library/gg679221(v=vs.103).aspx

DropCreateDatabaseIfModelChanges An implementation of IDatabaseInitializerthat will delete, recreate, and optionally re-seed the database with data only if the model has changed since the database was created. This is achieved by writing a hash of the store model to the database when it is created and then comparing that hash with one generated from the current model. To seed the database, create a derived class and override the Seed method.

The initialization strategy can optionally check for database existence, create a new database, and seed the database with data. The default strategy is an instance of CreateDatabaseIfNotExists.

Database.SetInitializer(new DropCreateDatabaseIfModelChanges());

Note that this assumes you have permission to even drop your database.

http://msdn.microsoft.com/en-us/library/gg679604(v=vs.103).aspx

DropCreateDatabaseAlways

An implementation of IDatabaseInitializer that will always recreate and optionally re-seed the database with data the first time that a context is used in the application domain. To seed the database, create a derived class and override the Seed method.

Database.SetInitializer<MyContext>(new DropCreateDatabaseAlways<MyContext>());

http://msdn.microsoft.com/en-us/library/gg679506(v=vs.103).aspx

I recommend that you look at Migrations if you want to track, revert the changes you made to your DB to the previous state http://msdn.microsoft.com/hr-hr/data/jj591621 .

UPDATE

context.Database.Initialize(true);

If the parameter force is set to true, then the initializer is run regardless of whether or not it has been run before. This can be useful if a database is deleted while an app is running and needs to be reinitialized.

For MVC application add a section to the Application_Start() method in the Global.asax

protected void Application_Start() {

     Database.SetInitializer<MyContext>(new DropCreateDatabaseAlways<MyContext>()); 

     // Forces initialization of database on model changes.
     using (var context= new MyContext()) {
          context.Database.Initialize(force: true);
     }    
}

Also you can use a custom initializer :

public class MyDbInit : DropCreateDatabaseAlways<MyContext>
{

}

and then use

Database.SetInitializer(new MyDbInit());

UPDATE 2

Make a new empty MVC4 application called DeleteDBOnEveryRequest. Put the following in the Global.asax Application_start

protected void Application_Start()
        {
            AreaRegistration.RegisterAllAreas();

            WebApiConfig.Register(GlobalConfiguration.Configuration);
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);

            Database.SetInitializer<BlogContext>(new DropCreateDatabaseAlways<BlogContext>());    

            using (var context = new BlogContext())
            {
                context.Database.Initialize(force: true);
            }    
        }

Make a new controller called DatabaseController with two actions.

In the Access action you delete the DB and redirect to Recreated action from where you create a DB as it was previousy deleted.

namespace DeleteDBOnEveryRequest.Controllers
{
    public class DatabaseController : Controller
    {
        public ActionResult Access()
        {
            using (var context = new BlogContext())
            {
                context.Database.Delete();
            } 
            return RedirectToAction("Recreated");
        }

        public ActionResult Recreated()
        {
            using (var context = new BlogContext())
            {
                context.Database.Initialize(force: true);                
            }
            return View();
        }
    }
}

Is this what you wanted?