System.Data.UpdateException on SaveChanges() with SQL Server CE

Go To StackoverFlow.com

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
2012-04-05 17:54
by Dev
What you have for ID in table ? is it set to auto generated - Habib 2012-04-05 18:20


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

2012-04-05 18:28
by Habib
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 2012-04-05 18:55
@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 g - Habib 2012-04-05 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 2012-04-06 07:48
@Dev did you update the edmx - Habib 2012-04-06 07:51
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 2012-04-06 08:01
Ads