Overview

The recommended way to create SQL databases from Entity Framework models is to use a dedicated database server. However, in some instances you may want to create the database in an .mdf file inside the application’s App_Data directory. While this is definitely possible, the solution is certainly not straightforward and not entirely possible from within Visual Studio alone. One needs to connect to an existing SQL Server database instance in SQL Server Management Studio and manually attach the .mdf database file to that instance.

Prerequisites

This article assumes that following software is installed on the computer:

• Visual Studio 2010
• SQL Server 2008 or SQL Server 2008 R2 (Full or Express) with at least one configured database instance
• SQL Server Management Studio (comes with SQL Server 2008 and SQL Server 2008 Express With Tools, or get it here)

Attaching The Database

In order to attach the .mdf database file to an existing SQL Server instance, perform the following steps:

• Close the connection to the .mdf database file in Visual Studio
• Launch Microsoft SQL Server Management Studio as an Administrator
• In the Object Explorer right-click the Databases node and click Attach…
• Click the Add… button and select the .mdf database file to attach, then click OK to complete the attachment
• Finally, rename the attached database to the name expected by the entity model.

By default, the attached database’s name is the full file path to its location. The actual name expected by the entity model can be found near the top of the generated .edmx.sql file in Visual Studio. In the following example, the database should be renamed to Database:

SET QUOTED_IDENTIFIER OFF;
GO
USE [Database];
GO

If the target directory or file for the .mdf database is not selectable, Management Studio may not have access permissions. Please note that file and folder access in SQL Server Management Studio is not determined by the user account running the program, but by the user account the SQL Server instance runs under. By default this is the Network Service account. One way to allow access to the database file is to give the Network Service user account access to the file itself and to all parent directories.

If there are a lot of parent directories that need to be changed, for example if the database is stored in the user’s Documents and Settings folder, it may be easier to just modify the account SQL Server is running under, instead of updating the permissions for tens of thousands of directories and files. The user account for the SQL Server instance can be changed in the SQL Server Configuration Manager. Ideally, this would be set to an account that already has access to the target location, such as an Administrator account.

Connecting the Transact-SQL Editor

Now that the database file is attached, the Transact-SQL Editor in Visual Studio can be connected:

1. In Visual Studio, open the .edmx.sql that was generated for the .edmx entity model
2. Right-click into the text editor and select Connection → Connect from the context menu
3. Browse to the database instance hosting the .mdf database file and click Connect.

If everything completed successfully, the text editor should say Connected. at the bottom left.

Generating the Database

The SQL script generated by Visual Studio for the entity model can now be executed against the database:

1. Right-click the text editor and click Execute SQL from the context menu.

The query should execute successfully. In SQL Management Studio, verify that the tables, views and and/or stored procedures were created by clicking the Refresh button.

Detaching the Database

While the database file is attached to the SQL Server instance it cannot be used in Visual Studio for development and testing. This is probably the biggest disadvantage of using local .mdf files as targets for entity models. In order to become available in Visual Studio again, the database file needs to be detached from the server in Management Studio:

1. Right-click the database and select Tasks… → Detach… from the context menu.

The database connection to the .mdf file can now be reestablished in Visual Studio.

Related Resources

• MSDN: SQL Server 2008 R2 – Securing Data and Log Files