Error of transaction stuck in previous delete (I think at least)

Question

I'm having a problem which happens when i try to delete a few registers from the database.

The problem is when one of those people that I'm trying to delete have an bill registered or another thing that I can't delete. Occurs error on 'SaveChenges()' because people have reference on another table, and will catch my exception and supposedly try to delete the next 'people', BUT in this next people occurs the same error, and in the 'Exception.InnerException' that shows me, there is the same Id of my previous 'people', and in this way my register are never deleted, unless no one of those peoples that I'm trying to delete occurs an error, or I'm trying to delete one by one for another route ("which uses the same 'DeletePeople(People people)'")

public IActionResult DeletePeople(long[] ids){
    var peoples = _peopleRepository.GetPeoples(ids);

    var nonDeleted = 0;  

    foreach (var people in peoples){
        var deleted = DeletePeople(people);

        if(!deleted)
           nonDeleted++;
    }

     return Json(new { nonDeleted, total = peoples.Count() });
}

public bool DeletePeople(People people){
   var transaction = _peopleRepository.BeginTransaction();

   try{
       _contactsPeopleRepository.RemoveRange(people.Contacts);

       _contactRepository.RemoveRange(people.Contacts.Select(x => x.Contact))

       if(people.Client != null)
           _clientRepository.Remove(people.Client);

       //
       Remove a few other things witch have reference with 'people' at the same way
       //

       _peopleRepository.Remove(people)
       _peopleRepository.SaveChanges();

       transaction.Commit();

       return true;
   }
   catch{
       transaction.Rollback();

       return false;       
   }
}

Below is my 'GetPeoples' on the repository.

public IEnumerable<People> GetPeoples(long[] ids){
    return GetAll()
        .Include(x => x.Client)
        //Here there are a few more includes that I try to remove
        .Where(x => ids.Contains(x.Id))
}

(My English is not very good, but I tried my best to describe my problem)

I tried to use asynchronous methods, and get the peoples from my database one by one inside my method 'DeletePeople(People people)', which in this case use to receive just and 'id', but nothing seems to work. And also, I tried to research if someone else already have this problem and found nothing :(

Answer 1

The issue will be most likely due to a "poisoned" DbContext in your Repository. If a DB operation fails then the DbContext change tracking needs to be flushed. The DbContext behind your repositories change tracking remembers everything you do, regardless of whether a transaction was rolled back or not. Normally the quick & dirty fix would involve Disposing the DbContext and re-creating it. However when you have several repositories with the same reference managed by an IoC container that's not the most straight forward.

See: DbContext discard changes without disposing I recommend cheching out Gary Pendlebury's updated solution down near the end.

For other references, you should be checking to see whether a Person can be deleted or not based on usage before attempting to delete, no just kicking off a delete and hoping for no exception. Exception handling should be the exception, not a normal program flow.

So for instance, before deleting a Person:

if (!PersonRepository.PersonIsInUse(personId))
    PersonRepository.Delete(personId);
else
    // TODO: Log/present a suitable message.

Where PersonInUse does a .Any() check against any and all entities that reference the Person record.

This is typically a driver to consider soft-delete systems where instead of Deleting important records like People which will be potentially referenced and needed for historical sake, you use a flag on each record for something like IsActive. To remove a Person so they will not show up in further query results etc. you would set IsActive = False and all queries would be written to ignore IsActive. (EF can also support global filters like this as well) This way you don't risk invalid relationships when you try to remove a record.

For parent-child "owned" relationships like People & Contacts should be set up in the database to perform a cascade delete rather than coding explicit deletes for related entities.