Entity Framework Core - Transaction & Rollback

Transactions are the mechanism that ensures data integrity: they guarantee that a series of database operations either all succeed or all fail (Atomicity).

In Entity Framework Core (EF Core), transaction management ranges from fully automatic to highly manual control.

---

1. Default Behavior: Implicit Transactions

By default, EF Core operates in an "implicit transaction" mode.

• How it works: When you call SaveChanges(), EF Core automatically creates a transaction, executes all pending INSERT, UPDATE, and DELETE commands, and then commits.

• Result: If any single command fails (e.g., a constraint violation), the database rolls back all changes made during that specific SaveChanges() call.

Example:

// No manual transaction needed here
using var context = new AppDbContext();

context.Customers.Add(new Customer { Name = "Alice" });
context.Orders.Add(new Order { CustomerId = 1, Total = 50 });

// EF Core wraps these two inserts in a single transaction automatically.
context.SaveChanges();

---

2. Explicit Transactions (Manual Control)

You need explicit transactions when you want to group multiple SaveChanges() calls or combine EF Core operations with raw SQL commands into a single atomic unit.

Standard Pattern: Begin, Commit, Rollback

This uses DbContext.Database.BeginTransaction().

Code Example:

using var context = new AppDbContext();

// 1. Start the transaction
using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Operation 1
    context.Customers.Add(new Customer { Name = "Bob" });
    await context.SaveChangesAsync(); // Saved to DB but not committed

    // Operation 2: Maybe some logic depends on the ID generated above
    var specialLog = new AuditLog { Message = "Added Bob" };
    context.AuditLogs.Add(specialLog);
    await context.SaveChangesAsync();

    // 3. Commit: Make changes permanent
    await transaction.CommitAsync();
}
catch (Exception)
{
    // 4. Rollback (Optional in strict "using" blocks, but good for clarity)
    // If CommitAsync() is not called, the "using" block will auto-rollback on Dispose.
    await transaction.RollbackAsync();
    throw;
}

---

3. Ambient Transactions (TransactionScope)

TransactionScope is a powerful .NET feature that allows you to handle transactions declaratively. It can implicitly enlist multiple database contexts (or even different database connections) into a single transaction.

Critical Requirement: You must use TransactionScopeAsyncFlowOption.Enabled if you are using async/await.

Code Example:

using System.Transactions;

// Ensure AsyncFlowOption is Enabled for async code!
using (var scope = new TransactionScope(
    TransactionScopeOption.Required,
    new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted },
    TransactionScopeAsyncFlowOption.Enabled)) 
{
    using var context1 = new AppDbContext();
    context1.Customers.Add(new Customer { Name = "Charlie" });
    await context1.SaveChangesAsync();

    using var context2 = new AppDbContext(); // Can be a completely different Context
    context2.Orders.Add(new Order { OrderName = "Order for Charlie" });
    await context2.SaveChangesAsync();

    // The transaction is committed here. 
    // If we exit this block without calling Complete(), everything rolls back.
    scope.Complete(); 
}

---

4. Savepoints (Partial Rollbacks)

Savepoints allow you to rollback a transaction to a specific point without aborting the entire transaction. This is useful for "soft fails" where you want to retry or ignore a specific error but keep previous progress.

Code Example:

using var transaction = await context.Database.BeginTransactionAsync();

try
{
    // Step 1: Crucial Data
    context.Add(new User { Name = "Dave" });
    await context.SaveChangesAsync();

    // Create a Savepoint
    await transaction.CreateSavepointAsync("BeforeOptionalLog");

    // Step 2: Optional Data (might fail)
    context.Add(new Log { Message = "User Created" });
    await context.SaveChangesAsync();

    await transaction.CommitAsync();
}
catch (Exception)
{
    // Rollback ONLY to the savepoint. "Dave" is kept, "Log" is discarded.
    await transaction.RollbackToSavepointAsync("BeforeOptionalLog");

    // Commit the successful parts
    await transaction.CommitAsync();
}

---

5. Corner Cases & "Gotchas"

A. The "AsyncFlowOption" Trap

If you use TransactionScope with async code but forget TransactionScopeAsyncFlowOption.Enabled, the transaction context may effectively "detach" from the thread after an await. The database operations after the await might execute outside the transaction or throw an exception.

B. Distributed Transactions (Cross-Database)

• Windows: TransactionScope naturally supports distributed transactions (via MSDTC) if you open connections to two different database servers.

• Linux/Mac (.NET Core): Distributed transactions are generally not supported natively by the platform in the same way. Trying to open two different DB connections inside one TransactionScope on Linux may throw a PlatformNotSupportedException.

• Solution: For cross-platform support, share a single DbConnection object between multiple DbContext instances if they target the same database.

C. Database Execution Strategies (Retries)

If you enable connection resiliency (e.g., EnableRetryOnFailure), explicit transactions (BeginTransaction) verify that you are not manually handling retries incorrectly.

• The Issue: You cannot simply wrap BeginTransaction in a try-catch if the provider is also trying to retry automatically; the ID logic gets confused.

• The Fix: You must use an Execution Strategy.

var strategy = context.Database.CreateExecutionStrategy();

await strategy.ExecuteAsync(async () =>
{
    using var transaction = await context.Database.BeginTransactionAsync();

    // Your transactional logic here...
    context.Users.Add(user);
    await context.SaveChangesAsync();

    await transaction.CommitAsync();
});

D. Nested Transactions (Anti-Pattern)

Relational databases generally do not support true nested transactions (starting a transaction inside another).

• If you call BeginTransaction while another is active on the same connection, EF Core/Database will usually throw an error or treat it as a savepoint (depending on provider).

• Best Practice: Use TransactionScope if you need nesting semantics; it manages the "flattening" of transactions automatically.

---

6. Best Practices Summary