C# – SQL Server Compact Edition with Entity Framework

centity-frameworksqlsql-server-ce

I try to use SQL Server Compact Edition with Entity Framework in Visual Studio 2008 SP1. Here's what I do:

1) I create a new project, of type Console Application.

2) I right-click on the project, select Add->New Item.

3) I choose to add a Local Database called Something.sdf

4) In the next page of the "Add New Item" wizard, I choose the default dataset name (SomethingDataSet)

Now, in the Server Explorer, the new database Something.sdf is shown under Data Connections.
5) I right click on the project again, and select Add->New Item.

6) I choose to add a ADO.NET Entity Data Model with the default name, Model1.edmx

7) In the next page of the wizard, I choose to generate model from database.

Now it asks me "Which data connection should your application use to connect to the database?". In this dialog, I can choose between using a pre-created Data connection, or to create a new connection.

The drop down with pre-created data connections is empty (I would have expected to see my Something.sdf here?). When I press New Connection, I can only choose among Microsoft SQL Server, Microsoft SQL Server Database File, and – I can't choose Microsoft SQL Server CE. If i choose Other, the data source ".NET Framework Data Provider for SQL Server" is shown, which is not what I want.

What am I missing here? Why can't I select my SQL Server CE database in the Add Entity Data Model Wizard?

Best Answer

Not sure if this is the same problem, but I think had to download SQL Server Management Studio Express 2008 to get CE as an option in the dropdown. I'll look more into it though and give you a better response.

Edit:

If you browse to: C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG\machine.config

you should find the following node:

<configuration>
    <system.data>
        <DbProviderFactories>
            <add name="Microsoft SQL Server Compact Data Provider" invariant="System.Data.SqlServerCe.3.5" description=".NET Framework Data Provider for Microsoft SQL Server Compact" type="System.Data.SqlServerCe.SqlCeProviderFactory, System.Data.SqlServerCe, Version=3.5.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91"/>

Let me know if you do not find it.

Edit 2:

I've been running VS 2010 beta 1, and the process you listed allows me to select SQL CE. However, I fired up 2008 and get the same result as you, SQL CE is no longer an option for a datasource.

I first started my project in 2008 a while back, and I had to install "Microsoft SQL Server Compact 3.5 SP1 Beta for Entity Framework Beta 3", as noted in the following link:

http://technet.microsoft.com/en-us/library/cc835494.aspx

However, the download does not seem to be available any longer. I couldn't find it anywhere and the link they provide in the article is broken.

After upgrading to 2010 I ran into the same problem with CE not being an option, so I tried re-installing VS 2008 SP1, and eventually found out that all I had to do was add the node that I mentioned above to the machine.config in C:\WINDOWS\Microsoft.NET\Framework\v4.0.20506\Config.

Sorry I couldn't have been more help, but I guess my 2008 SP1 is suffering from the same problem yours is.

Edit 3: It appears 2008 SP1 should 'just work' with no need to install an "Compact 3.5 SP1 Beta for Entity Framework" but when I tried installing Service Pack 1 for 2008 I am still getting the same symptom. Only thought left is trying to re-install some SQL CE 3.5 components.

Related Topic