1// EF Core 9 — Optimistic Concurrency with rowversion
2
3// Entity with concurrency token
4public class Product
5{
6    public int Id { get; set; }
7    public string Name { get; set; } = "";
8    public int Stock { get; set; }
9    public decimal Price { get; set; }
10
11    [Timestamp]                        // maps to rowversion in SQL Server
12    public byte[] RowVersion { get; set; } = [];
13    // Alternative for non-SQL Server: [ConcurrencyToken] on any property
14}
15
16// SQL Server: ALTER TABLE [Products] ADD [RowVersion] rowversion NOT NULL
17// rowversion auto-increments on every UPDATE — no application code needed
18
19// ------------------------------------------------------------------
20// The WHERE clause EF generates on UPDATE/DELETE:
21// UPDATE [Products] SET [Stock] = @p0
22// WHERE [Id] = @p1 AND [RowVersion] = @p2  ← concurrency check
23// If 0 rows affected → someone else updated first → exception
24
25// ------------------------------------------------------------------
26// Handling DbUpdateConcurrencyException — three strategies:
27
28// Strategy 1: Client Wins (overwrite DB with current user's values)
29public async Task UpdateStockClientWinsAsync(int productId, int newStock, CancellationToken ct)
30{
31    var maxRetries = 3;
32    for (var attempt = 0; attempt < maxRetries; attempt++)
33    {
34        try
35        {
36            var product = await _dbContext.Products.FindAsync([productId], ct);
37            if (product is null) throw new NotFoundException(productId);
38
39            product.Stock = newStock;
40            await _dbContext.SaveChangesAsync(ct);
41            return;
42        }
43        catch (DbUpdateConcurrencyException ex)
44        {
45            if (attempt == maxRetries - 1) throw;  // exhaust retries
46
47            var entry = ex.Entries.Single();
48            // Refresh original values from database so next SaveChanges
49            // sends the CURRENT rowversion in the WHERE clause
50            await entry.ReloadAsync(ct);  // ← CRITICAL: refresh OriginalValues
51        }
52    }
53}
54
55// Strategy 2: Database Wins (discard client changes, keep DB values)
56catch (DbUpdateConcurrencyException ex)
57{
58    var entry = ex.Entries.Single();
59    var dbValues = await entry.GetDatabaseValuesAsync(ct);
60
61    if (dbValues is null)
62        throw new InvalidOperationException("The entity was deleted by another user.");
63
64    // Overwrite current values with what's in the DB
65    entry.OriginalValues.SetValues(dbValues);
66    entry.CurrentValues.SetValues(dbValues);
67    // Don't retry — database already has the "winning" state
68}
69
70// Strategy 3: Merge (apply non-conflicting fields, surface conflicts)
71catch (DbUpdateConcurrencyException ex)
72{
73    var entry = ex.Entries.Single();
74    var dbValues = await entry.GetDatabaseValuesAsync(ct);
75    var clientValues = entry.CurrentValues;
76    var originalValues = entry.OriginalValues;
77
78    var conflicts = new List<string>();
79
80    foreach (var property in entry.Properties)
81    {
82        var dbValue = dbValues![property.Metadata.Name];
83        var clientValue = clientValues[property.Metadata.Name];
84        var origValue = originalValues[property.Metadata.Name];
85
86        if (!Equals(dbValue, origValue) && !Equals(clientValue, origValue))
87        {
88            // Both client and DB changed this field from the original
89            conflicts.Add($"{property.Metadata.Name}: DB={dbValue}, You={clientValue}");
90        }
91    }
92
93    if (conflicts.Count > 0)
94        throw new ConcurrencyConflictException(conflicts);  // surface to user
95
96    // Non-conflicting fields: take client values, refresh rowversion
97    entry.OriginalValues.SetValues(dbValues!);
98    await _dbContext.SaveChangesAsync(ct);
99}