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?

Are you restarting the application after deleting the database? — StriplingWarrior
– StriplingWarrior, Commented Nov 23, 2011 at 22:29
@StriplingWarrior: Yes, I am. I get the error upon starting the app again. — Eric J.
– Eric J., Commented Nov 24, 2011 at 4:14

dnatoli · Accepted Answer · 2011-11-24 00:01:50Z

6

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.

edited Nov 24, 2011 at 0:01

answered Nov 23, 2011 at 22:36

dnatoli

7,01211 gold badges62 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Eric J. Over a year ago

Why am I getting the "permission denied" error when I delete the .mdf file? Where is the EdmMetadata table if not in the deleted .mdf file?

dnatoli Over a year ago

I have updated my answer on how to delete the EdmMetadata table. I highly recommend you watch the video I linked to, it tells you everything you need to know. You could be getting a "permission denied" error for quite a few reasons, one being that the webserver is still running and accessing that database.

dnatoli Over a year ago

And yes, I would have assumed that if you deleted the .mdf file then the database itself should have been removed, including the EdmMetadata table. How are you deleting the database? Through an SQL Express manager?

dnatoli Over a year ago

On further inspection I see you are just deleting the files and not the database itself. By deleting the .mdf and .ldf files, you are only removing the data and logs files. Information regarding the database and its file locations are stored in the sql server master database so you need to remove it from there as well through either the database explorer or sql server/express management studio.

Eric J. Over a year ago

I don't see any mention of the "Journal" database in SQL Server Management Studio. I looked under the Databases node of Object Explorer and in sys.Databases.

|

Collectives™ on Stack Overflow

Entity Framework Code First and Connection Strings

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related