I just started to play with the entity framework, so I decided to connect it to my existing SQL Server CE database. I have a table with an IDENTITY(1, 1) primary key but when I tried to add an entity, I've got the above-mentioned error.
From MS Technet artice I learned that
SQL Server Compact does not support entities with server-generated keys or values when it is used with the Entity Framework. When using the Entity Framework, an entity’s keys may be marked as server generated. This enables the database to generate a value for the key on insertion or entity creation. Additionally, zero or more properties of an entity may be marked as server-generated values. For more information, see the Store Generated Pattern topic in the Entity Framework documentation. SQL Server Compact does not support entities with server-generated keys or values when it is used with the Entity Framework, although the Entity Framework allows you to define entity types with server-generated keys or values. Data manipulation operation on an entity that has server-generated values throws a "Not supported" exception.
So now I have a few questions:
Use uniqueidentifier or generate a bigint/int key value manually is your best option.
Something like this perhaps ...
private static object lockObject = new object();
private static long nextID = -1;
public static long GetNextID()
{
lock (lockObject)
{
if (nextID == -1) nextID = DateTime.UtcNow.Ticks; else nextID++;
return nextID;
}
}
This assumes that you don't generate more than one record per tick during an application run (plus the time to stop and restart). This is a reasonable assumption I believe, but if you want a totally bullet proof (but more complex) solution, go read the highest ID from the database and increment from that.