Why is DbUpdateConcurrencyException thrown?

I am using an SQL server and I have a table whose purpose is to hold a tree-like structure:

create table TableName (
   Id                   bigint               identity,
   Name                 nvarchar(50)         null,
   RootId               bigint               null,
   ParentId             bigint               null,
   Path                 nvarchar(100)        null,
   constraint PK_TableName primary key (Id)
)

The “Path” column value is generated by INSTEAD OF INSERT, UPDATE trigger. I am using EFCore 3.1 and each time I try to add a record into the table, I get DbUpdateConcurrencyException thrown.

What am I missing – how do I fix the problem?

btw, when I disable trigger insert passes and trigger works when I send regular INSERT command.

Thx Panagiotis for your answer. I understand the logic, but it still does not work. I have tried this:

protected virtual void MapTableName(EntityTypeBuilder<TableName> config)
{
    config.ToTable("TableName");
    config.HasKey(t => t.Id);
    config.Property(t => t.Id).ValueGeneratedOnAdd().IsConcurrencyToken();
    config.Property(t => t.Name).IsConcurrencyToken().HasMaxLength(50);
    config.Property(t => t.Description).IsConcurrencyToken().HasMaxLength(100);
    config.Property(t => t.RootId).IsConcurrencyToken();
    config.Property(t => t.ParentId).IsConcurrencyToken();
    config.Property(t => t.Path).HasMaxLength(100).ValueGeneratedOnAddOrUpdate();
    config.Property(t => t.TypeId).IsConcurrencyToken();
    config.Property(t => t.IsActive).IsConcurrencyToken();
    config.HasOne(t => t.LocationType).WithMany(t => t.TableNames).HasForeignKey(t => t.TypeId);
    config.HasOne(t => t.ParentTableName).WithMany(t => t.ChilTableNames).HasForeignKey(t => t.ParentId);
    config.HasOne(t => t.RootTableName).WithMany(t => t.ChildTableNamesAll).HasForeignKey(t => t.RootId);
}

but I get the same answer:

        "Database operation expected to affect 1 row(s) but actually affected 0 row(s). Data may have been modified or deleted since entities were loaded. See http://go.microsoft.com/fwlink/?LinkId=527962 for information on understanding and handling optimistic concurrency exceptions."

Answer

The problem is the Path.

EF Core uses optimistic concurrency by default, assuming that collisions (ie changes to the same record by another connection) are rare. To ensure the values haven’t changed since a record was loaded, EF Core will check the value of a rowversion column if one exists, or compare all original property values against the table’s values. If Path changes without EF knowing about it, it will appear there was a concurrency conflict.

The best way to fix this is to add a rowversion column to the table and add it to the model with the Timestamp attribute (the old name for rowversion). In SQL Server, a rowversion is updated automatically by the server on every update. This way, only one small binary value is used for the concurrency check :

class MyClass
{
    public int Id {get;set;}
    public string Name{get;set;}

    public string Path {get;set;}

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

Another option is to mark only a few of the properties with the ConcurrencyCheck attribute. In this case, every property except Path :

{
    [ConcurrencyCheck]
    public int Id {get;set;}
    [ConcurrencyCheck]
    public string Name{get;set;}

    public string Path {get;set;}
}