Entity Framework Code First and Connection Strings

Question

I have a small MVC 3 app using Entity Framework Code First and use this connection string for the model:

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Journal.mdf;User Instance=true;Database=MyJournal

When I make a change to the model (e.g. add a property), I get as expected

The model backing the 'JournalContext' context has changed since the database was created.

So, being in development mode, I go ahead and delete Journal.mdf and Journal.ldf.

Now when I run the application again, I get

Cannot open database "MyJournal" requested by the login. The login failed.

If I change my connection string to

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|Journal.mdf;User Instance=true;Database=MyJournal2

(changed the Database= parameter by appending '2')

Journal.mdf is created and the app works again. If I make a number of changes and attempt to "recycle" any Database name again, I get the "Cannot open" error.

Why do I need to provide a unique Database name each time I change the model, and how can I "clean out" previous names?

Answer 1

You don't need a unique database name each time. When a model is first created, it runs a DatabaseInitializer to do things like create the database if it's not there or add seed data. The default DatabaseInitializer tries to compare the database schema needed to use the model with a hash of the schema stored in an EdmMetadata table that is created with a database (when Code First is the one creating the database). If the hash comparison is different then it throws that error.

Obviously if you change the connection string then its going to create an entirely new database called 'MyJournal2'.

Ways to get around this are to delete the EdmMetadata table and run the initializer again. You can do this by going into the Database Explorer window in Visual Studio and connecting to your database, then going to Tables where you should find the EdmMetadata table, right-clicking on it and selecting Delete.

Alternatively put

DbDatabase.SetInitializer(new DropCreateDatabaseIfModelChanges<dbType>());

in your Application_Start method in Global.asax.cs. This will delete the database and recreate it whenever the schema changes.

See this video on pluralsight for more details, especially the section 'When classes change'.

Also check this link for DropCreateDatabaseIfModelChanges. It tells you about what actually happens and how to seed the database if you need to by creating a derived class.