How I can avoid optimistic concurrency exception when I delete rows?

4
votes

I have a method that receive the IDs of some rows to delete. I am using a code like this:

public bool delete(IEnumerable<long> paramIeId)
{
    using(myContext)
    {
        foreach(long iterator in paramIeId)
        {
            nyDbContext.Remove(new MyType(){ID = iterator});
        }
    }
}

It works fine because delete the rows when exists. But If there area 1 or more rows that doesn't exist then I get an exception and no one rows are delete, although some of them exists.

If I do this query in T-SQL I don't have problems, the database delete the exisiting rows and igonre the no exisiting rows because at the end I want to delete them so if another process deleted them for me, no problem.

I could handle the optimistic concurrency exception refreshing the dbContextfrom database, but I think that it is to do extra queries that they could be avoid.

Is there any way that EF works like T-SQL? If I try to delete a row that doen't exists, ignore it and delete the rest of the rows.

Thanks.

2
c#optimistic-concurrencyef-core-2.0
I'm not sure it is possible to disable this behavior. EF sees that your "DELETE from" statement affected less rows than expected and throws this exception. You might better just execute raw sql query yourself in this case, without EF. – Evk
This is one of the non logical behaviors of EF. There is an open enhancement request Optimistic concurrency check should be configurable #6218 that covers that, although if you ask me, the default behavior of delete should not throw such exception. – Ivan Stoev
If paramIeId implements IEnumerable<T> then you can do foreach(long id in paramIeId.ToList()) to prevent deferred query execution done by LINQ to remove collection items at runtime. Using the .ToList() method will freeze the list while iterating over it. – Kunal Mukherjee

2 Answers

2
votes

At least for now, the exception seems unavoidable when using detached entities to perform the delete. You'll either have to use a try / catch and handle the exception or query the DB for matching id's and only delete matches1.

Sample With Exception Handling

using (myContext)
{
    foreach (long iterator in paramIeId)
    {
        nyDbContext.Remove(new MyType() { ID = iterator });
    }

    try
    {
        nyDbContext.SaveChanges()
    }
    catch(DbUpdateConcurrencyException ex)
    {
        //if you want special handling for double delete
    }
}

Sample With Query then Delete

Note that I query the entire list of types before the loop to avoid making separate queries on each type.

using (myContext)
{
    var existingMyTypes = nyDbContext.MyTypes.Where(x => paramIeId.Contains(x.ID));
    foreach (MyType existing in existingMyTypes)
    {
        nyDbContext.Remove(existing);
    }

    nyDbContext.SaveChanges();
}

1 NOTE: The query then delete option leaves open a possible race condition which could trigger the OptimisticConcurrencyException you're trying to - namely, if another process / thread / program deletes the rows between your own processes's read and delete. The only way to completely handle that possibility is by handling the exception in a try / catch.

2
votes

You don't need to create a new object to delete it, just let EF handle everything for you:

public bool delete(IEnumerable<long> paramIeId)
{
    using(var nyDbContext = new DbContext())
    {
        foreach(long id in paramIeId)
        {
            MyType myType = nyDbContext.MyTypes.FirstOrDefault(x => x.ID == id);
            if (myType != null)
            {
                nyDbContext.MyTypes.Remove(myType);
            }
        }
        nyDbContext.SaveChanges();
     }
}