Handle concurrency conflicts with EF Core using application-managed tokens

[nanotaboada]

Description

To ensure data integrity and prevent lost updates when multiple clients modify the same player record, we need to implement optimistic concurrency control.

Since our app currently uses SQLite, which lacks automatic concurrency support (e.g., rowversion/timestamp), we’ll manage a concurrency token (GUID) ourselves in the application layer.

This prevents unintentional overwrites and aligns with best practices for API design in concurrent environments.

Proposed Solution

Introduce application-managed concurrency using a Version property (Guid) in the Player entity, marked as a concurrency token.

The client must supply the current version when issuing an update. If the version in the request does not match the database, a 409 Conflict response is returned, signaling the record has been modified elsewhere.

Suggested Approach

1. Add Version to the Player Model

public Guid Version { get; set; } = Guid.NewGuid();

2. Configure as a Concurrency Token

In PlayerDbContext.OnModelCreating():

modelBuilder.Entity<Player>()
    .Property(p => p.Version)
    .IsConcurrencyToken();

3. Add Version to the PlayerRequestModel

public class PlayerRequestModel
{
    public Guid Version { get; set; }
}

4. Update PlayerService.UpdateAsync

public async Task<bool> UpdateAsync(PlayerRequestModel playerRequestModel)
{
    var player = await playerRepository.FindBySquadNumberAsync(playerRequestModel.SquadNumber);
    if (player is null) return false;

    if (player.Version != playerRequestModel.Version)
        throw new ConcurrencyException("The player has been modified by another process.");

    mapper.Map(playerRequestModel, player);
    player.Version = Guid.NewGuid();

    try
    {
        await playerRepository.UpdateAsync(player);
        memoryCache.Remove(CacheKey_RetrieveAsync);
        return true;
    }
    catch (DbUpdateConcurrencyException)
    {
        throw new ConcurrencyException("A concurrency conflict occurred during update.");
    }
}

5. Add a Domain Exception

public class ConcurrencyException : Exception
{
    public ConcurrencyException(string message) : base(message) { }
}

6. Handle Conflict in PlayerController

[HttpPut("{squadNumber:int}")]
[ProducesResponseType(StatusCodes.Status204NoContent)]
[ProducesResponseType(StatusCodes.Status400BadRequest)]
[ProducesResponseType(StatusCodes.Status404NotFound)]
[ProducesResponseType(StatusCodes.Status409Conflict)]
public async Task<IResult> PutAsync(int squadNumber, PlayerRequestModel player)
{
    var validation = await validator.ValidateAsync(player);
    if (!validation.IsValid)
        return TypedResults.BadRequest(validation.Errors.Select(e => new { e.PropertyName, e.ErrorMessage }));

    if (await playerService.RetrieveBySquadNumberAsync(squadNumber) is null)
        return TypedResults.NotFound();

    try
    {
        var updated = await playerService.UpdateAsync(player);
        return updated ? TypedResults.NoContent() : TypedResults.NotFound();
    }
    catch (ConcurrencyException ex)
    {
        return TypedResults.Conflict(new { error = ex.Message });
    }
}

7. Update Repository

Update remains unchanged, but should optionally catch and wrap concurrency:

public async Task UpdateAsync(T entity)
{
    try
    {
        _dbSet.Update(entity);
        await dbContext.SaveChangesAsync();
    }
    catch (DbUpdateConcurrencyException ex)
    {
        throw new ConcurrencyException("A concurrency conflict occurred.", ex);
    }
}

Acceptance Criteria

• Player entity includes a Version field (Guid)
• Version is marked as a concurrency token in EF Core
• PlayerRequestModel includes Version
• PUT /players/{squadNumber} fails with 409 Conflict if version mismatch
• New version is generated on successful update
• Concurrency errors are logged and handled cleanly
• Unit and/or integration tests verify concurrency logic

[coderabbitai]

🤖 CodeRabbit Planning

Planning is in progress. This will take a moment...

[coderabbitai]

Coding Plan

Approach

Implement optimistic concurrency control for the Player entity using an application-managed GUID-based Version token. When clients attempt to update a player record, they must provide the current Version value; mismatches result in a 409 Conflict response, preventing lost updates in concurrent environments.

Observations

