sql - 使用 SqlCe 和 EntityFramework 进行插入和更新的语法

Question

VS 2008, SqlCe 3.5

我正在尝试学习 EntityFramework，但无法让基本的插入和更新工作。当我包含 SqlCe 数据库 (.sdf) 时，向导会创建 Test.edmx/designer.vb 文件。从那里我创建了我的数据上下文，如下所示。表名是用户。

我的实体类的语法似乎与网络上的示例有点不同。这有点令人困惑，我不知道这是为什么。下面我展示了两种不同的 Insert 方法，这两种方法都在 .SaveChanges 行中给出了异常:

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."}

还有 Update 方法，我真的不知道在缺失的部分写什么。很高兴能在这些问题上提供一些帮助......

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

嗯根据这个Microsoft TechNet article他们是这样说的:

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.

但是您确实有几个选择，您可以使用 UNIQUEIDENTIFIER，或者您可以生成自己的 bigint/int 类型的 key 。最好的解决方案是读取该表中的当前 ID 并将其递增 1