Delete from multiple Tables in Transaction

Question

I would like to delete from multiple tables using telerik open access within the one transaction - so if there is a problem with any of the deletions, they all roll back.

This is the code I have come up with after reading documentation, however I suspect that each 'DeleteAll' is running a seperate transaction rather than waiting for the 'SaveChanges()' at the end. Is that true? If so, how else can I accomplish what I am trying to do (i.e. all deletes in the one transaction)?

int deleted = 0;
using (PortalContext dbContext = new PortalContext())
{
    var bars = dbContext.GetAll<xxx>().Where(x => x.a == user.a && x.b == b && x.c >= sessionStart);
    deleted += bars.DeleteAll();
    var badss = dbContext.GetAll<yyy>().Where(x => x.a == user.a && x.b == b && x.c >= sessionStart);
    deleted += badss.DeleteAll();
    var bads = dbContext.GetAll<zzz>().Where(x => x.a == user.a && x.b == b && x.c >= sessionStart);
    deleted += bads.DeleteAll();
    var trades = dbContext.GetAll<aaa>().Where(x => x.a == user.a && x.b == b && x.c >= fromTime);
    deleted += trades.DeleteAll();
    var balances = dbContext.GetAll<bbb>().Where(x => x.a == user.a && x.b == b && x.c >= fromTime);
    deleted += balances.DeleteAll();

    dbContext.SaveChanges();
}

Answer 1

Your suspicion is correct. DeleteAll executes immediately in a separate transaction and does not wait for SaveChanges. Therefore you will have to use the normal Delete method. You will have to get the objects you wish to delete and then iterate through them calling Delete for each one and then SaveChanges at the end:

using (EntitiesModel context = new EntitiesModel())
{
    var rentalOrdersToDelete = context.RentalOrders.Where(order => order.RentalOrderID < 10);
    var carsToDelete = context.Cars.Where(car => car.CarID < 5);
    foreach (RentalOrder order in rentalOrdersToDelete)
    {
        context.Delete(order);
    }
    foreach (Car car in carsToDelete)
    {
        context.Delete(car);
    }
    context.SaveChanges();
}

Note that the similar SQL statements that will be generated will also be batched together so performance should be good.

Also, if you are using a long living context object for various other operations besides this multiple delete, I would recommend to make sure that there are no other pending changes in the context (there is no open transaction) so that you would not secretly push anything else to the database except the deletes when you call SaveChanges. You can use the context.HasChanges property to check for that.