.net 使用实体框架更新主键值

Update primary key value using entity framework

提问by Paul Lemke

I'm trying to update one value of a compound primary key from within the entity framework and I'm getting this error: "The property 'CustomerID' is part of the object's key information and cannot be modified. "

我正在尝试从实体框架中更新复合主键的一个值，但出现此错误：“属性 'CustomerID' 是对象键信息的一部分，无法修改。”

Here is my code:

这是我的代码：

Dim customer As Customer = (From c In db.Customer Where c.CustomerID = "xxx" AndAlso c.SiteKey = siteKey).FirstOrDefault
customer.CustomerID = "fasdfasdf"
db.SaveChanges()

It seems too simple. Is it true you can't update a primary key within the entity framework? I can't find any documentation on the topic. Thanks!

似乎太简单了。你真的不能在实体框架内更新主键吗？我找不到有关该主题的任何文档。谢谢！

采纳答案by Nathan W

You can't and for good reason. See KM comments.

你不能而且有充分的理由。见知识管理评论。

One thing I say you could do is have two tables one with anonymous data and one that stores the the real user data after they log in.

我说你可以做的一件事是有两个表，一个包含匿名数据，另一个存储用户登录后的真实数据。

Or your could (not tested or ever done by me) is have this kind of table layout:

或者你可以（我没有测试或做过）有这种表格布局：

---Customers----
AutoNumber PK <- This links to all other tables in your database, and does NOT change.
CustomerID  <- This can change.
CustomerType <- Anonymous or logged in.  

And when they log in you change the CustomerType and CustomerID to what you need.

当他们登录时，您将 CustomerType 和 CustomerID 更改为您需要的。

So your query could look like this:

因此，您的查询可能如下所示：

Dim customer As Customer = (From c In db.Customer _
                            Where c.CustomerID = {Some temp ID} _
                            AndAlso c. CustomerType = "Anonymous").FirstOrDefault
// After user logs in.
customer.CustomerID = {Make a new user ID here}
customer.CustomerType = "LoggedIn" {or what ever}
db.SaveChanges()

Note that the autonumber primary key neverchanges. This is so that any tables that you have in a relationship with the Customers table still work and don't have to do cascading updates on the primary key (which is like stabbing yourself in the eye with a pencil).

请注意，自动编号主键永远不会改变。这样，您与客户表有关系的任何表仍然可以工作，并且不必对主键进行级联更新（这就像用铅笔刺伤自己的眼睛）。

回答by Dr. A

I have a Ph.D. in cs - in the area of Databases, so this answer will be a bit different than a programmers perspective. With all respect to Oliver Hanappi, a key can and occasionally does change if it is not a surrogate key. E.g. A natural key or a composit key. For example. It is possible to get your SSN changed in the US. But many programmers down through the years would consider this an unchangeable key and use it as such. It is much more common to change a composite primary key made up of foreign keys.

我有博士学位。在 cs - 在数据库领域，所以这个答案与程序员的观点有点不同。就 Oliver Hanappi 而言，如果密钥不是代理密钥，它可以并且偶尔会发生变化。例如自然键或复合键。例如。在美国可以更改您的 SSN。但是多年来，许多程序员会认为这是一个不可更改的密钥，并因此使用它。更改由外键组成的复合主键更为常见。

I'm dealing with a database that has a ternary relationship. Specifically three entities (with foreign keys being surrogate primary keys in their respective tables). However, to preserve the relationship between two entities while changing the third entity requireschanging part of the intersection table (also called a pure join table on MSDN) primary key. This is a valid design and could only be improved by removing the ternary relationship intersection table and replacing it with two binary relationship tables (that may have their own surrogate keys). EF would handles this fine. This design change would make a (Many->many)->many or Parent1-Parent2 -> Child-grandchild model (if that's not clear read the example below). Entity framework would work fine with this as each relationship is really a one to many relationshiop. But its a crazy design from a DB perspective. Let me show you an example why.

我正在处理一个具有三元关系的数据库。特别是三个实体（外键是它们各自表中的代理主键）。但是，要在更改第三个实体的同时保留两个实体之间的关系需要更改交集表的一部分（在 MSDN 上也称为纯连接表）主键。这是一个有效的设计，只能通过删除三元关系交集表并将其替换为两个二元关系表（可能有自己的代理键）来改进。EF 会处理这个罚款。此设计更改将使 (Many->many)->many 或 Parent1-Parent2 -> Child-grandchild 模型（如果不清楚，请阅读下面的示例）。实体框架可以很好地处理这个问题，因为每个关系实际上都是一对多的关系。但从数据库的角度来看，这是一个疯狂的设计。让我举个例子告诉你为什么。

Consider that Course, Classroom and Instructor are associated with one another in a class. Class could include: CourseID, ClassroomID, InstructorID as foreign keys and contain a composit primary key including all three. Although a clear, concise ternary model (3 way relationship) we could break it up into binary relationships. This would give two intersection tables. Adding surrogate keys would satisfy EF as follows:

考虑课程、课堂和教师在一个班级中相互关联。类可以包括：CourseID、ClassroomID、InstructorID 作为外键，并包含一个包含所有三个的复合主键。虽然是一个清晰、简洁的三元模型（3 向关系），但我们可以将其分解为二元关系。这将给出两个交集表。添加代理键将满足 EF 如下：

Class(SurrogateKeyClass, InstructorID, CourseID)

类（SurrogateKeyClass，InstructorID，CourseID）

ClassRoomUsed(SurrogateKeyClassroomUsed, SurrogateKeyClass, ClassRoomID)

ClassRoomUsed( SurrogateKeyClassroomUsed, SurrogateKeyClass, ClassRoomID)

The problem with this design is that we could have the same course and instructor associated multiple times, which the previous model avoids. To avoid this problem, you can add a constraint in the database for uniqueness of the two ID fields, but why would you want to do this when you are only dealing with surrogate keys to start with? However this solution would work as best as I can tell. This is not however a logic database design because of the unatural unique constraint required in the DB.

这种设计的问题是我们可以多次关联相同的课程和讲师，而以前的模型避免了这种情况。为了避免这个问题，您可以在数据库中为两个 ID 字段的唯一性添加一个约束，但是当您开始只处理代理键时，为什么要这样做呢？然而，这个解决方案将尽我所能。然而，这不是逻辑数据库设计，因为数据库中需要不自然的唯一约束。

BUT, if you don't want to change your database or can't change your database, here is a second solution: Intersection/association tables are just that, links linking two entities or more together. If one changes, delete the association and recreate a new one that has the appropriate foreign keys (navigation properties). That means that you will not be allowed to require child entities in any of the relationships, but that is extremely common.

但是，如果您不想更改数据库或无法更改数据库，这里有第二种解决方案：交集/关联表就是这样，链接将两个或更多实体链接在一起。如果有更改，请删除关联并重新创建一个具有适当外键（导航属性）的新关联。这意味着您将不被允许在任何关系中要求子实体，但这非常常见。

I would suggest that the Entity Framework (in the future) allow those of us who can design an elegant DB model to change parts of keys in intersection/association tables when we want!

我建议实体框架（在未来）允许我们这些可以设计优雅的数据库模型的人在需要时更改交集/关联表中的部分键！

Another Example for free:

另一个免费示例：

Consider a Student, Course, Grade Association. Students are associated with a course via a grade. Usually this is a many to many association between Student and a Course with an additional field in the association table called grade (association tables have payload data like grade, intersection tables do not have a payload and are refered to in MSDN as pure join tables at lease in one place):

考虑一个学生、课程、年级关联。学生通过成绩与课程相关联。通常这是学生和课程之间的多对多关联，关联表中有一个名为成绩的附加字段（关联表具有有效载荷数据，如成绩，交集表没有有效载荷，在 MSDN 中被称为纯连接表在一处租赁）：

Student(StudentID, ....)

学生（学生ID，....）

Course(CourseID, ...)

课程（课程ID，...）

Taking(StudentID, CourseID, grade)

以（StudentID，CourseID，等级）