This is an ASP.NET Core Web API using a clean layered architecture with Repository → Service → Controller pattern. The project uses SQLite for persistence, FluentValidation for request validation, AutoMapper for entity/DTO mapping, and a global ExceptionMiddleware for error handling. The Player entity is defined in Models/Player.cs, with separate request/response DTOs. The DbContext configuration in PlayerDbContext.cs currently has no concurrency tokens configured. The middleware already handles DbUpdateConcurrencyException mapped to 409 Conflict, but the ticket requires a custom ConcurrencyException for application-level handling. Tests use xUnit with Moq and FluentAssertions.

Assumptions

Assumption 1: AutoMapper Version Mapping Strategy

Options Considered:

1. Ignore Version in PlayerRequestModel → Player mapping (explicit that service controls Version)
2. Map Version normally (overwritten by service anyway)

Chosen Option: 1

Rationale: Ignoring Version in the mapping makes the intent explicit that the service layer controls Version generation, avoiding confusion about where Version values come from.

Assumption 2: Exception Handling Location

Options Considered:

1. Handle ConcurrencyException in controller only (as shown in ticket)
2. Add ConcurrencyException to middleware and handle in controller
3. Rely entirely on middleware handling

Chosen Option: 2

Rationale: Add ConcurrencyException to the middleware for consistency with existing patterns, while also handling it in the controller as the ticket specifies. This provides defense in depth.

💡 User Tips

Regenerate the plan with different choices with @coderabbitai <feedback>.

Implementation Steps

Phase 1: Data Model and Database Migration

This phase establishes the Version field in the data layer, configuring it as an EF Core concurrency token and creating the necessary database migration to add the column to existing records.

Task 1: Add Version Property to Player Entity

Add the GUID-based Version property to the Player model with a default value that generates a new GUID for new entities.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Models/Player.cs to add public Guid Version { get; set; } = Guid.NewGuid(); property
• Place the property with other entity properties, following existing naming and formatting conventions

Task 2: Configure Concurrency Token in DbContext

Configure the Version property as a concurrency token in EF Core so that the framework generates appropriate WHERE clauses during updates.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Data/PlayerDbContext.cs in the OnModelCreating method
• Add configuration for the Player entity's Version property using .IsConcurrencyToken() fluent API
• This enables EF Core to detect concurrent modifications at the database level

Task 3: Create Database Migration

Generate and configure an EF Core migration to add the Version column, ensuring existing records receive generated GUID values.

• Create new migration in src/Dotnet.Samples.AspNetCore.WebApi/Migrations/ using EF Core CLI or Package Manager Console
• The migration should add the Version column as non-nullable Guid type
• Use SQL to populate existing records with unique GUID values (SQLite compatible approach using lower(hex(randomblob(16))))

🤖 Prompt for AI agents

Establish the Version field in the data layer as a GUID-based concurrency token.
Configure EF Core to use it for optimistic concurrency control.

Add Version property to Player entity:
- Add `public Guid Version { get; set; } = Guid.NewGuid();` to Player.cs
- Place with other entity properties following existing conventions

Configure concurrency token in PlayerDbContext:
- In OnModelCreating method, add fluent API configuration for Player.Version
using `.IsConcurrencyToken()`

Create database migration:
- Generate migration to add Version column as non-nullable Guid
- Include SQL to populate existing records with unique GUIDs using
SQLite-compatible approach

Phase 2: DTOs and Mapping Configuration

This phase extends the request and response models to include Version and updates AutoMapper to handle the new field appropriately for create versus update operations.

Task 1: Add Version to PlayerRequestModel

Add the Version property to the request model so clients can provide their known version when updating a player.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Models/PlayerRequestModel.cs to add public Guid Version { get; set; } property
• Note: For POST (create) operations, this field will default to Guid.Empty and be ignored; for PUT (update) operations, clients must provide the current version

Task 2: Add Version to PlayerResponseModel

Add the Version property to the response model so clients receive the current version for use in subsequent update requests.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Models/PlayerResponseModel.cs to add public Guid Version { get; set; } property
• This enables clients to read the current version from GET responses and include it in PUT requests

Task 3: Update AutoMapper Profile

Configure AutoMapper to properly handle Version mapping between entities and DTOs.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Mappings/PlayerMappingProfile.cs
• For PlayerRequestModel → Player mapping: add .ForMember(dest => dest.Version, opt => opt.Ignore()) since the service layer manages Version generation
• For Player → PlayerResponseModel mapping: ensure Version maps directly (AutoMapper handles this by convention, but verify)

🤖 Prompt for AI agents

Extend request and response DTOs to include Version field and configure
AutoMapper to handle it appropriately.

Add Version to DTOs:
- Add `public Guid Version { get; set; }` to PlayerRequestModel.cs
- Add `public Guid Version { get; set; }` to PlayerResponseModel.cs

