Entity Framework 4 and SQL Compact 4: How to generate database?

Question

I am developing an app with Entity Framework 4 and SQL Compact 4, using a Model First approach. I have created my EDM, and now I want to generate a SQL Compact 4.0 database to act as a data store for the model. I bring up the Generate Database Wizard and click the New Connection button to create a connection for the generated file. The Choose Data Source dialog appears, but SQL Compact 4.0 is not listed in the list of available data sources:

I am running VS 2010 SP1 (beta) and I have installed the VS 2010 Tools for SQL Compact 4.0. I can create a SQL Compact 4.0 data connection from the Server Explorer. It is only in the Generate Database Wizard that the 4.0 option doesn't appear. BTW, my SQL Compact 4.0 installation does include System.Data.SqlServerCe.Entity.dll. So I should have the SQL Compact components I need.

Am I doing something incorrectly, or is this a bug? Does anyone have a fix or a workaround? Thanks for your help.

Answer 1

As a workaround, I am using the Generate Database Wizard to generate a a SQL Compact 3.5 script, which I am running against a blank SQL Compact 4.0 database created in the VS 2010 Solution Explorer. Here are the steps to accomplish that task:

Step 1: Run the Generate Database Wizard. It prompts for the creation of a file on its first page, so have it create a SQL Compact 3.5 file. This file is a dummy, so it doesn't matter what you name it.

Step 2: Complete the wizard to create the DDL script that will configure the new database.

Step 3: Delete the dummy file created in Step 1.

Step 4: Use VS 2010's Server Explorer to create the actual SQL Compact 4.0 file that you will use in your project.

Step 5: The Generate Database Wizard places two references to SQL Compact "3.5" in the EDMX file; these references need to be changed to "4.0". Open the EDMX file in VS 2010's XML Editor (right-click and select 'Open With' to get to the XML Editor). Line 7 should contain the "3.5" references. Change these to "4.0" and save the file.

Step 6: Open the generated EDMX.SQLCE script file in VS 2010. VS will indicate (via a banner across the botton of the work pane) that the script is disconnected. Right-click on the white space above the banner and select Connection > Connect in the context menu that appears. That brings up a connection dialog--use it to connect the script to the SQL Compact 4.0 database that you created in Step 4.

Step 7: Right-click in the script white-space again and select Execute SQL from the context menu. The script will execute and you will get the usual results message in a pane below the script. Assuming successful execution, the database is then configured to match the Entity Data Model.

Step 8: We also need to ensure the App.config/web.config is not pointing to the dummy file. If it is change it to the actual CE 4.0 file created in Step 4 above. Else we have trouble with regeneration of the script when we make change to the model. In the config file change 3.5 to 4.0 as well.

At that point, you should be able to use Entity Framework 4 to work with your database.

Answer 2

To answer the comments below and address them according to: http://blogs.msdn.com/b/sqlservercompact/archive/2011/01/12/microsoft-sql-server-compact-4-0-is-available-for-download.aspx

These wizards to not work.

Designers in the VB or C# Windows projects in Visual Studio 2010 SP1 Beta: The following wizards do not work with Compact 4.0 in the Windows project system. Developers can manually add reference to the ADO.NET provider for Compact 4.0 (System.Data.SqlServerCe) to develop programs for Compact 4.0 in the Windows projects:

1. The Data Source Configuration Wizard that is used to configure datasets.
2. The Configure Data Configuration wizard that is used to setup the syncing of data and schema with SQL Server using Sync FX.
3. The Entity Data Model wizard that is used to generate entities from a Compact database.

Here are the accepted work arounds for these issues. http://erikej.blogspot.com/2010/11/using-entity-framework-with-sql-server.html