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

score 3 · Answer 1 · answered Aug 25 '14 at 14:53

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.

Ok, but if i loop through and delete each item, won't that end up creating a HUGE sql statement behind the scenes especially if there are a lot of rows being deleted? i.e. definitely won't be a simple sql like -> delete * from aaa where c > sessionstart — DaManJ, Aug 26 '14 at 05:22
The code described above will generate a parametrized sql statement for each type that is deleted. The parameter will be the identity of each object that needs to be deleted. Basically with the above approach a single per type statement will be executed multiple times in a single transaction: _DELETE FROM [RentalOrders] WHERE [RentalOrderID] = p0 9 param rows DELETE FROM [Cars] WHERE [CarID] = p0 4 param rows_ Note that [Statement Batching](http://goo.gl/h1jz7e) needs to be enabled in your model (it is enabled by default). — BitOffender, Aug 27 '14 at 06:41

Delete from multiple Tables in Transaction

1 Answers1