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

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

Best Regards
Oliver Hanappi

Answer 4

I have the same problem. I am using natural keys (Part number). What happens if the user enters a part number and realises that they have made a typing error. I have cascade update on in the database. I want to correct the natural primary key.

I cannot use surrogate keys because my system retrieves part number info from many sources and aggregates them into a single scenario.

Answer 5

I, like Phil, am using natural keys. I would like to be able to update them through EF, but I couldn't figure that out. If it is possible i would like to know how to do it. Until then I think I have no choice but to inactivate a row and insert a new one... what a pain.