Entity Framework Core Tutorial: Handling Transactions and Concurrency

This content originally appeared on DEV Community and was authored by mohamed Tayel

In this article, we will explore how to handle transactions and concurrency in Entity Framework Core (EF Core) within an API-based approach. These concepts are crucial for maintaining data consistency and managing conflicts when multiple users interact with the same data. We’ll cover:

• Defining and managing event ticket inventories with EventInventory
• Updating the EventDbContext to include EventInventory
• Running migrations
• Working with transactions
• Optimistic and pessimistic concurrency
• Handling concurrency conflicts

All examples will be API-based to ensure consistency with previous articles.

1. Defining EventInventory Entity

Let’s start by defining the EventInventory class, which will track how many tickets are available for each event.

public class EventInventory
{
    public int EventInventoryId { get; set; }
    public int EventId { get; set; }
    public int TicketCount { get; set; }

    // Navigation property for the related event
    public Event Event { get; set; }
}

• EventInventoryId: The primary key for this table.
• EventId: Foreign key that links to the Event table.
• TicketCount: The number of available tickets for the event.

This entity will allow us to manage ticket availability for each event.

2. Updating EventDbContext to Include EventInventory

Now, we need to update the EventDbContext to include the EventInventory entity so that EF Core can manage it.

public class EventDbContext : DbContext
{
    public DbSet<Event> Events { get; set; }
    public DbSet<EventInventory> EventInventory { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Event>()
            .OwnsOne(e => e.EventAddress, a =>
            {
                a.Property(ad => ad.Street).HasColumnName("Street");
                a.Property(ad => ad.City).HasColumnName("City");
                a.Property(ad => ad.PostalCode).HasColumnName("PostalCode");
            });

        modelBuilder.Entity<EventInventory>()
            .HasOne(ei => ei.Event)
            .WithMany()
            .HasForeignKey(ei => ei.EventId);

        base.OnModelCreating(modelBuilder);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("YourConnectionStringHere");
    }
}

In this DbContext:

• DbSet<EventInventory> is added to manage the EventInventory table.
• We also configure the relationship between EventInventory and Event using a foreign key (EventId), ensuring that each EventInventory entry corresponds to a specific event.

3. Running the Migration for EventInventory

To apply the changes to the database, you need to run a migration to create the EventInventory table.

Step 1: Add the Migration

Run the following command to create a migration:

dotnet ef migrations add AddEventInventory

This generates the necessary migration file that includes the schema for the EventInventory table and its relationship to the Event table.

Step 2: Apply the Migration

Run this command to apply the migration and update the database schema:

dotnet ef database update

This creates the EventInventory table in the database and establishes its foreign key relationship with the Event table.

4. Working with Transactions

Transactions in EF Core ensure that multiple database operations either succeed together or fail together. If one operation fails, the entire transaction is rolled back to keep the database in a consistent state.

Example: Using Transactions in an API

Consider an API where we create an event and update the available ticket inventory for that event. Both actions need to occur within a transaction so that if one operation fails, the other is rolled back.

[ApiController]
[Route("api/[controller]")]
public class EventController : ControllerBase
{
    private readonly EventDbContext _context;

    public EventController(EventDbContext context)
    {
        _context = context;
    }

    [HttpPost]
    [Route("CreateEventWithTransaction")]
    public async Task<IActionResult> CreateEventWithTransaction([FromBody] Event newEvent, int ticketCountToDeduct)
    {
        using (var transaction = await _context.Database.BeginTransactionAsync())
        {
            try
            {
                // Step 1: Create the event
                _context.Events.Add(newEvent);
                await _context.SaveChangesAsync();

                // Step 2: Deduct tickets from inventory
                var inventory = await _context.EventInventory
                    .FirstOrDefaultAsync(i => i.EventId == newEvent.EventId);

                if (inventory == null || inventory.TicketCount < ticketCountToDeduct)
                {
                    return BadRequest("Not enough tickets available.");
                }

                inventory.TicketCount -= ticketCountToDeduct;
                await _context.SaveChangesAsync();

                // Commit the transaction if both operations succeed
                await transaction.CommitAsync();

                return Ok(newEvent);
            }
            catch (Exception ex)
            {
                // Rollback the transaction if any operation fails
                await transaction.RollbackAsync();
                return StatusCode(500, "Transaction failed: " + ex.Message);
            }
        }
    }
}

In this example:

