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

Question

I just moved one of my projects to VS2010 / fx4.0 and use the SQL CE database as the backup storage. Since moving it to this version of .NET, I get this error:

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

My table was defined using PK username (row) and DoorOpen (datetime) as SQLCE required , for each table in fx3.5 there should be PK. Now that I'm in fx4.0, I'm at a dead end. I searched this for a search, and every answer I found was:

SQLCE does not support auto-generation values ​​(which I certainly do not need), so add a GUID and write it from the code.

I tried this approach and I still get 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] ); 

Previously used restriction:

 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 what now? Is this a bug in EF4? Am I doing something wrong?

TIA

---

Note:

Go through the EDMX file (right-click, open with XML). I found that one of my date columns was set using 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> 

Then I switched to a pretty view of the model and clicked on each column in my database to make sure it was NOT . PITA for sure. It seems like you need to create the perfect little tool / add-on ...

Answer 1

It is important to check the EDMX file and ensure that this property / column does not have a StoreGeneratedPattern identifier.