Note: Eric pointed me to a SQL Compact Toolbox he wrote that does a better job and quicker to setup.

So this week I wanted to add a local database to my application and I got the bright idea of since I am using Visual Studio 2010 SP1, .NET 4 and Entity Framework why not just use SQL CE 4.0.  It cannot be that hard to implement over SQL Server or SQL Express.  Boy what a journey this was, but I am still happy with my decision to continue with this.  So here are the steps to accomplish this feat, and I have tested Entity Framework 4.3 and it does appear to still be an issue.

Requirements

• Visual Studio 2010 with SP1
• Entity Framework 4.x+

• The first thing needed is to install Entity Framework 4.3,  this can be accomplished through Nuget.
• Next add ADO.NET Entity Data Model to the project

• Select Generate from Database and click Next

• From the Entity Data Model Wizard click New Connection
• Click Change for Data Source and here is where the fun begins
• This may not be the fault of Entity Frameworks (4.1 – 4.3) but Microsoft SQL Server Compact 4.0 is not listed.

• So what needs to be done if basically trick Entity Framework into thinking it wants to connect to compact 3.5.
• After Compact 3.5 is selected click OK
• From the Connection Properties for Database click Create
• Enter the location and name of the compact database (Anything can be put in for this name, this database will be deleted soon) and click Ok

• Back on the Connection Properties screen click Test Connection, if everything is good click OK
• On the Entity Data Model Wizard change the App/Web.config setting to be a real name (What the SQL CE 4.0 would be using)

• Click Finish
  • It may not be necessary but this error might present itself.  I said yes to it.

• Now add a SQL CE 4.0 data file to your project
• Right click your project –> Add –> New Item –> Data –> SQL Server Compact 4.0 Local Database
• Give this the real name and click Add

• Once the database has been added
• Right click on the Entity Framework edmx file and select open with
• Select XML (Text) Editor and click OK

• around line 7 the schema namespace should be defined.  If it is not there, you may need to open the model and run an update from database with the temp SQL SE database listed.

• Change the 3.5 references to 4.0, keep everything else the same

• Open up App/Web.config and change the provider from System.Data.SqlServerCe.3.5 to System.Data.SqlServerCe.4.0.
• Change the Data Source to the real local Sql CE 4.0 data file

• Once the changes are saved, the temp CE 3.5 data file can be deleted and Entity Framework should now work fully with it.