Checking Concurrency on an Entity without updating the Row Version

Question

I have a parent entity that I need to do a concurrency check (as annotated as below)

[Timestamp]
public byte[] RowVersion { get; set; }

I have a bunch of client processes that access readonly values out of this parent entity and primarily update its child entities.

The constraint

1. Clients should not interfere with each other's work, (e.g. updating child records should not throw a concurrency exception on the parent entity).

2. I have a server process that does update this parent entity, and in this case the client process needs to throw if the parent entity has been changed.

_{Note : The client's concurrency check is sacrificial, the server's workflow is mission critical.}

The problem

I need to check (from the client process) if the parent entity has changed without updating the parents entity's row version.

It's easy enough to do a concurrency check on the parent entity in EF:

// Update the row version's original value
_db.Entry(dbManifest)
      .Property(b => b.RowVersion)
      .OriginalValue = dbManifest.RowVersion; // the row version the client originally read

// Mark the row version as modified
_db.Entry(dbManifest)
       .Property(x => x.RowVersion)
       .IsModified = true;

The IsModified = true is the deal breaker because it forces the row version to change. Or, said in context, this check from the client process will cause a row version change in the parent entity, which interferes needlessly with the other client processes' workflows.

_{A work around : I could potentially wrap the SaveChanges from the client process in a Transaction and then a subsequent read of the parent entity's row version, in-turn, rolling back if the row version has changed.}

Summary

Is there an out-of-the-box way with Entity Framework where I can SaveChanges (in the client process for the child entities) yet also check if the parent entity's row version has changed (without updating the parent entities row version).

Answer 1

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.