Retry Entity Framwork DbContext.SaveChanges after double inserting a key

Question

I am using Entity Framework on Azure. After makes a few changes to my data I call the DbContext.SaveChanges .

In my code, Messages belong to Conversations. On receiving a message I add it to the Messages table and create its parent in the Conversations table. If I get two messages come in at once with the same parent, there is a possibility of double inserting values with the same primary key into the Conversations table.

The process:

I'll call some "insert conversation if it doesn't exist code":

 if (this.Context.Conversations.SingleOrDefault(fc => fc.ConversationId == conversation.ConversationId) == null)
        {
            Context.Entry(conversation).State = EntityState.Added; //public DbEntityEntry Entry(object entity);
        }

Later on I'll call this.Context.SaveChanges

I've been reading SQL Azure and Entity Framework Connection Fault Handling and I'm pretty sure I want to implement a Retry Policy With Transaction Scope.

How do I go about wrapping SaveChanges in a retry policy Some thoughts: How do I go about wrapping SaveChanges in a retry policy. Will this retry the code I ran earlier that checked if the conversation existed? How do I make my retry policy retry for non-transient faults (like PK violations)?
If a retry policy isn't possible , what is the entity framework approach to burying the "create or edit if exists" logic as far down as possible when saving a context? Is it best to just call a stored procedure in this special case?

Please try to ask one question per question. This is well written, but has been flagged as "Too Broad". — BradleyDotNET, Dec 15 '14 at 18:36
@BradleyDotNET Yeah, I'm not sure I can remove them without removing context for the answers. I've buried some of the questions. The core question is "how do I do this", I just had gone mad with bullet points. — Nathan Cooper, Dec 15 '14 at 22:02
No worries, I didn't VTC (looks like no one else has either). Just something you should be aware of. — BradleyDotNET, Dec 15 '14 at 22:04
Thanks for everyone's help. I the end, I used `Database.ExecuteSqlCommand` to fire off a stored procedure during `SaveChanges` — Nathan Cooper, Dec 16 '14 at 15:28

score 10 · Accepted Answer · answered Dec 15 '14 at 19:21

10

It sounds more like a concurrency retry pattern that you want:

using (var context = new BloggingContext()) 
{ 
    var blog = context.Blogs.Find(1); 
    blog.Name = "The New ADO.NET Blog"; 

    bool saveFailed; 
    do 
    { 
        saveFailed = false; 

        try 
        { 
            context.SaveChanges(); 
        } 
        catch (DbUpdateConcurrencyException ex) 
        { 
            saveFailed = true; 

            // Update the values of the entity that 
            //failed to save from the store 
            ex.Entries.Single().Reload(); 
        } 

    } while (saveFailed); 
}

Reference:

http://msdn.microsoft.com/en-gb/data/jj592904.aspx

answered Dec 15 '14 at 19:21

Colin

22,328
17
103
197

Thanks Colin, much appreciated. I was under the understanding that you couldn't just repeat SaveChanges - [Can I continue using DbContext after it has thrown an exception?](http://stackoverflow.com/questions/19108680/can-i-continue-using-dbcontext-after-it-has-thrown-an-exception). Does your "ex.Entries..." line deal with it? If so do I need to know the nature of the changes I've made when i write that? – Nathan Cooper Dec 15 '14 at 21:55
The answer over there actually says "If you don't supply the `DbContext` with an already opened SQL connection, the `DbContext` will open and close the connection for you when you call `SaveChanges`. In that case there is no danger in keeping the `DbContext` around". There is a lot of discussion of how to handle a `DbUpdateException` in the case of a deadlock when you supply an open connection to the `DbContext` - which you cannot do in EF6. `DbUpdateConcurrencyExceptions` are raised by EF so you will be nowhere near that edge case. More concurrency: http://stackoverflow.com/a/27013719/150342 – Colin Dec 16 '14 at 09:50
This did work. But unfortunately I would have had to refactor my context common code to account for a special case, have some way of checking it was a PK violation, accounted for multiple entries etc etc. Incidentally it was just a DbUpdateException, because the race was in the code rather than the db, but that doesn't really matter. – Nathan Cooper Dec 16 '14 at 21:24

score 4 · Answer 2 · answered Dec 15 '14 at 18:16

Sadly - you do not. That is not within the scope of what EF can do out of the box. The retry logic explicitly only handles transient connection issues. And there is no retry logic that magically fixes PK issues. There also is no "upsert" (update or insert) logic at all in EF.

You are on your own here - no sense to even ask the devs as they are busy with EF 7 and even drop features temporarily to make that happen (like inheritance). Maybe in a year or two, once EF has more functionality and is stable in v7... but no, nothing there at the moment.

You will have to sort that out in your own application logic.

Retry Entity Framwork DbContext.SaveChanges after double inserting a key

2 Answers2