How should I handle this Optimistic Concurrency error in this Entity Framework code, I have?

Question

Hi folks,

I have the following pseduo code in some Repository Pattern project that uses EF4.

public void Delete(int someId)
{
   // 1. Load the entity for that Id. If there is none, then null.
   // 2. If entity != null, then DeleteObject(..);
}

Pretty simple but I'm getting a run-time error:-

ConcurrencyException: Store, Update, Insert or Delete statement affected an unexpected number of rows (0).

Now, this is what is happening :-

1. Two instances of EF4 are running inthe app at the same time.
2. Instance A calls delete.
3. Instance B calls delete a nano second later.
4. Instance A loads the entity.
5. Instance B also loads the entity.
6. Instance A now deletes that entity - cool bananas.
7. Instance B tries to delete the entity, but it's already gone. As such, the no-count or what not is 0, when it expected 1 .. or something like that. Basically, it figured out that the item it is suppose to delete, didn't delete (because it happened a split sec ago).

I'm not sure if this is like a race-condition or something.

Anyways, is there any tricks I can do here so the 2nd call doesn't crash? I could make it into a stored procedure.. but I'm hoping to avoid that right now.

Any ideas? I'm wondering If it's possible to lock that row (and that row only) when the select is called ... forcing Instance B to wait until the row lock has been relased. By that time, the row is deleted, so when Instance B does it's select, the data is not there .. so it will never delete.

Answer 1

Normally you would catch the OptimisticConcurrencyException and then handle the problem in a manner that makes sense for your business model - then call SaveChanges again.

try
{
    myContext.SaveChanges();
}
catch (OptimisticConcurrencyException e)
{
    if (e.StateEntries.FirstOrDefault() is DeletingObject)
        myContext.Detach(e.StateEntries.First();
    myContext.SaveChanges();
}

Give that a whirl and see how you get on - not tested/compiled or anything - I've used DeletingObject as the type of the entity you're trying to delete. Substitute for your entity type.