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

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

Here is the working code with two calls to SaveChanges:

        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

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.

Answer 1

I think a more likely explanation is that EF does do the deletes, but probably it does them after the insert, so you end up passing through an invalid state.

Unfortunately you don't have low level control over the order DbCommands are executed in the database.

So you need two SaveChanges() calls.

One option is to create a wrapping TransactionScope.

Then you can call SaveChanges() twice and it all happens inside the same transaction.

See this post for more information on the related techniques

Hope this helps

Alex

Answer 2

Thank you Alex. This is very interesting. I did indeed decide to wrap the whole thing up in a transaction scope and it did work fine with the two SaveChanges() -- which, as you point out, appear to be needed due to the primary key conflict with the deletes and subsequent inserts. A new issue now arises based on the article to which you linked. It properly advises to call SaveChanges(false) -- instructing EF to hold onto it's changes because the outer transaction scope will actually control whether those changes ever actually make it to the database. Once the controlling code calls scope.Complete(), the pattern is to then call EF's context.AcceptAllChanges(). But I think this will be problematic for me because I'm forced to call SaveChanges TWICE for the problem originally described. If both those calls to SaveChanges specify False for the accept changes parameter, then I suspect the second call will end up repeating the SQL from the first. I fear I may be in a Catch-22.