CODEWITHSUNDEEP
c#.netentity-framework-core
giorgi02
giorgi02

Reputation: 1073

How to update row with concurrency control using EF Core?

I've a table "Orders" with columns: "Id", "ProductId", "Status", "Version".

"Version" is incremented with 1 after every changing of the row. This column is for control concurrency of several users changes.

For example, I read an order with Id=3 and Version=7. When I update it, I must be sure that nobody has changed this row yet. By SQL, I would write such script:

UPDATE Orders
SET Status = 'completed', Version = Version + 1
WHERE Id = 3 AND Version = 7

How can I write the same code by EF Core without using SQL command?

Upvotes: 1

Views: 2284

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89424

Sure. Just mark Version as a Concurrency Token and its pre-update value will be used in the WHERE clause of any update. EG

modelBuilder.Entity<Order>()
            .Property(o => o.Version)
            .IsConcurrencyToken(true);

For a change like

var order = ...
order.Status = "Completed";
order.Version += 1;
db.SaveChanges();

The UPDATE will look something like this:

  SET NOCOUNT ON;
  UPDATE [Events] SET [Status] = @p0, [Version] = @p1
  WHERE [Id] = @p2 AND [Version] = @p3;
  SELECT @@ROWCOUNT;

And if the Version has changed 0 rows will be affected and EF will throw an DbUpdateConcurrencyException indicating a concurrency conflict.

You can also use server-side incrementing for the concurrency token with additional configuration, or by using a provider-specific type like SQL Server's rowversion.

Upvotes: 2

Related Questions