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
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
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