How to use entity framework transaction in raw query?

Question

I am using entity framework but doing my operations with raw queries. My operations are like following:

1. Check if recırd exist with integration_id
2. Delete record if exit
3. Insert new record

So I am using transaction

using (var transaction = await _context.Database.BeginTransactionAsync())
{ 
        var isExist = await IsExist(id);

        if (isExist)
        {
              var deleteQuery = "delete from ....";
              await _context.Database.ExecuteSqlRawAsync(deleteQuery);
        }

        var insertQuery = "insert into ...";
        await _context.Database.ExecuteSqlRawAsync(insertQuery);
}

if insert operation fails, does deleted record rollback?

Answer 1

UPD: https://learn.microsoft.com/en-us/ef/core/saving/transactions#controlling-transactions

transaction will auto-rollback when disposed if either commands fails

So, my code below may be overkill on the catch side, but Commit is still essential :)

======================

I believe the correct way of using transaction would be following:

using (var transaction = await _context.Database.BeginTransactionAsync())
{
    try
    {
        var isExist = await IsExist(id);

        if (isExist)
        {
            var deleteQuery = "delete from ....";
            await _context.Database.ExecuteSqlRawAsync(deleteQuery);
        }

        var insertQuery = "insert into ...";
        await _context.Database.ExecuteSqlRawAsync(insertQuery);

        // there we tell DB to finish the transaction,
        // mark all changes as permanent and release all locks
        transaction.Commit();
    }
    catch (Exception ex)
    {
        // there we tell DB to discard all changes
        // made by this transaction that may be discarded
        transaction.Rollback();
        // log error
    }
}

But I never used BeginTransaction*Async* personally before.

Answer 2

This method doesn't start transaction on it's own. If you need to execute queries in transaction you need to first call

BeginTransaction(DatabaseFacade, IsolationLevel) or UseTransaction.

Reference learn.microsoft.com

So in your case it will execute queries in a transaction and roll back all the queries if any of the query failed