Properly Deleting Database for Database-Migration

I am working on a simple brand new project with ASP.NET MVC and decided to try EF to connect to my database. This gives me the opportunity to learn EF Code-First, database-migration, etc.

Everything seems to be pretty intuitive until when I am running "update-database" from the Package Manager Console. I created my Configuration class, turn-on automatic migration, and populated my database using Seed method, made sure all my context are correct.

[TL;DR]

When one need to delete/recreate a database, do not delete the mdf file from App_Data, but instead go to "SQL Server Object Explorer" and find your database under (localdb) and delete it from there.

FULL VERSION:

Then, since I want to recreate my database, I delete my database (mdf file) from my App_Data folder under Solution Explorer and then run "update-database". See picture on the left.

But then I am getting an error:

Cannot attach the file D:\Projects\MvcApplication1\App_Data\aspnet-MvcApplication1-20130407085115.mdf' as database 'MvcApplication1'.

I looked in the file explorer and the mdf file is surely gone. Try to close Visual Studio and reopen, same error.

Well, the database was initially created when I try to "Register" or create an account using the site (it's using SimpleMembershipProvider) - so maybe it will recreate it if I simply run the site and try to register again. But then I am getting the same error when running the website.

I went to the recycle bin, restore the mdf file and ran the project again - it worked. It did not have the new tables or new data, but no "cannot attach" error. Restoring this file also restore my default connection. If I try to delete the mdf file again, then my project won't run and my database-migration also won't run.

I almost resort to think that "Code-First" is a lie - that I simply have to add the new tables manually in the SQL table designer, etc. This is so confusing - should not be that hard, I think.

So with the mdf file deleted, I went to Server Explorer and checked that my connection to the database is gone - there is nothing under "Data Connections".

So maybe I need to delete the data connection instead of deleting the mdf file from App_Data? So I did a restore again from my Recycle Bin, made sure my project ran, and then I deleted my connection from the Server Explorer, rebuilt the project and ran it. It worked! But my delight is short-lived, since then I realized that deleting the connection does not necessarily mean deleting the database - which I quickly checked that my mdf file still in the App_Data folder. I simply deleted the connection to view the database via Visual Studio.

I tried more and more things - which increase the frustration level - because at this point I am stuck in my project and all I have been trying to do is to modify my database schema. If I did this using the old way using SQL Management Studio or SQL Express, or even Linq-to-SQL, I could have been making a huge progress in my project.

SOLUTION

Until I stumbled on "SQL Server Object Explorer" under "VIEW" in your VS Studio 2012 top menu/toolbar. I noticed that although my mdf file is deleted from my App_Data folder, but under "SQL Server Object Explorer", my database is still registered under (localdb)\v11.0\Databases.

Out of curiosity, I deleted my database from there and re-ran my project - it worked. It recreated my database (without the new tables and seed data). So I deleted it again from SQL Object Explorer, and ran "update-database" - it ran successfully this time. EUREKA!!

So lesson learned: when one need to delete/recreate a database, do not delete the mdf file from App_Data, but instead go to "SQL Server Object Explorer" and find your database under (localdb) and delete it from there.