SQL Server Compact Edition with Entity Framework

Question

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?

Answer 1

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.

Answer 2

Hi guys,

There is no design time support for SQL CE 4.0 CTP1 in VS2010. so you can not use SQL CE 4.0 with Entity Framework.

But can be created using few hacks.

Below is a link to Tutorial for using Entity Framework for SQL Ce.

http://getsrirams.blogspot.com/2010/10/adonet-entity-data-model-for-sqlserver.html