SQL Server Compact does not allow keys and values produced by the server.

compact-framework entity-framework

Question

I just recently began experimenting with the entity framework, therefore I choose to link it to my current SQL Server CE database. I attempted to add an entity to a table with an IDENTITY(1, 1) primary key, but I received the error indicated above.

Zzz-9 to Zzz I discovered 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.

I now have the following inquiries:

  • Why would you label a key as server-generated if it would cause an error and is not supported? It's difficult to understand the text that was cited.
  • My entity's property was rejected by Studio when I attempted to add StoreGeneratedPattern="Identity" to it. What am I mishandling?
  • What is the best way to get around this restriction (including using a different DB)? My restrictions are utilizing entity framework and zero-installation.
1
35
3/15/2009 8:05:26 PM

Accepted Answer

When I came across this restriction, I switched the type to uniqueidentifier.

24
3/15/2009 8:40:33 PM

Popular Answer

Your best bet is to manually create a bigint/int key value or use uniqueidentifier.

Maybe something like this

    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 implies that throughout an application run, you only produce one record each tick (plus the time to stop and restart). This is, in my opinion, a fair assumption, but if you want a really foolproof (albeit more complicated) method, go read the highest ID from the database and increment from there.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow