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.

Answer 1

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.