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

Answer 1

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