Transactions in EF Core and Dapper

Entity Framework Core (EF Core) provides robust support for transactions, which are crucial for maintaining data integrity, especially in scenarios involving multiple operations that must either all succeed or all fail together.

An example: Retail Order Processing

Imagine we’re building an online retail system where customers can place orders for various products. The order processing involves several steps that must be completed as a single unit of work. If any step fails, the entire operation should be rolled back to maintain data consistency.

Steps involved in Order Processing:

1. Update Inventory: Deduct the quantity of ordered items from the inventory.
2. Create Order Record: Record the customer’s order details in the database.
3. Update Customer Points: Increase the customer’s loyalty points based on the order value.
4. Payment Processing: Charge the customer’s payment method.

Implementation using EF Core Transactions:

using (var context = new RetailDbContext())
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            // Step 1: Update Inventory
            var product = context.Products
              .FirstOrDefault(p => p.Id == orderedProductId);
            if (product == null || product.Stock < orderedQuantity)
            {
                throw new Exception("Product not available in sufficient quantity.");
            }
            product.Stock -= orderedQuantity;
            context.SaveChanges();

            // Step 2: Create Order Record
            var order = new Order { /* ... set order details ... */ };
            context.Orders.Add(order);
            context.SaveChanges();

            // Step 3: Update Customer Points
            var customer = context.Customers
              .FirstOrDefault(c => c.Id == customerId);
            if (customer != null)
            {
                customer.LoyaltyPoints += CalculatePoints(order.TotalAmount);
                context.SaveChanges();
            }

            // Step 4: Payment Processing
            // ... perform payment processing, throw exception if it fails ...

            // If all steps succeed, commit the transaction
            transaction.Commit();
        }
        catch (Exception ex)
        {
            // If any step fails, roll back the entire transaction
            transaction.Rollback();
            // Log error or handle it as required
        }
    }
}

Key Points

• Atomicity: The entire operation is atomic. If any step fails (like payment processing fails or inventory is insufficient), the transaction is rolled back, leaving the database in a consistent state.
• Explicit Transaction Management: Instead of relying on the implicit transactions of SaveChanges, this scenario demonstrates explicit transaction management where you have fine-grained control over what constitutes a transaction.
• Real-World Relevance: This example closely resembles real-world scenarios in e-commerce systems, where multiple related database operations must succeed or fail as a unit.

This approach ensures that your application handles complex operations involving multiple steps in a reliable and consistent manner, leveraging the power of EF Core’s transaction management.

Implicit Nested Transations

In the provided example, each call to SaveChanges() indeed creates its own transaction. However, when these calls are wrapped inside an explicit transaction created with context.Database.BeginTransaction(), EF Core handles this gracefully.

Understanding Nested Transactions in EF Core

• Implicit Transactions: EF Core implicitly creates a transaction for each SaveChanges() call if there isn't an existing transaction. This ensures that the changes made in that call are committed atomically.
• Explicit Transactions: When you manually begin a transaction using context.Database.BeginTransaction(), you're creating an explicit transaction. All subsequent operations that call SaveChanges() before the transaction is committed or rolled back will be part of this explicit transaction.
• Nested Transaction Behavior: In databases that support true nested transactions, each inner transaction is independent. However, many relational databases, including popular ones like SQL Server, do not support true nested transactions. In these cases, when EF Core detects that an operation is already within a transaction, it doesn’t create a new one but instead enlists the operations in the existing transaction. This means that within the scope of an explicit transaction, the implicit transactions created by SaveChanges() are effectively just part of the outer transaction.
• Rollback and Commit: If an error occurs in any of the SaveChanges() calls within the explicit transaction block, you can roll back the entire transaction, undoing all changes made within that transaction scope. Conversely, if all operations succeed, committing the transaction will persist all changes.

Implications

• Safety: There’s no risk of unwanted nested transactions in this scenario. EF Core and the underlying database manage the transaction scope to ensure that all operations are either committed or rolled back together.
• Performance Considerations: While using multiple SaveChanges() calls within a transaction is safe, be aware that each call is a round trip to the database. In performance-critical applications, you might want to minimize these calls.

Considerations

