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

entity-framework

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.

Best Answer

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

Related Topic