C# 4.0/EF - Server-generated keys and server-generated values are not supported by SQL Server Compact.

Question

I have just moved one of my projects into VS2010/fx4.0 and am using a SQL CE database as the backing store. Since moving it to this version of .NET I am now getting this error:

Server-generated keys and server-generated values are not supported by SQL Server Compact.

My table was defined with a PK of UserName (string) & DoorOpen (datetime) as SQLCE required there be a PK on every table in fx3.5. Now that I am in fx4.0 I am stumped. I've googled for this and every answer I found was:

SQLCE does not support auto-generating values (which I am most certainly not needing) so put a GUID ID on there and populate it from code.

I tried this approach and I am still getting the same error!

SQLCE:

CREATE TABLE [ImportDoorAccesses] (
    [RawData] nvarchar(100)  NOT NULL,
    [DoorOpen] datetime  NOT NULL,
    [UserName] nvarchar(100)  NOT NULL,
    [CardNumber] bigint  NOT NULL,
    [Door] nvarchar(4000)  NOT NULL,
    [Imported] datetime  NOT NULL,
    [ID] uniqueidentifier  NOT NULL -- new column
);

ALTER TABLE [ImportDoorAccesses]
ADD CONSTRAINT [PK_ImportDoorAccesses]
    PRIMARY KEY ([ID] );

The constraint used to be:

ALTER TABLE [ImportDoorAccesses]
ADD CONSTRAINT [PK_ImportDoorAccesses]
    PRIMARY KEY ([DoorOpen],[UserName]);

CODE:

foreach (dto.DoorAudit newDoorAudit in dataTransferObject)
{
    if (newDoorAudit.DoInsert)
    {
        myEntities.AddToImportDoorAccesses(new ImportDoorAccess
        {
            CardNumber = newDoorAudit.CardNumber,
            Door = newDoorAudit.Door,
            DoorOpen = newDoorAudit.DoorOpen,
            Imported = newDoorAudit.Imported,
            RawData = newDoorAudit.RawData,
            UserName = newDoorAudit.UserName,
            ID = Guid.NewGuid()  // LOOK - HERE IT IS AS SUGGESTED!
        });
    }
}
myEntities.SaveChanges();

So, now what? Is this a bug in EF4? Am I doing something wrong?

TIA

---

NOTE:

Going through the EDMX file (right-click, open with, XML) I found that one of my date columns was set with StoreGeneratedPattern="Identity".

  <EntityType Name="ImportDoorAccesses">
    <Key>
      <PropertyRef Name="ID" />
    </Key>
    <Property Name="RawData" Type="nvarchar" Nullable="false" MaxLength="100" />
    <Property Name="DoorOpen" Type="datetime" Nullable="false" />
    <Property Name="UserName" Type="nvarchar" Nullable="false" MaxLength="100" />
    <Property Name="CardNumber" Type="bigint" Nullable="false" />
    <Property Name="Door" Type="nvarchar" Nullable="false" />
    <Property Name="Imported" Type="datetime" StoreGeneratedPattern="Identity" Nullable="false" />
    <Property Name="ID" Type="uniqueidentifier" Nullable="false" />
  </EntityType>

I then switched back to the pretty model view and clicked on every single column in my database to make sure this was NOT set. A PITA for sure. Looks like a perfect little tool/add-in needs to be created...

Answer 1

The important thing to check is the EDMX file and make sure this property/column doesn't have a StoreGeneratedPattern of identity in there.

Answer 2

See this blog: http://erikej.blogspot.com/2010/04/solutions-to-server-generated-keys-and.html