In summary, using SaveChanges() within an explicit transaction in EF Core is safe and behaves as expected: the changes are all part of the single, explicitly started transaction. This pattern is commonly used for ensuring atomicity of complex operations involving multiple steps.

Nested Transactions in other databases

Finding a database that natively supports nested transactions can be a bit challenging because many popular relational database management systems (RDBMS) do not offer this feature. Nested transactions, also known as subtransactions or savepoints, allow for portions of a larger transaction to be rolled back without affecting the entire transaction.

One example of a database system that supports nested transactions is PostgreSQL. In PostgreSQL, you can use savepoints to create points within a transaction that you can roll back to, without rolling back the entire transaction. This allows for a form of nested transaction control.

Here’s how it works in PostgreSQL:

Begin your main transaction:

BEGIN;

Create a savepoint at a certain point within your transaction:

SAVEPOINT savepoint_name;

then execute some database operations.

If something goes wrong, you can roll back to the savepoint without affecting the entire transaction:

ROLLBACK TO SAVEPOINT savepoint_name;

Finally, you can either commit the entire transaction or roll it back.

COMMIT;  -- or ROLLBACK;

It’s important to note that while PostgreSQL supports this feature, the concept of nested transactions in the traditional sense (where each nested transaction is completely independent) is not universally supported or implemented in the same way across different database systems.

In systems that do not support nested transactions, the typical approach is to use transaction management features provided by the application framework (like EF Core) or implement business logic to handle partial rollbacks and error recovery.

Using transactions with Dapper

When using Dapper you need to explicitly manage transactions, especially if your operation involves multiple steps that need to be executed as a single atomic unit.

Dapper, as a micro ORM, doesn’t handle transactions automatically. You must explicitly start, commit, and roll back transactions. Here’s an example of how you might manage transactions with Dapper:

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using (var transaction = connection.BeginTransaction())
    {
        try
        {
            // Perform your database operations
            connection.Execute(sql1, param1, transaction: transaction);
            connection.Execute(sql2, param2, transaction: transaction);            
            // Commit transaction if all operations are successful
            transaction.Commit();
        }
        catch
        {
            // Rollback transaction in case of an error
            transaction.Rollback();
            throw;
        }
    }
}

Dapper.Transaction Nuget Package

In the world of Dapper there is also a library called Dapper Transaction: https://dappertutorial.net/dapper-transaction

Dapper Transaction supports all the methods that are supported by Dapper. The only difference is that these methods can be called from any object of type IDbTransaction instead of from an object of type IDbConnection.

Let’s consider the following example that shows how to call the extension method.

using (var connection = new SqlConnection(connectionString))
{
	connection.Open();
	
	using (var transaction = connection.BeginTransaction())
	{
		// Dapper Transaction extension
		var affectedRows1 = transaction.Execute(sql, 
                  new {CustomerName = "Mark"});		
                // You can also use traditional Dapper transaction
		var affectedRows2 = connection.Execute(sql, 
                  new {CustomerName = "Mark"}, transaction: transaction);
		
		transaction.Commit();
	}
}

Using Raw SQL with EF Core

When using raw SQL commands with EF Core, while SaveChanges() does handle transactions implicitly for its changes, you should manage transactions explicitly if your operation involves multiple raw SQL commands. This can be done similarly to how you handle transactions in Dapper:

using (var context = new YourDbContext())
{
    using (var transaction = context.Database.BeginTransaction())
    {
        try
        {
            // Execute raw SQL commands
            context.Database.ExecuteSqlRaw("SQL COMMAND HERE");
            // Commit transaction if all operations are successful
            transaction.Commit();
        }
        catch
        {
            // Rollback transaction in case of an error
            transaction.Rollback();
            throw;
        }
    }
}

Dapper & raw SQL key points:

• Explicit Transaction Management: Both Dapper and raw SQL in EF Core require explicit transaction management for complex operations involving multiple database interactions.
• Error Handling: The use of try-catch blocks to catch any exceptions, roll back the transaction if an error occurs, and rethrow the exception when necessary are crucial for correct error handling.
• Consistency: Ensure that all related database modifications either succeed or fail as a unit to maintain data integrity.

Managing transactions explicitly provides you with more control over your database operations, particularly when dealing with complex scenarios that EF Core’s implicit transaction handling for SaveChanges() does not cover.