none
Entity framework updating composite primary key RRS feed

  • Question

  • I am using EF 6.0, SQL Server 2008 R2 and .Net 4.5

    I have two tables:

    PersonPhone

    PersonId int primary key
    PhoneTypeId int primary key
    PhoneNumber int 

    PhoneTypes

    PhoneTypeId int primary key Description varchar(50)

    When I try to update the PhoneTypeID from EF, I initially got:

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

    Then I modified my code by referring - http://stackoverflow.com/questions/3187963/the-property-id-is-part-of-the-objects-key-information-and-cannot-be-modified

    This is how it currently looks:

    PhoneType phoneType = PersonDb.PhoneTypes.Single(c => c.PhoneTypeId == phoneTypeID); //phoneTypeID is the existing DB row's type. 

    PhoneRow.PhoneType = phoneType; 

    PersonDb.SaveChanges();

    Now I get this error:

    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.

     

    Please assist.


    • Edited by pp1299 Friday, October 17, 2014 7:47 PM
    Friday, October 17, 2014 7:46 PM

Answers

  • Updating primary key columns is not a good practice.  It confuses EF because it changes the identity of the object, and makes keeping the in-memory copy and the in-database copy of the data in sync very problematic.  So it's not allowed.

    Just don't update primary keys.  Instead delete one row and insert a new one.

    Alternatively you can update the primary key directly with a store query.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fred BaoModerator Monday, October 20, 2014 3:25 AM
    • Marked as answer by pp1299 Monday, October 20, 2014 1:49 PM
    Friday, October 17, 2014 7:54 PM

All replies

  • Updating primary key columns is not a good practice.  It confuses EF because it changes the identity of the object, and makes keeping the in-memory copy and the in-database copy of the data in sync very problematic.  So it's not allowed.

    Just don't update primary keys.  Instead delete one row and insert a new one.

    Alternatively you can update the primary key directly with a store query.

    David


    David http://blogs.msdn.com/b/dbrowne/

    • Proposed as answer by Fred BaoModerator Monday, October 20, 2014 3:25 AM
    • Marked as answer by pp1299 Monday, October 20, 2014 1:49 PM
    Friday, October 17, 2014 7:54 PM
  • Building on David's comment, see:

    https://code.msdn.microsoft.com/ASPNET-MVC-Application-b01a9fe8/sourcecode?fileId=134759&pathId=2009908151

     private void UpdateInstructorCourses(string[] selectedCourses, Instructor instructorToUpdate) 
            { 
                if (selectedCourses == null) 
                { 
                    instructorToUpdate.Courses = new List<Course>(); 
                    return; 
                } 

                var selectedCoursesHS = new HashSet<string>(selectedCourses); 
                var instructorCourses = new HashSet<int> 
                    (instructorToUpdate.Courses.Select(c => c.CourseID)); 
                foreach (var course in db.Courses) 
                { 
                    if (selectedCoursesHS.Contains(course.CourseID.ToString())) 
                    { 
                        if (!instructorCourses.Contains(course.CourseID)) 
                        { 
                            instructorToUpdate.Courses.Add(course); 
                        } 
                    } 
                    else 
                    { 
                        if (instructorCourses.Contains(course.CourseID)) 
                        { 
                            instructorToUpdate.Courses.Remove(course); 
                        } 
                    } 
                } 
            } 

     

    Appreneur Application Architect Software Ronin

    Monday, September 21, 2015 7:52 AM
  • I disagree... When using natural keys or compose keys in tables we cannot use EF? Reason: some developers thinks that only Surrogate Keys are good practices?

    In my case, I need to create a table that contains two columns, code and description. Using surrogate keys, I need to store, at least, 2 integer fields and another string. With natural keys, 1 integer and 1 string. I am not mention the misunderstood that can occurs when one integer becomes different to other. Again, this perception is only forged due some developers oppinion that Surrogate Keys are the correct and "best practices" way.

    At this time, I cannot delete EF reference from project and must implement a solution to a real problem not covered by EF. If you do not want that we use natural key you need to block using natural keys in EF. Imposes implicit use of surrogate keys.

    The proposed solution, create a copy and delete the original, sounds like "gambiarra", in brazilian portuguese. Sounds great with tables without relationships. As the number of relationships and business objectives increases, this solution becomes a shotgun fire to get rid of a bug...

    Monday, July 17, 2017 2:31 PM
  • >When using natural keys or compose keys in tables we cannot use EF?

    There's noting wrong with using natural keys.  You still should not be modifying the key values.

    And if you do need to update the key, a simple SQL query the better workaround for than using EF to delete and then re-insert.

    David


    Microsoft Technology Center - Dallas
    My blog

    Saturday, July 22, 2017 6:23 PM