Update primary key value using entity framework

Question

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

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!

Answer 1

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.

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 requires changing 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.

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:

Class(SurrogateKeyClass, InstructorID, CourseID)

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.

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

Student(StudentID, ....)

Course(CourseID, ...)

Taking(StudentID, CourseID, grade)

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 CourseID seems like a better alternative. Come up with your own association if this one seems contrived, but as a professor it was natural for me.

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.

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.

Answer 2

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.

Answer 3

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.

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 never changes. 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).

Answer 4

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.

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.

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.

Answer 5

You need to delete the old entity and insert it again with a modified ID. The easiest method is to JSON stringify the object to create a clone.

var cloned_account = DeserializeObject<account>(SerializeObject(existing_account));
cloned_account.user_id = 1;

db.account.Remove(existing_account);
db.account.Add(cloned_account);

Answer 6

I would like to suggest that instead of updating primary key, its better to insert another row with new primary key and delete the old one. However you need to be aware to change all the child entities to refer to the new primary key pair

Answer 7

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.