Checking Concurrency on an Entity without updating the Row Version

c# database-concurrency entity-framework entity-framework-6 sql-server

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).

1
17
7/19/2019 8:07:17 AM

Accepted Answer

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 a an ORM that supports optimistic concurrency. A future move to EF-core won't be a problem.

6
8/19/2019 3:34:59 PM

Popular Answer

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.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow