Implement Optimistic Concurrency with SQL Server rowversion in EF Core

The Problem: Silent Lost Updates

Two users load the same order record. Both change different fields and save. Without concurrency control, the second SaveChanges overwrites the first user's changes silently. The database shows only the second user's data; the first user's work is gone with no error.

Optimistic concurrency solves this by attaching a database-maintained token to each row. When the token at save time differs from the token at load time, EF Core throws DbUpdateConcurrencyException instead of committing. Your code then decides how to resolve the conflict.

Desired Outcome

• Concurrent edits to the same row raise DbUpdateConcurrencyException on the second SaveChanges.
• The exception surfaces the current database values so you can implement database-wins, client-wins, or a field-level merge.
• No silent data loss.

Prerequisites

• .NET 6+ project with Microsoft.EntityFrameworkCore.SqlServer (version matching your EF Core major version).
• An existing entity with a primary key (e.g., Order with Id).
• EF Core migration tooling: dotnet ef CLI or Visual Studio Package Manager Console.
• SQL Server 2016+ (rowversion is supported on all supported versions).

Add the Concurrency Token

Option A: Data Annotation

using System.ComponentModel.DataAnnotations;

public class Order
{
    public int Id { get; set; }
    public string CustomerName { get; set; }
    public decimal Total { get; set; }
    
    [Timestamp]
    public byte[] RowVersion { get; set; }
}

Option B: Fluent API (preferred for complex models)

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity()
        .Property(o => o.RowVersion)
        .IsRowVersion();
}

The byte[] property maps to a SQL Server rowversion column (also called timestamp in older syntax). The database increments this binary counter on every row update; your application never sets it.

Create and Apply the Migration

Run from the project directory containing the .csproj:

dotnet ef migrations add AddRowVersionToOrders

Inspect the generated migration. The Up method should contain:

migrationBuilder.AddColumn<byte[]>(
    name: "RowVersion",
    table: "Orders",
    rowVersion: true,
    nullable: false,
    defaultValue: new byte[0]);

Apply it:

dotnet ef database update

Verify in SSMS or via catalog query that the column type is rowversion and NOT NULL:

SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Orders' AND COLUMN_NAME = 'RowVersion';

How EF Core Uses the Token

On SaveChanges, EF Core generates an UPDATE that includes the token in the WHERE clause:

UPDATE [Orders] SET [CustomerName] = @p0, [Total] = @p1
WHERE [Id] = @p2 AND [RowVersion] = @p3;
SELECT [RowVersion] FROM [Orders] WHERE @@ROWCOUNT = 1 AND [Id] = @p2;

If another transaction updated the row after you loaded it, the WHERE matches zero rows. EF Core detects @@ROWCOUNT = 0 and throws DbUpdateConcurrencyException. The new token is re-read on success so the context stays in sync.

Handle Conflicts in Connected Scenarios

Wrap SaveChanges (or SaveChangesAsync) in a try/catch. The exception's Entries collection holds one IUpdateEntry per conflicting entity.

public async Task<Order> UpdateOrderAsync(int id, Action<Order> mutate)
{
    using var context = new AppDbContext();
    var order = await context.Orders.FindAsync(id);
    if (order == null) throw new KeyNotFoundException();

    mutate(order);

    try
    {
        await context.SaveChangesAsync();
        return order;
    }
    catch (DbUpdateConcurrencyException ex)
    {
        foreach (var entry in ex.Entries)
        {
            if (entry.Entity is Order dbOrder)
            {
                var databaseValues = await entry.GetDatabaseValuesAsync();
                if (databaseValues == null)
                {
                    // Row was deleted
                    throw new InvalidOperationException("Order deleted by another user.");
                }

                // DATABASE-WINS: reload current values, reapply mutation, retry
                entry.OriginalValues.SetValues(databaseValues);
                mutate(dbOrder); // reapply the same change
            }
        }
        await context.SaveChangesAsync(); // retry
        return order;
    }
}

Three resolution strategies:

• Database-wins (shown): reload database values into OriginalValues, reapply the user's intent, retry.
• Client-wins: entry.OriginalValues.SetValues(databaseValues) then entry.CurrentValues.SetValues(desiredValues) and retry — forces your version.
• Field-level merge: inspect databaseValues and entry.CurrentValues property by property, build a merged result, set CurrentValues, retry.

Disconnected / Web Scenarios

In a typical web app, the DbContext lives only for one request. The token must round-trip to the client and back.

1. Send the token to the client

// GET /orders/42/edit
public async Task<IActionResult> Edit(int id)
{
    var order = await _context.Orders.AsNoTracking().FirstOrDefaultAsync(o => o.Id == id);
    if (order == null) return NotFound();
    return View(order); // RowVersion included in model
}

In the Razor view, include a hidden field:

<input type="hidden" asp-for="RowVersion" />

2. Restore the token on POST

[HttpPost]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Edit(Order order)
{
    if (!ModelState.IsValid) return View(order);

    _context.Attach(order);
    // Critical: tell EF the original token value
    _context.Entry(order).Property(o => o.RowVersion).OriginalValue = order.RowVersion;

    try
    {
        await _context.SaveChangesAsync();
        return RedirectToAction(nameof(Index));
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single();
        var databaseValues = await entry.GetDatabaseValuesAsync();
        if (databaseValues == null)
        {
            ModelState.AddModelError(string.Empty, "This order was deleted by another user.");
            return View(order);
        }

        // Show conflict UI with current database values
        var dbOrder = (Order)databaseValues.ToObject();
        ModelState.AddModelError(string.Empty,
            "The record was modified by another user. Current values are shown; review and resubmit.");
        // Optionally bind dbOrder to view for comparison
        return View(dbOrder);
    }
}

Without setting OriginalValue, EF Core assumes the token is unchanged and cannot detect the conflict.

Expected Checks (Verification Steps)

1. Two-context integration test — load the same row in two contexts, save the first, then the second. Assert DbUpdateConcurrencyException is thrown and the first user's values persist.

   [Fact]
   public async Task ConcurrentUpdate_ThrowsConcurrencyException()
   {
       using var ctx1 = CreateContext();
       using var ctx2 = CreateContext();
       var o1 = await ctx1.Orders.FirstAsync();
       var o2 = await ctx2.Orders.FirstAsync();
       o1.Total = 100;
       await ctx1.SaveChangesAsync();
       o2.Total = 200;
       await Assert.ThrowsAsync<DbUpdateConcurrencyException>(() => ctx2.SaveChangesAsync());
       var reloaded = await ctx1.Orders.FirstAsync();
       Assert.Equal(100, reloaded.Total);
   }

2. SQL inspection — enable EF Core logging (LogTo with LogLevel.Information) or use SQL Server Profiler/Extended Events. Confirm the UPDATE includes RowVersion in the WHERE clause.
3. Schema check — after migration, verify column type is rowversion and NOT NULL (see catalog query above).
4. Manual conflict injection — in SSMS, run UPDATE Orders SET Total = 999 WHERE Id = 42; between your app's read and save. Confirm the exception handler produces the expected final state.

Recovery and Rollback Notes

• SaveChanges is atomic per call. When DbUpdateConcurrencyException is thrown, all pending changes in that call are rolled back. No partial commit occurs.
• The exception can contain multiple entries if you modified several conflicting entities in one SaveChanges. Iterate ex.Entries and resolve each.
• If you implement a retry loop, cap retries (e.g., 3) to avoid livelock under high contention.

Limitations and Cautions

• rowversion is binary, not a clock. Never display it as a timestamp or compare values across tables.
• Token must round-trip. In disconnected scenarios, if the client omits or tampers with the token, EF cannot detect conflicts — the feature silently does nothing.
• Bulk APIs bypass the tracker. ExecuteUpdate, ExecuteDelete, and third-party bulk extensions may not honor concurrency tokens. Test your specific EF Core version.
• Removing the token reintroduces lost-update risk. If conflicts are too frequent, tune the resolution strategy (e.g., finer-grained merges) or add a retry policy instead of dropping the token.
• Provider-specific. IsRowVersion() maps to SQL Server rowversion. For PostgreSQL use xmin via Npgsql's UseXminAsConcurrencyToken or a uuid_generate_v4() column with IsConcurrencyToken(). For SQLite, use a trigger-maintained integer version column.

Quick Reference: Configuration Checklist

Step	Command / Code	Verify
Add property	byte[] RowVersion + [Timestamp] or .IsRowVersion()	Property exists on entity
Create migration	dotnet ef migrations add AddRowVersion	Migration shows rowVersion: true
Apply migration	dotnet ef database update	Column type = rowversion, not null
Connected conflict test	Two contexts, save first, save second	Throws DbUpdateConcurrencyException
Disconnected round-trip	Hidden field + OriginalValue restore	Conflict detected on POST
SQL check	Enable EF logging	WHERE ... AND RowVersion = @p

Next Steps

Integrate the conflict handler into your application's error-handling pipeline. For high-contention tables, consider a retry policy with exponential backoff (e.g., via Polly) around the save-and-resolve block. Monitor DbUpdateConcurrencyException frequency in production logs — a sudden spike may indicate a UX issue (e.g., long edit forms) rather than a data problem.