If someone makes a data entry error from a dropdown and puts a student in the wrong class, you like them to change it later by selecting the dropdown again and selecting a different course. In the background you will need to delete the EF object from the Taking table and recreate it without losing a grade if there is one. Simply changing the Foreign Key CourseIDseems like a better alternative. Come up with your own association if this one seems contrived, but as a professor it was natural for me.

如果有人从下拉列表中输入数据错误并将学生放在错误的班级中，您希望他们稍后通过再次选择下拉列表并选择不同的课程来更改它。在后台，您需要从 Take 表中删除 EF 对象并重新创建它，而不会丢失一个等级（如果有的话）。简单地更改外键CourseID似乎是一个更好的选择。如果这似乎是人为的，请提出您自己的协会，但作为教授，这对我来说很自然。

Conclusion: When you have a string of relationships, it may be better not to allow cascading and/or changing FK, but there exists resonable/logical scenarios where it is needed, even if not recommended as a best practice in general.

结论：当您有一系列关系时，最好不要允许级联和/或更改 FK，但存在需要它的合理/逻辑场景，即使通常不建议将其作为最佳实践。

This problem may manifest itself with the following Exceptions depending on if you are changing the navigation property or the key property in the model respectively:

根据您是分别更改模型中的导航属性还是键属性，此问题可能会通过以下异常表现出来：

A referential integrity constraint violation occurred: A primary key property that is a part of referential integrity constraint cannot be changed when the dependent object is Unchanged unless it is being set to the association's principal object. The principal object must be tracked and not marked for deletion.

发生参照完整性约束冲突：当依赖对象未更改时，作为参照完整性约束一部分的主键属性无法更改，除非将其设置为关联的主体对象。主体对象必须被跟踪而不是标记为删除。

The property 'X' is part of the object's key information and cannot be modified.

属性“X”是对象关键信息的一部分，不能修改。

回答by Shiraz Bhaiji

You cannot update the primary key through entity framework, since entity framework would not know which database row to update.

您不能通过实体框架更新主键，因为实体框架不知道要更新哪个数据库行。

However, if you really need to do it, you could write a stored procedure that updates the primary key, and then execute the stored procedure from entity framework.

但是，如果您确实需要这样做，您可以编写一个更新主键的存储过程，然后从实体框架中执行该存储过程。

回答by Tom

If there could be only one instance of the database context at any time then modifying the pk would not be such a problem. But each context instance maintains its own cache and can be caching the record that you want to modify.

如果在任何时候都可能只有一个数据库上下文实例，那么修改 pk 就不会成为这样的问题。但是每个上下文实例都维护自己的缓存，并且可以缓存您要修改的记录。

EF interactions with the database use the pk (from the context cache) to identify the record(s) that they are querying. If a context object could update the key in persistence, the information that other context objects rely upon to identity that record is immediately and permanently wrong.

EF 与数据库的交互使用 pk（来自上下文缓存）来标识他们正在查询的记录。如果上下文对象可以在持久性中更新密钥，则其他上下文对象依赖于标识该记录的信息立即且永久错误。

In short, if you update the primary key you have invalidated the cache in potentially all other instances of the context, and that would break EF fundamentally. This is one of the bigger reasons why it's a bad idea to update a pk.

简而言之，如果您更新主键，您可能会使上下文的所有其他实例中的缓存无效，这将从根本上破坏 EF。这是更新 pk 是个坏主意的更大原因之一。

回答by Oliver Hanappi

You cannot update a primary key, but that is not a limitation of entity framework, but a very fundamental rule of database development. A primary key is assigned once to a row in a table and makes this row unique.
Maybe you can update the key in some ways, but this violates definitely the definition of a primary key.

您不能更新主键，但这不是实体框架的限制，而是数据库开发的一个非常基本的规则。主键一次分配给表中的一行，并使该行唯一。
也许您可以通过某些方式更新键，但这绝对违反了主键的定义。

So, just don't do it, there are other ways to accomplish what you are trying to do.

所以，不要这样做，还有其他方法可以完成您正在尝试做的事情。