Primary Keys in Entity Framework and Sync Framework - Part 2

In Part 1 we considered various options when it came to using Microsoft Sync Framework with Entity Framework.

After a certain amount of pain we seem to have got to something approaching a settled condition.

1. We didn't want IDs that were GUIDs as this would make them unreadable. So, we are keeping Primary keys as simple ID columns.
2. Because of this, we went for a solution that would reseed the local tables on each client to a known range. After each synchronisation, the tables would be reseeded to the correct value. That way entries created on the client/server would have different IDs and wouldn't clash
3. We had to use SQL Server Express on the clients.

Number (3) above is a long story...

• SQL Compact 4 won't play nicely with Entity Framework or the Sync Framework (Link here and here )
• SQL Compact 3.5 wouldn't play nicely with the various items - follow this link and see the section "Limitations of SQL Server compact" - basically it can't:
• "support entities with server-generated keys or values when it is used with the Entity Framework ".

There's a lot of other things that SQL server compact can't do - including supporting stored procedures. So that's how we ended up back at SQL Server Express.

I hope this gives you a flavour of the sort of things to consider when looking at data replication on SQL Server.

You're going to ask why didn't we use SQL Server Merge Replication - that's another story and another post...