Checking Concurrency on an Entity without updating the Row Version

There is a surprisingly simple solution, "out-of-2-boxes", but it requires two modifications I'm not sure you can, or are willing to, make:

• Create an updatable view on the Child table containing a ParentRowVersion column
• Map the Child entity to this view

Let me show how this works. It's all pretty straightforward.

Database model:

CREATE TABLE [dbo].[Parent]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) NOT NULL,
[RowVersion] [timestamp] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Parent] ADD CONSTRAINT [PK_Parent] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]

CREATE TABLE [dbo].[Child]
(
[ID] [int] NOT NULL IDENTITY(1, 1),
[Name] [nvarchar] (50) NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[ParentID] [int] NOT NULL
) ON [PRIMARY]
ALTER TABLE [dbo].[Child] ADD CONSTRAINT [PK_Child] PRIMARY KEY CLUSTERED  ([ID]) ON [PRIMARY]
GO
CREATE VIEW [dbo].[ChildView]
WITH SCHEMABINDING
AS
SELECT Child.ID
, Child.Name
, Child.ParentID
, Child.RowVersion
, p.RowVersion AS ParentRowVersion
FROM dbo.Child
INNER JOIN dbo.Parent p ON p.ID = Child.ParentID

The view is updatable because it meets the conditions for Sql Server views to be updatable.

Data

SET IDENTITY_INSERT [dbo].[Parent] ON
INSERT INTO [dbo].[Parent] ([ID], [Name]) VALUES (1, N'Parent1')
SET IDENTITY_INSERT [dbo].[Parent] OFF

SET IDENTITY_INSERT [dbo].[Child] ON
INSERT INTO [dbo].[Child] ([ID], [Name], [ParentID]) VALUES (1, N'Child1.1', 1)
INSERT INTO [dbo].[Child] ([ID], [Name], [ParentID]) VALUES (2, N'Child1.2', 1)
SET IDENTITY_INSERT [dbo].[Child] OFF

Class model

public class Parent
{
    public Parent()
    {
        Children = new HashSet<Child>();
    }
    public int ID { get; set; }
    public string Name { get; set; }
    public byte[] RowVersion { get; set; }
    public ICollection<Child> Children { get; set; }
}

public class Child
{
    public int ID { get; set; }
    public string Name { get; set; }
    public byte[] RowVersion { get; set; }

    public int ParentID { get; set; }
    public Parent Parent { get; set; }
    public byte[] ParentRowVersion { get; set; }
}

Context

public class TestContext : DbContext
{
    public TestContext(string connectionString) : base(connectionString){ }

    public DbSet<Parent> Parents { get; set; }
    public DbSet<Child> Children { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Parent>().Property(e => e.RowVersion).IsRowVersion();
        modelBuilder.Entity<Child>().ToTable("ChildView");
        modelBuilder.Entity<Child>().Property(e => e.ParentRowVersion).IsRowVersion();
    }
}

Bringing it together

This piece of code updates a Child while a fake concurrent user updates its Parent:

using (var db = new TestContext(connString))
{
    var child = db.Children.Find(1);

    // Fake concurrent update of parent.
    db.Database.ExecuteSqlCommand("UPDATE dbo.Parent SET Name = Name + 'x' WHERE ID = 1");
    
    child.Name = child.Name + "y";
    db.SaveChanges();
}

Now SaveChanges throws the required DbUpdateConcurrencyException. When the update of the parent is commented out the child update succeeds.

I think the advantage of this method is that it's pretty independent of a data access library. All you need is an ORM that supports optimistic concurrency. A future move to EF-core won't be a problem.

Well, what you need to do is check the concurrency token (Timestamp) of the parent entity when you write to the child entity. The only challenge is that the parent timestamp is not in the child entities.

You didn't state explicitly but I'm assuming that you are using EF Core.

Looking at https://docs.microsoft.com/en-us/ef/core/saving/concurrency, it seems that EF Core will throw the concurrency exception if an UPDATE or DELETE affects zero rows. To implement concurrency testing, EF adds a WHERE clause testing the concurrency token and then tests whether or the correct number of rows were impacted by the UPDATE or DELETE.

What you could try would be to add an additional WHERE clause to the UPDATE or DELETE that tests the value of the parent's RowVersion. I think you might be able to do this using the System.Diagnostics.DiagnosticListener class to intercept the EF Core 2. There is an article on it at https://weblogs.asp.net/ricardoperes/interception-in-entity-framework-core and a discussion at Can I configure an interceptor yet in EntityFramework Core? . Evidently EF Core 3 (I think it is coming in September/October) will include an interception mechanism similar to that which was in EF pre-Core, see https://github.com/aspnet/EntityFrameworkCore/issues/15066

Hope this is useful for you.

From project to project I meet this problem on a broad platforms (not only .Net). From architecture perspective I can propose several decisions that aren't peculiar to EntityFramework. (As for me #2 is better)

OPTION 1 to implement optimistic locking approach. In general idea sounds like: "Let's update Client then check state of the parent". You have already mentioned idea "To use transaction", but optimistic locking can just reduce time needed to retain Parent entity. Something like:

var expectedVersion = _db.Parent...First().RowVersion;
using (var transactionScope = new TransactionScope(TransactionScopeOption.Required))
{
    //modify Client entity there
    ...
    //now make second check of Parent version
    if( expectedVersion != _db.Parent...First().RowVersion )
        throw new Exception(...);
    _db.SaveChanges();
}

Note! Depending on SQL server settings (isolation levels) you may need to apply to Parent entity select-for-update pls see there how to do it. How to implement Select For Update in EF Core

OPTION 2 As for me better approach instead of EF to use explicit SQL something like:

UPDATE 
    SET Client.BusinessValue = :someValue -- changes of client
    FROM Client, Parent
         WHERE Client.Id = :clientToChanges -- restrict updates by criteria
         AND Client.ParentId = Parent.Id -- join with Parent entity
         AND Parent.RowVersion = :expectedParent

After this query in .Net code you need to check that exactly 1 row was affected (0 means that Parent.Rowversion has been changed)

if(_db.ExecuteSqlCommand(sql) != 1 )
    throw new Exception();

Also try to analyse "Global Lock" design pattern with help of additional DB-table. You may read about this approach there http://martin.kleppmann.com/2016/02/08/how-to-do-distributed-locking.html