# 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.

```csharp
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)
    

```csharp
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:**

```csharp
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.