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

Question

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.

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, Nov 19 '17 at 10:54
This is one of the non logical behaviors of EF. There is an open enhancement request [Optimistic concurrency check should be configurable #6218](https://github.com/aspnet/EntityFrameworkCore/issues/6218) that covers that, although if you ask me, the default behavior of delete should not throw such exception. — Ivan Stoev, Nov 19 '17 at 11:07
If paramIeId implements IEnumerable 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, Nov 19 '17 at 13:15

Isma · Answer 1 · 2017-11-28T12:45:40.310

2

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();
     }
}

edited Nov 28 '17 at 12:45

answered Nov 19 '17 at 10:29

Isma

14,604
5
37
51

1

This answer will result in DB *queries* for each id in `paramIeId` which may significantly affect performance. The OPs code did not do any queries before doing the delete. – just.another.programmer Nov 26 '17 at 22:30
The question was not about performance but about making sure the record exists before deleting it, but you are right, if he needs to bulk delete a lot of records this is not the best solution, only the "general purpose" solution https://stackoverflow.com/questions/2519866/how-do-i-delete-multiple-rows-in-entity-framework-without-foreach – Isma Nov 26 '17 at 22:40
OP specifically said he's concerned w/ doing extra queries if not needed. – just.another.programmer Nov 26 '17 at 22:43
Three differences: 1) I highlighted the tradeoffs of using a query before delete, 2) I suggested an exception handling based approach which has no unneeded queries, 3) In the query example, I did the entire query *before* entering the foreach so it's a single query instead of many separate individual queries. – just.another.programmer Nov 26 '17 at 22:45
The extra query does not completely eliminate the exception - it only makes it much less likely. See my answer for more details. – just.another.programmer Nov 26 '17 at 22:50

score 2 · Accepted Answer · answered Nov 26 '17 at 22:42

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 matches¹.

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();
}

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

But in this case, how EF does all or nothing, if there are only one record that doesn't exists, I have to handle the exception to delete the records that still exists. — Álvaro García, Jan 08 '18 at 16:53
@ÁlvaroGarcía You're right. There's no built-in way to avoid having to re-delete on a concurrency exception without doing all the delete's separately. Note that this is not a problem with the second approach (querying the full list of existingTypes into memory then removing them). — just.another.programmer, Jan 08 '18 at 19:19

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

2 Answers2

Sample With Exception Handling

Sample With Query then Delete