Syntax for Insert and Update with SqlCe and EntityFramework

Question

VS 2008, SqlCe 3.5

I'm trying to learn EntityFramework, but can't get the basic Insert and update to work. When I include the SqlCe database (.sdf) a wizard creates the Test.edmx/designer.vb file. From that I create my datacontext, like below. The table name is Users.

The syntax of my entity classes seems a bit different from examples on the web. That's a bit confusing, and I don't know why this is. Below I show two different Insert methods, both of which gives exceptions on the .SaveChanges line:

An error occurred while updating the entries. See the InnerException for details.

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

Also the Update method I have really no idea what to write in the missing part.. Would be very glad for some assistance on these issues...

Public Sub Insert(ByVal user As Users)
    Dim ctx As New TestDBEntities1(connection)
    ctx.Users.Context.AddObject("Users", user)
    ctx.Users.Context.SaveChanges()
End Sub

Public Sub Insert(ByVal user As Users)
    Dim ctx As New TestDBEntities1(connection)

    ctx.AddToUsers(user)
    ctx.SaveChanges()
End Sub


Public Sub Update(ByVal user As Users)
    Dim ctx As New TestDBEntities1(connection)
    Dim q = (From n In ctx.Users Where n.Id = user.Id Select n).Single

    ' How to update ??

    ctx.SaveChanges()
End Sub

Appears to be a duplicate of http://stackoverflow.com/questions/2734424/c-4-0-ef-server-generated-keys-and-server-generated-values-are-not-supported-b — SilverbackNet, Jan 08 '11 at 05:38
What does that mean? If it can not be identity key, does that mean I can not have auto-generated integer Id's? I need to create and keep them updated by hard coding? — bretddog, Jan 08 '11 at 05:57
You will indeed have to handle key creation yourself. (I would not recommend trying to "update" them by the way). SQLS Compact is unable to generate keys for you, so everything concerning store generation will not work. Either use the GUID approach described in the link above, or switch to another database for your tests, bindings exist for free databases such as SQLite in CE .NET 3.5. — Marcanpilami, Jan 08 '11 at 13:54

score 1 · Answer 1 · answered Mar 10 '11 at 06:31

Well according to this Microsoft TechNet article they say this:

When using the Entity Framework, an entity’s keys may be marked as server generated. This enables the database to generate a value for the key on insertion or entity creation. Additionally, zero or more properties of an entity may be marked as server-generated values. For more information, see the Store Generated Pattern topic in the Entity Framework documentation.

SQL Server Compact does not support entities with server-generated keys or values when it is used with the Entity Framework, although the Entity Framework allows you to define entity types with server-generated keys or values. Data manipulation operation on an entity that has server-generated values throws a "Not supported" exception.

But you do have a couple options, you can use an UNIQUEIDENTIFIER or you can generate your own key of type bigint/int. Best solution for that would be to read the current ID in that table and increment it by 1

score 0 · Accepted Answer · answered Jul 29 '12 at 07:50

0

As comment above; This was fixed in SQL CE 4.0.

answered Jul 29 '12 at 07:50

bretddog

5,411
11
63
111

Syntax for Insert and Update with SqlCe and EntityFramework

2 Answers2

Linked