Update AutoMapper configuration:
- In PlayerMappingProfile.cs, add `.ForMember(dest => dest.Version, opt =>
opt.Ignore())` for PlayerRequestModel → Player mapping
- Verify Player → PlayerResponseModel mapping includes Version (should map by
convention)

Phase 3: Exception and Service Layer Updates

This phase introduces the domain-specific ConcurrencyException and updates the service layer to perform application-level version checking and new version generation on successful updates.

Task 1: Create ConcurrencyException

Create a custom domain exception for concurrency conflicts that can be caught and handled specifically by the controller and middleware.

• Create src/Dotnet.Samples.AspNetCore.WebApi/Exceptions/ConcurrencyException.cs (new directory if Exceptions doesn't exist)
• Implement exception class inheriting from Exception with a string message constructor
• Include an optional inner exception constructor for wrapping DbUpdateConcurrencyException

Task 2: Update PlayerService.UpdateAsync

Modify the update logic to perform application-level version checking, generate new versions on success, and wrap database concurrency exceptions.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Services/PlayerService.cs
• Change method signature to return Task<bool> instead of Task to indicate update success/failure
• Add version mismatch check before mapping: compare player.Version with playerRequestModel.Version, throw ConcurrencyException if different
• After mapping request to player, explicitly set player.Version = Guid.NewGuid() to generate new version
• Wrap the repository call in try-catch to convert DbUpdateConcurrencyException to ConcurrencyException
• Update src/Dotnet.Samples.AspNetCore.WebApi/Services/IPlayerService.cs interface to reflect new return type

Task 3: Update ExceptionMiddleware

Add handling for the new ConcurrencyException in the global exception middleware for consistency.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Middlewares/ExceptionMiddleware.cs
• Add ConcurrencyException to the exception switch statement, mapping it to 409 Conflict status
• This provides a fallback if the exception isn't caught at the controller level

🤖 Prompt for AI agents

Introduce ConcurrencyException and implement application-level version checking
in the service layer with automatic version generation on successful updates.

Create ConcurrencyException:
- Create Exceptions/ConcurrencyException.cs inheriting from Exception
- Include constructor accepting message string
- Include constructor accepting message and inner exception for wrapping
DbUpdateConcurrencyException

Update PlayerService:
- Change UpdateAsync signature to return Task<bool> in both interface and
implementation
- Add version check: compare player.Version with playerRequestModel.Version,
throw ConcurrencyException on mismatch
- After mapping, set player.Version = Guid.NewGuid() to generate new version
- Wrap repository call in try-catch to convert DbUpdateConcurrencyException to
ConcurrencyException

Update ExceptionMiddleware:
- Add ConcurrencyException case to exception handling logic
- Map to 409 Conflict status code

Phase 4: Controller Layer Updates

This phase updates the PUT endpoint to explicitly handle concurrency exceptions and document the 409 response type.

Update PlayerController.PutAsync

Modify the PUT endpoint to catch ConcurrencyException and return appropriate 409 Conflict responses.

• Update src/Dotnet.Samples.AspNetCore.WebApi/Controllers/PlayerController.cs
• Add [ProducesResponseType(StatusCodes.Status409Conflict)] attribute to PutAsync method
• Wrap the playerService.UpdateAsync() call in try-catch for ConcurrencyException
• Return TypedResults.Conflict() with error message when concurrency exception is caught
• Add appropriate logging for concurrency conflict scenarios

🤖 Prompt for AI agents

Update the PUT endpoint to handle concurrency exceptions and document the 409
response.

Modify PutAsync in PlayerController:
- Add [ProducesResponseType(StatusCodes.Status409Conflict)] attribute
- Wrap playerService.UpdateAsync() call in try-catch for ConcurrencyException
- Return TypedResults.Conflict() with error message when caught
- Add logging for concurrency conflicts

Phase 5: Unit Tests

This phase adds unit tests to verify the concurrency control logic works correctly at both service and controller layers.

Task 1: Add Service Layer Concurrency Tests

Create unit tests for PlayerService.UpdateAsync covering version mismatch scenarios.

• Add tests to test/Dotnet.Samples.AspNetCore.WebApi.Tests/Unit/PlayerServiceTests.cs
• Test case: UpdateAsync throws ConcurrencyException when request Version doesn't match entity Version
• Test case: UpdateAsync succeeds and generates new Version when versions match
• Test case: UpdateAsync generates new Version value (verify Version changed after successful update)
• Follow existing test patterns using Moq for mocking repository, mapper, cache, and logger

Task 2: Add Controller Layer Concurrency Tests

Create unit tests for PlayerController.PutAsync covering concurrency exception handling.

• Add tests to test/Dotnet.Samples.AspNetCore.WebApi.Tests/Unit/PlayerControllerTests.cs
• Test case: PutAsync returns 409 Conflict when service throws ConcurrencyException
• Test case: Verify conflict response includes error message
• Follow existing controller test patterns using mocked IPlayerService

🤖 Prompt for AI agents

Add comprehensive unit tests for concurrency control logic at service and
controller layers.

Service layer tests in PlayerServiceTests.cs:
- Test UpdateAsync throws ConcurrencyException when versions don't match
- Test UpdateAsync succeeds when versions match
- Test UpdateAsync generates new Version (verify Version changed after update)
- Use Moq for mocking dependencies following existing patterns

Controller layer tests in PlayerControllerTests.cs:
- Test PutAsync returns 409 Conflict when ConcurrencyException thrown
- Test conflict response includes error message
- Use mocked IPlayerService following existing patterns

---

🚀 Next Steps

🤖 All AI agent prompts combined

Task: 1

Establish the Version field in the data layer as a GUID-based concurrency token.
Configure EF Core to use it for optimistic concurrency control.

Add Version property to Player entity:
- Add `public Guid Version { get; set; } = Guid.NewGuid();` to Player.cs
- Place with other entity properties following existing conventions

Configure concurrency token in PlayerDbContext:
- In OnModelCreating method, add fluent API configuration for Player.Version
using `.IsConcurrencyToken()`

Create database migration:
- Generate migration to add Version column as non-nullable Guid
- Include SQL to populate existing records with unique GUIDs using
SQLite-compatible approach
===============================================================================

Task: 2

Extend request and response DTOs to include Version field and configure
AutoMapper to handle it appropriately.

Add Version to DTOs:
- Add `public Guid Version { get; set; }` to PlayerRequestModel.cs
- Add `public Guid Version { get; set; }` to PlayerResponseModel.cs

Update AutoMapper configuration:
- In PlayerMappingProfile.cs, add `.ForMember(dest => dest.Version, opt =>
opt.Ignore())` for PlayerRequestModel → Player mapping
- Verify Player → PlayerResponseModel mapping includes Version (should map by
convention)
===============================================================================

Task: 3

Introduce ConcurrencyException and implement application-level version checking
in the service layer with automatic version generation on successful updates.

Create ConcurrencyException:
- Create Exceptions/ConcurrencyException.cs inheriting from Exception
- Include constructor accepting message string
- Include constructor accepting message and inner exception for wrapping
DbUpdateConcurrencyException

Update PlayerService:
- Change UpdateAsync signature to return Task<bool> in both interface and
implementation
- Add version check: compare player.Version with playerRequestModel.Version,
throw ConcurrencyException on mismatch
- After mapping, set player.Version = Guid.NewGuid() to generate new version
- Wrap repository call in try-catch to convert DbUpdateConcurrencyException to
ConcurrencyException

Update ExceptionMiddleware:
- Add ConcurrencyException case to exception handling logic
- Map to 409 Conflict status code
===============================================================================

Task: 4

Update the PUT endpoint to handle concurrency exceptions and document the 409
response.

Modify PutAsync in PlayerController:
- Add [ProducesResponseType(StatusCodes.Status409Conflict)] attribute
- Wrap playerService.UpdateAsync() call in try-catch for ConcurrencyException
- Return TypedResults.Conflict() with error message when caught
- Add logging for concurrency conflicts
===============================================================================

Task: 5

Add comprehensive unit tests for concurrency control logic at service and
controller layers.

Service layer tests in PlayerServiceTests.cs:
- Test UpdateAsync throws ConcurrencyException when versions don't match
- Test UpdateAsync succeeds when versions match
- Test UpdateAsync generates new Version (verify Version changed after update)
- Use Moq for mocking dependencies following existing patterns

Controller layer tests in PlayerControllerTests.cs:
- Test PutAsync returns 409 Conflict when ConcurrencyException thrown
- Test conflict response includes error message
- Use mocked IPlayerService following existing patterns

💡 Iterate on the plan with: @coderabbitai <feedback>

Example Feedback
- `@coderabbitai` You can skip phase 3. Add a simple unit test case for phase 2.
- `@coderabbitai` For assumption 1 go ahead with option 3 and replan.

---

💬 Have feedback or questions? Drop into our discord!