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

0
votes

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:

enter image description here

enter image description here

enter image description here

3
c#entity-framework
If you also have SQL Server Express installed, look for a database named after your connection string name. - Crowcoder
Ok may have found root cause... My system had both SQL 2014 and 2012 installed. When I removed 2012 now my project won't write data. I deleted the MDF files and reran it to try to get it to use 2014, no dice. Next step will be to remove SQL 2014 re-install 2012... will post back results... This tells me that either EF or Visual Studio is not univerally using the same version of SQL server to do the creation and connection/management stuff. - JWP
Have you verified that you Visual Studio's SqlServer database tools are up to date? Check for extension updates. Also, you said EF shows the version to still be 4.0? The Runtime version, or actual version? - Leniency
Ok new update, I created a brand new MVC code first project, compiled and ran it after installing SQL Express 2012. It ran fine, creating the MDF file and showing the content added by the application. It add the default connection string as follows: <add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\aspnet-WebApplication3-20150128060349.mdf;Initial Catalog=aspnet-WebApplication3-20150128060349;Integrated Security=True" But now the MDF file is not in the App_Data directory....it's in the SQLExpress instance of the DB... - JWP
Before I removed SQL 2012 and 2014 everything used LocalDB after I reinstalled SQL 2012 Express, VS will only use SQL Express now.... Is there anyway to tell VS to use local? - JWP

3 Answers

1
votes

(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.

0
votes

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.

0
votes

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!