How does one Attach to an MDF file created by Code First using Server Explorer

Question

Given: Code First Technique (all properly done and validated). I can actually, in the application see records added from the program. This means all the SaveChanges and subsequent queries are working.

Problem: I want to expose this DB to another program but cannot get Server Explorer Data Connections to attach to it...

What did you try?

1. In VS2013 I opened the Server Explorer, clicked on Add Connetion and entered this (after changing the DataSource to SQLClient and not the default database file setting) : (LocalDb)\V11.0 and then clicked on "Selected or enter a database name" The tables shown did not show the new database table.

2. I then tried to type in the fully qualified name of MYProject.MYDBContent and that didn't work.

3. I then switched to the checkbox of "Attach a database file" and found the file that was created. (Yes!) But when attempting to open it says It cannot be opened because it is version 782, this server supports version 706 and earlier. Later on, the message says also "A database with the same name exists, or specified file cannot be opened, or it is located on a UNC Share."

Environment

I'm using VS2013 and used NUGET to download EF 6.0.2, but when I look at EF in the references area is tells me it's still Version 4.0....

My system has both SQL Server 2012 and 2014 (Local DB) installed. This must be root cause of issue.

Thoughts?

Visual Studio 2013, EF 6.0.1 and SQL Express 2012 do not work well together when using Code First. The problem is the MDF file is generated but any attempt to connect using the Server Explorer Data Connections fails with the 782 error message above as well as in SSMS for SQL 2012. Even if your system does not have SQL 2014 installed the MDF file version thinks it is a 2014 file version!

If you delete an MDF file (as I did and subsequent) attempts to create MDF fail (Cannot Attach File error), then simply rename the DBContext class name and you will get a new MDF file!

Environment:

Answer 1

Visual Studio 2013, EF 6.0.1 and SQL Express 2012 do not work well together when using Code First. The problem is the MDF file is generated but any attempt to connect using the Server Explorer Data Connections fails with the 782 error message above as well as in SSMS for SQL 2012. Even if your system does not have SQL 2014 installed the MDF file version thinks it is a 2014 file version!

Answer: If you delete an MDF file (as I did) and subsequent attempts to create MDF fail with(Cannot Attach File error), then simply rename the DBContext class name and you will get a new MDF file!

Answer 2

(LocalDb)\V11.0 and then clicked on "Selected or enter a database name" The tables shown did not show the new database table.

You are able to connect successfully using this method because it uses your installed 2014 SQL Express LocalDB. Why some tables were not showing up probaly has to do with how/when Code First decides to deploy updates. I probably would have stuck with connecting via localdb and focus on investigating that issue.

Attaching an MDF from Server Explorer uses an instance of SQL Server. For which you do not have a 2014 instance installed. The 2014 LocalDB is not sufficient for this. See this question that highlights the fact that attaching MDF's through Server Explorer requires an instance of SQL Express: How to add SQL Server database file (.mdf) in Visual Studio without installing SQL Server Express Edition?

The default installation you have supports connecting via localdb. So it does "work fine together" so to speak if you are connecting via localdb. If you want to connect to the MDF file, you will need to install a server instance of 2014 SQL Express.

However, if I were in your shoes I'd stick with connecting via localdb, and explore why "The tables shown did not show the new database table."

If you are planning to share the database however, then as Erik points out, localdb will not work. You will need to point your connection at an instance of SQL Server and let code first and/or code first migrations create the database for you in the instance. Then instead of connecting via localdb or MDF, you will instead connect to the Server instance.

If you point the connection string at an SQL Server instance, instead of using the MDF or localdb syntax, then Code First should create the database in whatever instance of SQL Server you point it to. This means whatever version you point it at will be the version it creates. I.e. if your connection string points at an instance of SQL Server 2012 Developer Edition, then the database will be a 2012 version database created in that instance.

Answer 3

Problem: I want to expose this DB to another program ...

The best way I can describe LocalDb is as an in memory version of an instance of a Sql Server (probably something like Sql Express). That means that any application used LocalDb to connect to the Mdf/Ldf will have exclusive access to that file can it cannot be used by another instance.

If you need to share data across multiple applications, that is what Sql Server Express and above are for.

I'm using VS2013 and used NUGET to download EF 6.0.2, but when I look at EF in the references area is tells me it's still Version 4.0....

I'm not sure if the image is the current version you were looking at when you wrote this, but the image shows version 6.0 running designed for a .Net runtime of 4.0. That looks correct to me.

Visual Studio 2013, EF 6.0.1 and SQL Express 2012 do not work well together when using Code First.

I have no idea why you would state this. I've written 3 projects already without an issue. I feel like you don't understand that LocalDb and Sql Server Express are completely two independent products.

If you are using LocalDb with AttachDbFilename then when you're application is finished running the database is dropped from the instance.

Excerpt:

Use the AttachDBFilename connection string keyword to add a database to your LocalDB instance. When using AttachDBFilename, if you do not specify the name of the database with the Database connection string keyword, the database will be removed from the LocalDB instance when the application closes.

So...

If you delete an MDF file (as I did and subsequent) attempts to create MDF fail (Cannot Attach File error).

This is because the connection is cached while Visual Studio is open. Renaming your DbContext creates a new connection and creates a new cached version.

I want to expose this DB to another program but cannot get Server Explorer Data Connections to attach to it..

Only a single instance of any version Sql Server (LocalDb, Express, Standard, etc) can open a database. If you want to share data between application you have to use Express or above, you cannot use LocalDb.