0

I am creating an Entity Framework 4-based app that uses SQL Server CE 3.5 as its data store.

I have an empty .sdf file generated with the .sqlce script created from an Entity Data Model.

When I run this code:

Item item = new Item();
item.ID = 1;
item.Title = "example";
item.Value = 37;

using (ModelContainer context = new ModelContainer())
{
    context.ItemsSet.AddObject(item);

    context.SaveChanges();
}

on the SaveChages() I get this System.Data.UpdateException:

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

From this question I know that when used with the Entity Framework, SQL Server CE 3.5 does not support autogenerated primary keys. In my case, however, I explicitly set the ID attribute of the item I'm adding.

Is there something else wrong?

EDIT:

This is the content of my .sqlce script after removing IDENTITY(1,1):

-- Creating table 'ItemsSet'
CREATE TABLE [ItemsSet] (
    [ID] int NOT NULL,
    [Title] nvarchar(4000)  NOT NULL,
    [Value] int NULL
);
GO

-- Creating primary key on [ID] in table 'ItemsSet'
ALTER TABLE [ItemsSet]
ADD CONSTRAINT [PK_ItemsSet]
    PRIMARY KEY ([ID] );
GO
Community
  • 1
  • 1
Dev
  • 7,027
  • 6
  • 37
  • 65

1 Answers1

1

Setting the ID explicitly from the code is not going to do anything if you have set it to auto increment in database. You can either use uniqueidentifier or modify the primary key in the table and remove the auto increment. Then you would be able to set the Id to a value from the code. Check out this thread Server-generated keys and server-generated values are not supported by SQL Server Compact

Community
  • 1
  • 1
Habib
  • 219,104
  • 29
  • 407
  • 436
  • Hi and thanks for your answer! This is the `CREATE TABLE` inside my **.sqlce**: `CREATE TABLE [ItemsSet] ( [ID] int IDENTITY(1,1) NOT NULL, ...)` And then: `ALTER TABLE [ItemsSet] ADD CONSTRAINT [PK_ItemsSet] PRIMARY KEY ([ID] ); GO` Do you think there is something wrong here? – Dev Apr 05 '12 at 18:55
  • 1
    @Dev Yes, you have specified IDENTITY while defining ID column, that is specifying auto increment field for primary key, remove that, and your code should be good to go – Habib Apr 05 '12 at 19:12
  • Unfortunately, the same error "_Server-generated keys and server-generated values are not supported by SQL Server Compact._" appears... I have updated my question with the content of my **.sqlce** file. – Dev Apr 06 '12 at 07:48
  • Solved! As I found in this answer: http://stackoverflow.com/questions/2734424/c-sharp-4-0-ef-server-generated-keys-and-server-generated-values-are-not-suppo "_The important thing to check is the EDMX file and make sure this property/column doesn't have a StoreGeneratedPattern of identity in there._" Now it works! – Dev Apr 06 '12 at 08:01