locked
Get duplicate key exception when deleting and then re-adding child rows with Entity Framework RRS feed

  • Question

  • I'm using the Entity Framework to model a simple parent child relationship between a document and it's pages. The following code is supposed to (in this order):

    • make a few property updates to the document
    • delete any of the document's existing pages
    • insert a new list of pages passed into the method.

    The new pages do have the same keys as the deleted pages because there is an index that consists of the document number and then the page number (1..n).

    This code works. However, when I remove the first call to SaveChanges, it fails with: System.Data.SqlClient.SqlException: Cannot insert duplicate key row in object  'dbo.DocPages' with unique index 'IX_DocPages'.

    If I leave the code as written, EF creates two transactions -- one for each call to SaveChanges. The first updates the document and deletes any existing pages. The second transaction inserts the new pages. I examined the SQL trace and that is what I see.

    However, if I remove the first call to SaveChanges (because I'd like the whole thing to run in a single transaction), EF mysteriously does not do the deletes at all but rather generates only the inserts?? -- which result in the duplicate key error. I wouldn't think that waiting to call SaveChanges should matter here?

    Incidentally, the call to _docRepository.DeletePage(page) does a objectContext.DeleteObject(page). Can anyone explain this behavior?

    Thanks.

    Here is the code:

           Document doc = _docRepository.GetDocumentByRepositoryDocKey(repository.Repository_ID, repositoryDocKey);
    
            if (doc == null) {
                doc = new Document();
                _docRepository.Add(doc);
            }
            _fieldSetter.SetDocumentFields(doc, fieldValues);
    
            List<DocPage> pagesToDelete = (from p in doc.DocPages
                                           select p).ToList();
    
            foreach (DocPage page in pagesToDelete) {
                _docRepository.DeletePage(page);
            }
    
            _docRepository.GetUnitOfWork().SaveChanges();  //IF WE TAKE THIS OUT IT FAILS
    
            int pageNo = 0;
            foreach (ConcordanceDatabase.PageFile pageFile in pageList) {
                ++pageNo;
                DocPage newPage = new DocPage();
                newPage.PageNumber = pageNo;
                newPage.ImageRelativePath = pageFile.Filespec;
                doc.DocPages.Add(newPage);
            }
    
            _docRepository.GetUnitOfWork().SaveChanges();  //WHY CAN'T THIS BE THE ONLY CALL TO SaveChanges
    

    Thursday, October 8, 2009 9:31 PM

Answers

  • Hi Howard,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    Is the PageNumber the primary key of the Page object?  If so, I think the pageNo which is started from zero are duplicate with the original object primary keys.  When calling SaveChanges, the ObjectContext detects there are duplicate primary keys among the objects it is tracking.

     

    One workaround would be first updating some entity objects and then deleting or inserting other entities based on the length of the pagesToDelete and pageList collections.  Here I assume that the original page objects has a primary key started from zero.

     

    Here I tested on a parent table Category and a child table Section. 

    =======================================================================================
                MyNewDatabaseEntities context = new MyNewDatabaseEntities();

     

                var c = context.Category.Include("Section").First();

                var list = c.Section.ToList();

     

                List<string> sectionNames = new List<string>() { "SectionName1", " SectionName2", " SectionName3" };

                int index = 0;

                if (list.Count >= sectionNames.Count)

                {

     

                    foreach (var s in list.Take(sectionNames.Count))

                    {

                        s.SectionName = sectionNames[index++];

                    }

                    foreach (var s in list.Skip(sectionNames.Count))

                    {

                        context.DeleteObject(s);

                    }

                }

                else

                {

                    foreach (var s in list)

                    {

                        s.SectionName = sectionNames[index++];

                    }

                    for (int i = index; i < sectionNames.Count; i++)

                    {

                        Section newS = new Section();

                        newS.SectionID = i;

                        newS.SectionName = sectionNames[i];

                        c.Section.Add(newS);

                    }

                }

     

                var deleted = context.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted);

                var added = context.ObjectStateManager.GetObjectStateEntries(EntityState.Added);

                var modified = context.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);

     

                context.SaveChanges();
    =======================================================================================

     

    If you have any problems, please feel free to let me know.

     

    Hope you have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Friday, October 9, 2009 9:17 AM

All replies

  • Hi Howard,

     

    Welcome to ADO.NET Entity Framework and LINQ to Entities forum!

     

    Is the PageNumber the primary key of the Page object?  If so, I think the pageNo which is started from zero are duplicate with the original object primary keys.  When calling SaveChanges, the ObjectContext detects there are duplicate primary keys among the objects it is tracking.

     

    One workaround would be first updating some entity objects and then deleting or inserting other entities based on the length of the pagesToDelete and pageList collections.  Here I assume that the original page objects has a primary key started from zero.

     

    Here I tested on a parent table Category and a child table Section. 

    =======================================================================================
                MyNewDatabaseEntities context = new MyNewDatabaseEntities();

     

                var c = context.Category.Include("Section").First();

                var list = c.Section.ToList();

     

                List<string> sectionNames = new List<string>() { "SectionName1", " SectionName2", " SectionName3" };

                int index = 0;

                if (list.Count >= sectionNames.Count)

                {

     

                    foreach (var s in list.Take(sectionNames.Count))

                    {

                        s.SectionName = sectionNames[index++];

                    }

                    foreach (var s in list.Skip(sectionNames.Count))

                    {

                        context.DeleteObject(s);

                    }

                }

                else

                {

                    foreach (var s in list)

                    {

                        s.SectionName = sectionNames[index++];

                    }

                    for (int i = index; i < sectionNames.Count; i++)

                    {

                        Section newS = new Section();

                        newS.SectionID = i;

                        newS.SectionName = sectionNames[i];

                        c.Section.Add(newS);

                    }

                }

     

                var deleted = context.ObjectStateManager.GetObjectStateEntries(EntityState.Deleted);

                var added = context.ObjectStateManager.GetObjectStateEntries(EntityState.Added);

                var modified = context.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);

     

                context.SaveChanges();
    =======================================================================================

     

    If you have any problems, please feel free to let me know.

     

    Hope you have a nice weekend!

     

     

    Best Regards,
    Lingzhi Sun



    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

    Friday, October 9, 2009 9:17 AM
  • Hi Howard,

     

    I am writing to check the status of the issue on your side.  Would you mind letting me know the result of the suggestions? 

     

    If you need further assistance, please feel free to let me know.   I will be more than happy to be of assistance.

     

    Have a nice day!

     

     

    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Tuesday, October 13, 2009 9:52 AM