35

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:

  • Why would you mark key as server-generated if it is not supported and will throw an exception? It's hard to make sence from the quoted paragraph.
  • When I've tried to add StoreGeneratedPattern="Identity" to my entity's property, Studio complained that it is not allowed. What I'm doing wrong?
  • What is the best workaround for this limitation (including switching to another DB)? My limitations are zero-installation and using entity framework.
Sergey Aldoukhov
  • 22,316
  • 18
  • 72
  • 99

7 Answers7

24

When I hit this limitation, I changed the type to uniqueidentifier

mostlytech
  • 814
  • 9
  • 13
  • Strange, it doesn't seem to work for me. Changed the column type to uniquidentifier, refreshed the model, and fails for both StoreGeneratedPattern = None and Identity. – arviman Dec 06 '12 at 19:51
  • 1
    @arviman If you are using VS2010, you may have to delete the model and then refresh the model from the database. A coworker helped me stop beating my head with that one. It's still stupid, but at least it got it working. It might mess up links that you have though... – teynon Jul 09 '13 at 17:39
17

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.

  • I'd go with reading the max id + 1 solution, avoiding difficult contructs or GUIDs – Jeroen Feb 12 '11 at 22:59
  • The "bullet proof" solution suggested isn't even correct, nevermind bullet proof. Other processes (even the same *code*, say in a web farm context) would not respect the lock. There's only ONE place where it makes sense to do the synchronization, and that's in the database itself. What MS were thinking when they decided not to support this is beyond me. Not impressed by Entity Framework so far, that's for sure. – The Dag Oct 14 '15 at 15:05
6

SQL CE version 4.0 fixed this problem with its Entity Framework provider.

Pablonete
  • 1,504
  • 1
  • 14
  • 11
  • 1
    Where is this and how do you install it? I cannot find anything by googling this. – Kyeotic May 07 '12 at 01:18
  • 1
    @Tyrsius, I hope you've found it by now, nevertheless, here's the link: http://blogs.msdn.com/b/sqlservercompact/archive/2011/01/12/microsoft-sql-server-compact-4-0-is-available-for-download.aspx – Nelson Reis Jul 06 '12 at 15:18
  • @NelsonReis I had, but thank you for folowing up. I am sure someone will find this useful. – Kyeotic Jul 06 '12 at 15:48
  • 3
    I have to disagree with the linked post. It's 2013 and I am using EF5 in VS2012 and the Uniqueidentifier is not working when adding entities when saving changes (as ROWGUID or not). Unless the NuGets for EF for SqlCompact are somehow outdated. Throws the exception: `The column 'DeviceId' has type 'SqlServerCe.uniqueidentifier', which is not a valid type for an identity column.` – timmi4sa Aug 06 '13 at 21:27
  • This issue is not fixed even with Entity Framework. I am using version 6.2.0 EntityFramework.SqlServerCompact. I still get this error: `Server-generated keys are only supported for identity columns. The column 'ID' has type 'SqlServerCe.uniqueidentifier', which is not a valid type for an identity column.` – sky91 Dec 01 '17 at 07:47
4

I just hit this issue too... mostlytech's answer is probably the best option, GUIDs are very easy to use and the risk of key collision is very low (although not inexistant).

  • Why would you mark key as server-generated if it is not supported and will throw an exception? It's hard to make sence from the quoted paragraph.

Because SQL Server (not Compact) supports it, and other third parties may support it too... Entity Framework is not only for SQL Server Compact ;)

Thomas Levesque
  • 286,951
  • 70
  • 623
  • 758
  • 2
    GUIDs, however, make terrible primary keys and indexes because they are not sequential. Better to use bigint/long and use a time-based value (similar to hightechrider's approach). – mlibby Aug 18 '10 at 21:13
4

In my case, all of my classes have the primary key named "ID"

I created an interface

public class IID
{
    public Int32 ID { get; set; }
}

Then I create an extension method

public static Int32 GetNextID<T>(this ObjectSet<T> objects)
    where T : class, IID
    {
        T entry = objects.OrderByDescending(u => u.ID).FirstOrDefault();
        if (entry == default(T))
            return 1;
        return entry.ID + 1;
    }

Then when I need a new ID, I just do this:

MyObject myobj = new MyObject();
myobj.ID = entities.MyTable.GetNextID();
Mike Christiansen
  • 1,104
  • 2
  • 13
  • 30
  • 2
    That will not work in case of multiple concurrent operation. If two operations call `GetNextID` before they save the record they will both have the same Id. – Ladislav Mrnka May 12 '11 at 08:51
  • What if this GetNextID method wrapped the select in a ReadUncommited transaction? – BenCr Jan 18 '13 at 17:18
1

the other option is to use SqlCeResultSet on the tables that have the identity column.

user544111
  • 33
  • 6
0

i have a primary key named ID with data type of INT32 and have Identity Column

Just do this

MyEntity Entity = new MyEntity();

String Command;

command = "Insert into Message(Created,Message,MsgType)values('12/1/2014','Hello World',5); Entity.ExecuteStoreCommand(command);

--Exclude the primary key in the insert Statement

--Since the SQLCE do not support system generated keys

--Do not use LINQ because it supplies a default value to 0 for Primary keys that has a data type of INT

Ian
  • 1
  • 1