Target SQL Server and SQL CE with the same Entity Framework objects while using Sync Framework

When developing a sometimes-disconnected application in .NET, you have the ability to create a “local data cache” (Sql CE file) and then synchronize this local database with a server database periodically (when connected) to keep the data relevant.

Of course to actually interact with the data from your .NET application, you will want to create a data model with Entity Framework. There are a few tricks that can make the whole process much simpler.

Step 1: Create your database on the server first. After you design your database on the server, be sure to generate and save the SQL scripts so you can re-create it if you fill it with bad data.

Step 2: Create the Entity Data Model against the server database. You are unlikely to be accessing all of the change tracking fields which will be added later once you create a Sync Framework local data cache, so there is no need for these fields to be present in your data model. By generating your *.edmx against the clean server database, you create Entities that will only reflect fields used by your application domain.

Step 3: Create the Local Cache against the server database. Now is the time to “dirty up” the server database with change tracking fields (you still have those prestine SQL scripts saved from step 1, right?). Create your *.sync against the server DB and don’t use SQL Server change tracking (let it create change tracking fields and tables). I do this for compatibility with older versions of SQL Server (only 2008 starts supporting change tracking).

At this point, you should have a set of Entity Framework objects that you can use to interact with the server database, and a set of Sync Framework objects that you can use to synchronize server data with a local cache SQL CE file.

Now comes the fun part—switching your Entities to use the SQL CE file instead of the server…

Step 4: update your app.config connection strings. Of course you will need to change the connection string in the config to point at your local data cache instead of the server for the Entities. I simply comment out the old one (for when I might want to switch back), and add a new one to get something that looks like this:

<connectionStrings>

<!-- Server (obviously substitute your own values for [example]) 
<add name="[example]DBEntities" connectionString="metadata=res://\*/[example]Model.csdl|res://\*/[example]Model.ssdl|res://\*/[example]Model.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=localhost;initial catalog=\[example\]DB;persist security info=True;user id=sa;password=\[example\];multipleactiveresultsets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" /> -->

<!-- Local Cache (obviously substitute your own values for \[example\]) --> <add name="[example]DBEntities" connectionString="metadata=res://\*/[example]Model.csdl|res://\*/[example]Model.ssdl|res://\*/[example]Model.msl;provider=System.Data.SqlServerCe.3.5;provider connection string=&quot;Data Source=|DataDirectory|\\DB\\[example]DB.sdf;Max Database Size=2047&quot;" providerName="System.Data.EntityClient" />

</connectionStrings>

Step 5: update the *.edmx for SqlServerCe. You need to open your *.edmx file with an XML editor (in VS2010 right-click the file, select “Open With…” and then double click XML Editor). The first change you need to make is to the Schema element (near the top of the file). You need to change the Provider attribute from “System.Data.SqlClient” to “System.Data.SqlServerCe.3.5” like this:

<!-- for local cache use "System.Data.SqlServerCe.3.5" for server use "System.Data.SqlClient" --> 
<Schema Namespace="\[example\]DBModel.Store" Alias="Self" Provider="System.Data.SqlServerCe.3.5" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">

You might be done at this point, or you might get a bunch of errors that say

“Type _____ is not qualified with a namespace or alias.”

Most of the data types between SQL Server and SQL CE match up fairly well, however there is a very common data type that doesn’t seem to match: “nvarchar(Max)”

The way to fix those errors is to manually update the text generated by Entity Framework (don’t ask me why it doesn’t just generate the correct text that works for both SQL Server and SQL CE in the first place, I didn’t write it :P).

You need to update the errored lines thusly:

<Property Name="ExampleField" Type="nvarchar(MAX)" Nullable="false"/>

becomes…

<Property Name="ExampleField" Type="nvarchar" Nullable="false" MaxLength="Max"/> 

At this point everything should be good to go :)

P.S.

If you encounter problems with your local cache not being updated or are receiving an exception when calling the SyncAgent.Synchronize() method:

“The specified change tracking operation is not supported. To carry out this operation on the table, disable the change tracking on the table, and enable the change tracking.”

You need to change the properties of your *.SDF file (the local cache) so that it is not copied to the output directory:

Rich-click the *.sdf file in VS2010 select Properties, then change the value for “Copy to Output Directory” to “Do Not Copy”.

Also, if you are seemingly unable to push data from the local cache to the server, be sure the table you want to push up to the server is set to Bi-directional mode before you call the Synchronize() method:

syncAgent.ExampleTable.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional;