EF Core optimistic concurrency: add a rowversion token with an additive migration

TL;DR: add a byte array RowVersion property to the entity, configure it with IsRowVersion (or the Timestamp attribute), and scaffold a migration. Check the migration does exactly one thing, adds a non-nullable rowversion column and nothing else: SQL Server fills a generated value in for every existing row, old code ignores the new column, so the schema change is additive. Apply the migration first (expand), then deploy the code that uses the token (contract is a later, separate cleanup if you ever need one). From then on EF Core puts the original RowVersion in the UPDATE predicate; a concurrent change makes the update affect zero rows and throws DbUpdateConcurrencyException, which you handle by reloading and returning a conflict, never by blindly retrying the overwrite.

Verbatim problem (the search that created the source thread, plus the conflict error you get once the token is live and a save loses the race):

EF Core optimistic concurrency additive migration rowversion
DbUpdateConcurrencyException: The database operation was expected to affect 1 row(s), but actually affected 0 row(s); data may have been modified or deleted since entities were loaded.

Symptoms this fixes: two requests load the same row, both edit it, and the second save silently overwrites the first (a lost update); an admin edit and a user edit race and one disappears with no error anywhere; or a team wants protection against that without taking locks, rewriting the table, or breaking the version of the app that is still running during the deploy.

Steps

1. Add the RowVersion property and configure it as a row version

Add a byte array property named RowVersion to the entity that needs protection, and configure it in OnModelCreating with the fluent call Property for RowVersion followed by IsRowVersion. The attribute form is equivalent: put the Timestamp attribute from System.ComponentModel.DataAnnotations on the same property and skip the fluent call. Pick one style per codebase, not both. IsRowVersion implies three things at once: the property is a concurrency token, the database generates its value, and EF Core refreshes it after every save. Do not set its value in application code, ever; SQL Server rejects attempts to insert or update a rowversion column directly.

Expected output: the model snapshot diff shows one new property, RowVersion, marked as a concurrency token and value generated on add or update. Success check: no other entity changed, and the property is not nullable in the model.

2. Scaffold the migration and audit it line by line before applying anything

Run the usual scaffold command, for example dotnet ef migrations add AddRowVersionToOrders, substituting your entity table. Then read the generated Up method before you run it. An additive result has all of these properties: it adds one column named RowVersion of type rowversion, non-nullable, to the one table; it drops nothing, renames nothing, and alters no existing column; the Down method only drops that new column. The equivalent raw SQL is one statement, in words: alter the table and add the RowVersion column as rowversion not null. If scaffolding also produced unrelated changes, your model had drifted from the last snapshot; stop, separate those changes into their own migration, and re-scaffold so this migration stays single-purpose and reviewable.

Expected output: a migration whose Up is the single column addition described above. Success check: a reviewer can confirm expand-only by reading Up top to bottom in under a minute, and dotnet ef migrations script for just this migration prints that one addition and nothing destructive.

3. Understand why no backfill script is needed, and size the table first

A rowversion column is database-generated, so adding it does not need a default value, a backfill update, or application data. When SQL Server adds the column to a table that already has rows, it assigns a generated rowversion value to every existing row as part of that operation. That is what makes the migration additive for data as well as for schema. One caution: on a very large table that assignment is real work, not an instant metadata-only change, so rehearse the migration against a restored copy at production size, time it, and run the production one in a window that tolerates that table being worked on. Do not add an index on the new column, and remember a table can have only one rowversion column; if the table already has a timestamp or rowversion column, map that existing column instead of adding a second one.

Expected output: the rehearsal migration completes with every pre-existing row now carrying a non-empty RowVersion value. Success check: a count of rows where RowVersion is null returns zero, and the row count before and after is identical.

4. Deploy expand-first: migration before code, never the other way round

Apply the migration while the old application version is still running, and confirm the old version keeps working against the new schema. It will, because the old code never references the new column and SQL Server maintains the value on every insert and update regardless of which code wrote the row. Only then deploy the new application version that maps the token. Reversing the order breaks: new code against the old schema fails on every save because the column it expects does not exist yet. Keep the two steps as separate, separately verifiable deploy stages, and keep the Down migration (drop the column) as the rollback for the schema stage; rolling back the code alone also works, because old code ignores the column if you leave it in place.

Expected output: after stage one, the old app passes its normal smoke checks with the column present; after stage two, the new app saves normally. Success check: an edit made by the still-running old build changes the row and the RowVersion value changes with it, proving the database, not the app, owns the value.

5. Handle DbUpdateConcurrencyException as a conflict, not as a retryable glitch

Wrap SaveChangesAsync in a catch for DbUpdateConcurrencyException from the first day the token ships; an unhandled one is just a 500 with extra steps. In the handler, walk the exception entries and for each one call GetDatabaseValuesAsync. If that returns null, the row was deleted by someone else: report a not-found or gone result. If it returns values, the row changed under you: the safe defaults are to return HTTP 409 with the current database values so the caller can merge and resubmit, or to reload the entity and ask the user to reapply their edit. Database-wins resolution sets the original values from the database values and retries once, discarding the attempted edit; client-wins does the opposite and knowingly overwrites. Either is a product decision made on purpose. What never works is catching the exception and calling SaveChangesAsync again unchanged: the original token is still stale, so it throws again, and a loop around it spins or, worse, eventually overwrites after someone forces the original value.

Expected output: a simulated race (step 6) produces a clean 409 or a reload prompt, not a 500 and not a silent overwrite. Success check: the database after the losing save holds the winning edit exactly, with its newer RowVersion.

6. Prove it end to end with a two-reader test before you call it done

Load the same row in two independent contexts (or two browser sessions) so both hold the same original RowVersion. Save a change in the first: it succeeds and the RowVersion value in the database is different from the one both readers started with. Now save a change in the second, which still holds the stale original: it must throw DbUpdateConcurrencyException, because EF Core generates an update whose predicate includes the key and the original RowVersion, the predicate matches zero rows, and zero rows affected is exactly what EF Core reports as a concurrency conflict. Also check the API surface if the entity crosses one: a detached update only gets protection if the client round-trips the token (commonly as a base64 string in the DTO) and the server sets it as the original value on the attached entity. An update path that never supplies the original token has no check to fail, so audit create-versus-update DTOs before signing off.

Expected output: first save succeeds with a rotated token, second save conflicts. Success check: with command logging on in development, the generated update for a tracked entity visibly predicates on both the key and RowVersion.

When this applies

• EF Core (6 and later, including 8, 9, and 10) on SQL Server or Azure SQL, with an existing table that has live rows
• Lost updates are possible today because two writers can load, edit, and save the same row concurrently
• The change must ship without downtime theatre: one additive migration, old code tolerant of the new schema, no data rewrite by the application
• You can change the save path to handle the conflict exception, and a 409 or reload response is acceptable to callers

When this does not apply

• PostgreSQL as the primary target: PostgreSQL has no rowversion column. Use the xmin variant below instead of scaffolding a column that will never behave the same way.
• You actually need pessimistic locking because conflicts are the normal case, work is long-running while holding data, or partial overwrites would corrupt an invariant spanning several rows. A token detects races; it does not coordinate work.
• The entity is append-only, single-writer, or last-write-wins is an accepted product rule. The token adds a failure mode callers must handle, for no benefit there.
• A gated production release process is the actual problem (reviewed allowlists, hashed SQL artifacts, approval gates). That is a release-governance problem, a different skill; this one assumes you may apply a normal migration.
• Bulk operations are your write path: ExecuteUpdate and ExecuteDelete do not apply concurrency tokens automatically. Include the token explicitly in their predicate yourself, or those paths stay unprotected no matter what the model says.

Variant: PostgreSQL with the xmin system column

PostgreSQL hides a system column named xmin on every table, holding the id of the transaction that last changed the row, so it works as a token with nothing to add. Map a uint property to it (the Npgsql docs show the Timestamp attribute on a uint Version property, or the provider helper that uses xmin as the concurrency token), and accept that the scaffolded migration is mostly a model change rather than a real new column, since the column already exists in the database. The conflict behavior is identical: the token goes in the update predicate and a mismatch throws the same exception, handled exactly as in step 5.

Variant: the table already has a timestamp column

timestamp in SQL Server is the deprecated synonym for rowversion, same type, same behavior. Map the existing column with IsRowVersion and skip the migration entirely apart from any snapshot update; adding a second rowversion column to the same table is not allowed.

Variant: a non-SQL Server store without a generated token

Use an application-maintained token instead: a Guid or a long Version property marked as a concurrency token, set to a fresh value on every save in SaveChanges or in the save handler. It works anywhere, but the database no longer guarantees the rotation; a write path that bypasses your context (raw SQL, a second service) will not rotate it and will not be detected. Prefer the database-generated token wherever the provider offers one.

Variant: token present but conflicts never fire

Usually one of three causes: the property is configured as a plain concurrency token but not as a row version, so its value never changes; the entity is loaded with no-tracking and attached without restoring the original token, so there is nothing stale to compare; or the failing path is a bulk operation (see when this does not apply). Check the generated SQL predicate first: if RowVersion is not in it, EF Core is not checking, whatever the model code looks like.

Why it happens

EF Core has no built-in way to know a row changed between your read and your write unless you give it something to compare. Without a concurrency token, the generated update predicates on the key alone, so the last writer always matches and always wins, silently. A rowversion column gives the database itself a value to rotate on every single update to the row, by any writer, and IsRowVersion tells EF Core to snapshot the value you read, predicate the update on that snapshot, and treat zero rows affected as the conflict signal. The additive shape falls out of the same fact: because the database generates and maintains the value, existing rows, old binaries, and raw SQL writers all participate without any of them doing the work.

Edge cases and pitfalls

• Never expose the token as anything but an opaque value. It is an 8-byte binary counter scoped to the database, not a timestamp, not ordered across tables, and meaningless as a date or a global sequence. Round-trip it byte-for-byte (base64 in JSON); parsing or reformatting it breaks the comparison.
• The token rotates on any update to the row, including updates to columns you do not care about and updates from triggers-adjacent jobs or admin scripts. That is correct optimistic behavior, but expect conflict rates to reflect all writers, not just the screen you are testing.
• A rowversion value changes even when an update sets a column to the value it already had. Do not use token equality as a nothing-changed detector for business logic.
• Detached and offline scenarios must carry the original token from read time, not fetch a fresh one at save time; fetching fresh silently disables the whole check.
• One token per entity that needs independent protection. A token on a parent does not protect child rows, and a shared token across an aggregate makes unrelated child edits conflict with each other.
• Test the migration rollback honestly: Down drops the column, which also drops protection, so a rolled-back deploy is an unprotected deploy. Prefer rolling the code back and leaving the column in place.