How to get Entity Framework to talk to SQL CE 4

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

CropperCapture[8]

  • Select Generate from Database and click Next

CropperCapture[9]

  • 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.

CropperCapture[10]

  • 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

CropperCapture[11]

  • 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)

CropperCapture[14]

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

CropperCapture[13]

  • 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

CropperCapture[16]

  • 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

CropperCapture[17]

  • 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.

CropperCapture[18]

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

CropperCapture[19]

  • 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

CropperCapture[20]

  • Once the changes are saved, the temp CE 3.5 data file can be deleted and Entity Framework should now work fully with it.
About these ads
This entry was posted in Database and tagged , , , . Bookmark the permalink.

6 Responses to How to get Entity Framework to talk to SQL CE 4

  1. erikej says:

    You could also use the SQL Server Compact, and what you describe above becomes a silnle clikc processs.

  2. Rathna says:

    Hi sir. Thank you very much…am doing my final year project…your suggestions help me a lot….
    thank you…

  3. Pingback: Utilizando SqlServer Compact Edition com o Entity Framework | MayogaX dev blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s