I must do these 3 operations in sequence over a database:

1. Truncate final table
2. Migrate data from start table to final table
3. Truncate start table

I want to wrap these operations in a single transaction, because i want to execute a rollback if anything goes wrong.

I did something like the following:

using(var transaction = _context.Database.BeginTransaction())
{
    try {
        await _context.Database.ExecuteRawQueryAsync("truncate...");
        await _context.Database.ExecuteRawQueryAsync("insert into ... select from ...");
        await _context.Database.ExecuteRawQueryAsync("truncate...");

        await transaction.CommitAsync();
    }
    catch(...) {
        await transaction.RollBackAsync();
    }
}

I think that this isn't the right way to do this because i can see that db tables get modified before the code reaches the commit line. I even tried to execute the first truncate, launch a new exception and execute the rollback but data didn't get restored.

Ps: i can't use stored procedures.

Thank you for your time :)