• A transaction is started using BeginTransactionAsync.
• The event is created, and tickets are deducted from the inventory.
• If any step fails, the transaction is rolled back to ensure data consistency.

5. Optimistic and Pessimistic Concurrency

Concurrency control helps manage conflicts when multiple users try to modify the same data at the same time. EF Core provides two types of concurrency control:

• Optimistic Concurrency: Assumes conflicts are rare and handles them when they occur.
• Pessimistic Concurrency: Locks the data during read or write operations to prevent conflicts.

Optimistic Concurrency in EF Core

In optimistic concurrency, EF Core checks if the data has been modified by another user before applying updates. This is typically done using a RowVersion or Timestamp column to detect concurrent changes.

Step 1: Add a Concurrency Token

public class Event
{
    public int EventId { get; set; }
    public string Name { get; set; }
    public DateTime Date { get; set; }

    // Concurrency token
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

Step 2: Handle Concurrency Exceptions

[HttpPut]
[Route("UpdateEvent")]
public async Task<IActionResult> UpdateEvent([FromBody] Event updatedEvent)
{
    try
    {
        _context.Events.Update(updatedEvent);
        await _context.SaveChangesAsync();
        return Ok(updatedEvent);
    }
    catch (DbUpdateConcurrencyException ex)
    {
        foreach (var entry in ex.Entries)
        {
            var databaseValues = await entry.GetDatabaseValuesAsync();
            if (databaseValues == null)
            {
                return NotFound("This event no longer exists.");
            }

            var databaseEvent = (Event)databaseValues.ToObject();
            entry.OriginalValues.SetValues(databaseValues);
            return Conflict("Concurrency conflict detected. Please refresh your data.");
        }

        return StatusCode(500, "An error occurred while handling concurrency.");
    }
}

In this example:

• We catch DbUpdateConcurrencyException when another user has modified the data.
• We retrieve the current database values and decide how to handle the conflict.

Pessimistic Concurrency in EF Core

Pessimistic concurrency locks the data during read or write operations to prevent conflicts. This is useful when conflicts are likely and need to be avoided.

Example: Pessimistic Locking in an API

[HttpGet]
[Route("GetEventForUpdate/{eventId}")]
public async Task<ActionResult<Event>> GetEventForUpdate(int eventId)
{
    var eventToUpdate = await _context.Events
        .FromSqlRaw("SELECT * FROM Events WITH (UPDLOCK) WHERE EventId = {0}", eventId)
        .FirstOrDefaultAsync();

    if (eventToUpdate == null)
    {
        return NotFound();
    }

    return Ok(eventToUpdate);
}

In this example:

• The WITH (UPDLOCK) hint locks the selected row, preventing others from modifying it while the current operation is ongoing.

6. Handling Concurrency Conflicts in an API

When working with optimistic concurrency, conflicts may occur if two users try to modify the same data simultaneously. Here’s how to handle these conflicts gracefully.

Example: Handling Concurrency Conflicts in the Controller

We handle concurrency conflicts by showing the user both the client’s changes and the database values, allowing them to resolve the conflict.

[HttpPut]
[Route("HandleConcurrency")]
public async Task<IActionResult> HandleConcurrency([FromBody] Event updatedEvent)
{
    try
    {
        _context.Events.Update(updatedEvent);
        await _context.SaveChangesAsync();
        return Ok(updatedEvent);
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single

();
        var clientValues = (Event)entry.Entity;
        var databaseValues = (Event)await entry.GetDatabaseValuesAsync();

        if (databaseValues == null)
        {
            return NotFound("The event was deleted.");
        }

        // Show both client and database values to the user for resolution
        return Conflict(new
        {
            Message = "Concurrency conflict detected.",
            ClientValues = clientValues,
            DatabaseValues = databaseValues
        });
    }
}

In this example:

• If a concurrency conflict occurs, both the client’s values and the database values are retrieved.
• The user can then decide how to resolve the conflict, e.g., by merging changes.

Conclusion

In this article, we:

• Defined the EventInventory entity to track the number of tickets available for each event.
• Updated the EventDbContext to manage the EventInventory entity and establish its relationship with the Event table.
• Ran the necessary migration to update the database.
• Explained how to work with transactions in EF Core.
• Covered optimistic and pessimistic concurrency control and how to handle concurrency conflicts in an API context.

This approach ensures data integrity and allows you to handle multi-user interactions efficiently. Let me know if you need any further adjustments or explanations!

This content originally appeared on DEV Community and was authored by mohamed Tayel