Entity Framework Core - Transactions & Resiliency

The combination of Transactions and Retry Logic (Connection Resiliency) is one of the most complex areas in EF Core. If you blindly enable retries (EnableRetryOnFailure), standard transaction code will crash.

Here is the comprehensive documentation and code for handling retries correctly using Execution Strategies.

---

The Problem: Why do we need this?

When you enable automatic retries (e.g., for Azure SQL), EF Core can automatically retry failed read/write commands. However, transactions are stateful.

If a transient error (network blip) occurs inside a transaction:

1. The database rolls back the transaction entirely.

2. EF Core cannot simply "resume" the transaction from line 3; it must restart the entire logic block from the beginning.

3. EF Core blocks you from creating a transaction inside a retryable context unless you explicitly verify you are handling the restart logic.

1. Configuration: Enabling Resiliency

First, ensure your DbContext is configured to retry on transient failures.

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer(
        "YourConnectionString",
        sqlOptions =>
        {
            // Enable automatic retries (e.g., max 5 retries, max 30 sec delay)
            sqlOptions.EnableRetryOnFailure(
                maxRetryCount: 5, 
                maxRetryDelay: TimeSpan.FromSeconds(30), 
                errorNumbersToAdd: null);
        });
}

---

2. The Implementation: "Bank Transfer" Example

This example handles a money transfer. This is the Gold Standard pattern. It handles:

1. Transaction creation

2. Automatic retries via Execution Strategy

3. Idempotency (Preventing double-charging if a retry happens)

public async Task TransferFundsAsync(int fromAccountId, int toAccountId, decimal amount)
{
    using var context = new BankingContext();

    // 1. Get the Execution Strategy (The Retry Logic Manager)
    var strategy = context.Database.CreateExecutionStrategy();

    // 2. Execute the entire logic inside the strategy
    await strategy.ExecuteAsync(async () =>
    {
        // EVERYTHING inside this block is retried if a failure occurs.
        // Therefore, we must start the transaction INSIDE this block.

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

        try
        {
            // --- CRITICAL CORNER CASE: IDEMPOTENCY ---
            // If the transaction fails AFTER 'SaveChanges' but BEFORE 'Commit' is confirmed,
            // the retry logic runs this block again. 
            // We must ensure we don't deduct the money twice.

            var fromAccount = await context.Accounts.FindAsync(fromAccountId);

            // Check if we already processed this transfer in a previous (failed) attempt
            // Example: We might check a 'TransferLog' or the account balance logic
            // (For this example, we assume purely transactional logic, but in real apps
            // you often query a 'ProcessedTransactionId' table here).

            var toAccount = await context.Accounts.FindAsync(toAccountId);

            // Business Logic
            if (fromAccount.Balance < amount)
                throw new InvalidOperationException("Insufficient funds");

            fromAccount.Balance -= amount;
            toAccount.Balance += amount;

            // Save Changes (Updates rows in memory/DB transaction log)
            await context.SaveChangesAsync();

            // Commit Transaction
            await transaction.CommitAsync();
        }
        catch (Exception)
        {
            // Rollback is automatic on Dispose if Commit isn't called,
            // but calling it explicitly is safe.
            await transaction.RollbackAsync();

            // We MUST throw the exception so the Execution Strategy knows 
            // something failed and can decide whether to Retry or abort.
            throw;
        }
    });
}

---

3. Detailed Corner Cases of Retry

Here is exactly what happens during different failure points in the code above.

Case A: Failure during Find or Calculation (Before SaveChanges)

• What happens: Connection drops while fetching fromAccount.

• Behavior: The strategy catches the exception. It waits (backoff) and restarts the lambda function from the top.

• Result: A new transaction is started. Data is fetched again. Safe.

Case B: Failure during SaveChanges

• What happens: You send the UPDATE SQL, but the DB is momentarily locked or unavailable.

• Behavior: SaveChanges throws a transient exception. The strategy catches it, rolls back the uncommitted transaction, and restarts the lambda.

• Result: Logic runs again. Safe.

Case C: The "Phantom Commit" (Failure during CommitAsync)

• The Scenario: This is the most dangerous case.

  1. Your code calls CommitAsync().

  2. The Database successfully commits the data.

  3. BUT, the network connection drops before the Database sends the "Success" acknowledgement back to your C# application.

  4. Your application thinks the Commit failed.

• The Behavior: The strategy sees a failure. It retries the block.

• The Danger: It deducts the money again (Double Charge).

• The Fix (Idempotency):

To fix Case C, you must include a mechanism to track if the operation actually finished.

Revised Idempotency Pattern:

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

    // Generate a unique ID for this specific transfer request (outside the retry loop ideally)
    var transferId = Guid.Parse("..."); 

    // CORNER CASE FIX: Check if this transfer already exists
    var existingTransfer = await context.TransferLogs
                                        .AnyAsync(x => x.RequestId == transferId);

    if (existingTransfer) 
    {
        // It was already committed in a previous attempt that "appeared" to fail.
        // Do nothing, just return.
        return; 
    }

    // ... Perform Debit/Credit Logic ...

    // Add a log record so next retry knows we did it
    context.TransferLogs.Add(new TransferLog { RequestId = transferId, ... });

    await context.SaveChangesAsync();
    await transaction.CommitAsync();
});

Summary of Best Practices for Resiliency

1. Always use CreateExecutionStrategy() when EnableRetryOnFailure is on and you need explicit transactions.

2. Make the block Idempotent. Assume the code block might run 2 or 3 times. If it runs twice, the result in the database must remain the same as if it ran once.

3. Do not capture state. Do not assume variables outside the .ExecuteAsync block are current. Query the DB fresh inside the block.