Using entity framework, change the value of the main key.

.net entity-framework sql-server vb.net

Question

The property "CustomerID" is a key component of the object and cannot be changed, and I'm attempting to edit one value of a compound primary key from inside the entity framework.

This 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 feels very basic. Is it accurate to say that an entity framework primary key cannot be updated? There is no documentation available on the subject. Thanks!

1
51
9/2/2009 1:41:35 PM

Accepted Answer

You cannot, and with good cause. See KM's remarks.

One solution I suggest is to create two tables, one for anonymous data and the other for true user data that is stored once a person logs in.

Or you could use a table arrangement like this (I haven't tried it or done it before):

---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 you adjust the CustomerType and CustomerID to what you need after they log in.

So, your inquiry may be as follows:

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

The main key for the autonumber never changes. This will save you from having to do cascade changes on the main key, (this is comparable to sticking a pencil in your own eye)., on any tables that are related to the Customers table and ensure that they continue to function.

11
9/3/2009 1:41:02 AM

Popular Answer

Since I have a Ph.D. in computer science with a focus on databases, my response will vary somewhat from that of a coder. With all due respect to Oliver Hanappi, if the key is not a surrogate key, it may and sometimes does change. Natural or composite keys, for instance. An illustration. In the US, changing your SSN is feasible. But many programmers have used this as an immutable key throughout the years and have thought of it as such. A composite primary key made up of many foreign keys is changed significantly more often.

I'm working with a ternary relationship-based database. in particular, three entities (with foreign keys being surrogate primary keys in their respective tables). However, the primary key of the intersection table (also known as a pure join table on MSDN) must be changed in order to maintain the link between two entities while updating the third object. This is a good design, and the only thing that could be done to make it better is to substitute two binary relationship tables for the ternary relationship intersection table (that may have their own surrogate keys). This would be handled well by EF. A (Many->Many)->Many or Parent1-Parent2 -> Child-Grandchild model would result from this design modification (if that's not apparent, check the example below). Given that each connection is really a one-to-many relationship, entity structure would function well with this. But from a database standpoint, it's a strange design. I'll give you an illustration of why.

Think about how the terms "course," "classroom," and "instructor" are related in a classroom. Classes may have composite primary keys that comprise all three of the following as foreign keys: CourseID, ClassroomID, and InstructorID. Although a straightforward, three-way ternary model, we might divide it into binary relationships. Two intersection tables would result from this. The following would satisfy EF if surrogate keys were added:

Class (SurrogateKeyClass, InstructorID, CourseID)

ClassRoomUsed (SurrogateKeyClassroomUsed, SurrogateKeyClass, ClassRoomID)

This architecture has the drawback of allowing for many associations of the same course and teacher, which the earlier model prevented. You can add a constraint to the database to ensure that the two ID columns are unique in order to prevent this issue, but why would you want to do that when you are already working with surrogate keys? However, as far as I can tell, this solution would be effective. However, due of the unnatural unique requirement needed in the DB, this is not a logic database architecture.

BUT here is a the second option if you don't want to modify your database or are unable to alter your database: Links connecting two things or more together make up intersection/association tables. Delete the association and build a new one with the necessary foreign keys if one changes (navigation properties). You won't be able to need child entities in any of the connections, but this is something that happens often.

I'd advise that the Entity Framework (in the future) enable those of us who can create a beautiful DB model to update specific keys in intersection/association tables as we choose!

Another free illustration

A student, course, and grade association comes to mind. Through a grade, students are connected to a course. This often involves a many-to-many relationship between the Student and the Course, with the inclusion of a grade field in the association table (association tables carry payload data like grade, while intersection tables do not, and are referred to in MSDN as pure join tables at least once):

Student (StudentID, ....)

Course (CourseID, ...)

Taking (StudentID, CourseID, grade)

You would like it if someone entered student information incorrectly from a dropdown and then changed it by picking a different course from the dropdown. You will need to remove the EF object from the Taking table in the background and rebuild it without losing any grades, if any. The Foreign Key CourseID should just be changed, since this seems like a better solution. If you think this relationship is artificial, come up with your own; but, for me as a professor, it seemed right.

Conclusion: Although it is not advised as a best practice in general, there are reasonable/logical situations where cascading and/or modifying FK are necessary when there is a long list of links.

Depending on whether you are updating the model's navigation property or key property, this issue may show up as one of the following exceptions:

Referential integrity constraints were broken: When the dependent object is Unchanged, a primary key attribute that is a component of the referential integrity constraint cannot be modified unless it is being assigned to the association's main object. The main item needs to be monitored and not designated for deletion.

The object's key information includes the attribute "X," which cannot be changed.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow