Updating entity in EF Core application with SQLite gives DbUpdateConcurrencyException

Question

I try to use optimistic concurrency check in EF Core with SQLite. The simplest positive scenario (even without concurrency itself) gives me Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException: '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.

Entity:

public class Blog
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public byte[] Timestamp { get; set; }
}

Context:

internal class Context : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(@"Data Source=D:\incoming\test.db");
        ///optionsBuilder.UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .HasKey(p => p.Id);

        modelBuilder.Entity<Blog>()
            .Property(p => p.Timestamp)
            .IsRowVersion()
            .HasDefaultValueSql("CURRENT_TIMESTAMP");
    }
}

Sample:

internal class Program
{
    public static void Main(string[] args)
    {
        var id = Guid.NewGuid();
        using (var db = new Context())
        {
            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();
            db.Blogs.Add(new Blog { Id = id, Name = "1" });
            db.SaveChanges();
        }

        using (var db = new Context())
        {
            var existing = db.Blogs.Find(id);
            existing.Name = "2";
            db.SaveChanges(); // Exception thrown: 'Microsoft.EntityFrameworkCore.DbUpdateConcurrencyException'
        }

    }
}

I suspect it's something to do with the data types between EF and SQLite. Logging gives me the following query on my update:

Executing DbCommand [Parameters=[@p1='2bcc42f5-5fd9-4cd6-b0a0-d1b843022a4b' (DbType = String), @p0='2' (Size = 1), @p2='0x323031382D31302D30372030393A34393A3331' (Size = 19) (DbType = String)], CommandType='Text', CommandTimeout='30']
UPDATE "Blogs" SET "Name" = @p0
WHERE "Id" = @p1 AND "Timestamp" = @p2;

But the column types are BLOB for both Id and Timestamp (SQLite does not provide UUID and timestamp column types):

---

At the same time if I use SQL Server (use commented connection string + remove .HasDefaultValueSql("CURRENT_TIMESTAMP")), sample works correctly and updates timestamp in the DB.

Used packages:

<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="2.1.4" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite.Core" Version="2.1.4" />

Have I configured the model for concurrency check wrong? That drives me crazy that I can't make it work with this simplest scenario.

---

UPDATE: how I finally made it work. Here only idea is shown, but probably it helps anybody:

public class Blog
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public long Version { get; set; }
}

internal class Context : DbContext
{
    public DbSet<Blog> Blogs { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(@"Data Source=D:\incoming\test.db");
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Blog>()
            .HasKey(p => p.Id);

        modelBuilder.Entity<Blog>()
            .Property(p => p.Version)
            .IsConcurrencyToken();
    }
}

internal class Program
{
    public static void Main(string[] args)
    {
        var id = Guid.NewGuid();
        long ver;
        using (var db = new Context())
        {
            db.Database.EnsureDeleted();
            db.Database.EnsureCreated();
            var res = db.Blogs.Add(new Blog { Id = id, Name = "xxx", Version = DateTime.Now.Ticks});
            db.SaveChanges();
        }

        using (var db = new Context())
        {
            var existing = db.Blogs.Find(id);
            existing.Name = "yyy";
            existing.Version = DateTime.Now.Ticks;
            db.SaveChanges(); // success
        }

        using (var db = new Context())
        {
            var existing = db.Blogs.Find(id);
            existing.Name = "zzz";
            existing.Version = DateTime.Now.Ticks;
            db.SaveChanges(); // success
        }

        var t1 = Task.Run(() =>
        {
            using (var db = new Context())
            {
                var existing = db.Blogs.Find(id);
                existing.Name = "yyy";
                existing.Version = DateTime.Now.Ticks;
                db.SaveChanges();
            }
        });

        var t2 = Task.Run(() =>
        {
            using (var db = new Context())
            {
                var existing = db.Blogs.Find(id);
                existing.Name = "zzz";
                existing.Version = DateTime.Now.Ticks;
                db.SaveChanges();
            }
        });

        Task.WaitAll(t1, t2); // one of the tasks throws DbUpdateConcurrencyException
    }
}

Answer 1

I was not able to make it work with CURRENT_TIMESTAMP because it ends up with the string displayed in both DB Browser for SQLite and EF. Having entity property of type long leads to the 2022 value and prevents me to save the column because obviously 2022 != 2022-12-09 14:03:14. It also has seconds only which is bad for concurrency.

So I have decided to go with the other solution

byte[] + SetDefaultValueSql("randomblob(8)") + update trigger

The property itself represents regular version column

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

Now we need to add some reflection magic in order to assign default value on insert for all entities at once:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    var versionProperties = builder.Model
        .GetEntityTypes()
        .SelectMany(t => t.GetProperties().Where(p => p.ClrType == typeof(byte[]) && p is { ValueGenerated: ValueGenerated.OnAddOrUpdate, IsConcurrencyToken: true }));

    foreach (var property in versionProperties)
    {
        property.SetDefaultValueSql("randomblob(8)");
    }
}

And the last step: we need to force DB to update the Version column on row update. We need a trigger for it but I didn't want any manual work in migration so here is automation based on the migration and seeding process:

public class AppDbContextSeedingService
{
    private readonly string VersionColumnName = nameof(EntityBase.Version);
    private readonly string IdColumnName = nameof(EntityBase.Id);
    private readonly AppDbContext _dbContext;
    public AppDbContextSeedingService(AppDbContext dbContext)
    {
        _dbContext = dbContext;
    }
    public async Task EnsureMigratedAndSeededAsync(CancellationToken cToken = default)
    {
        await _dbContext.Database.MigrateAsync(cancellationToken: cToken).ConfigureAwait(false);
        var triggerNames = await _dbContext.Database.SqlQueryRaw<string>("SELECT name FROM sqlite_master WHERE type = 'trigger'")            .ToArrayAsync(cToken).ConfigureAwait(false);;
        foreach (var tableName in typeof(AppDbContext).GetProperties().Where(p => p.PropertyType.IsGenericType && p.PropertyType.GetGenericTypeDefinition() == typeof(DbSet<>)).Select(p => p.Name))
        {
            var updateTriggerName = GetUpdateTriggerName(tableName);
            if(triggerNames.Contains(updateTriggerName))
                continue;
            await _dbContext.Database.ExecuteSqlRawAsync(
                $"CREATE TRIGGER {updateTriggerName} AFTER UPDATE ON {tableName} BEGIN UPDATE {tableName} SET {VersionColumnName} = randomblob(8) WHERE {IdColumnName} = NEW.{IdColumnName}; END", cToken).ConfigureAwait(false);
        }
    }
    private static string GetUpdateTriggerName(string tableName) => $"{tableName}_OnUpdate";
}

So we get the list of existing triggers then iterate through all the DBSet<> properties and insert missing triggers.

But there is limitation from SQLite side.

EF Core makes this request during update:

UPDATE "PrinterOperations" SET "CommandJson" = @p0
WHERE "Id" = @p1 AND "Version" = @p2
RETURNING "Version";

So the idea is to return updated Version value after column update using the new RETURNING clause. https://www3.sqlite.org/lang_returning.html

Unfortunately:

The RETURNING clause only returns rows that are directly modified by the DELETE, INSERT, or UPDATE statement. The RETURNING clause does not report any additional database changes caused by foreign key constraints or triggers.

It means that item returned after the SaveChanges() will have the same Version value as it was before because SQLite returns the old value instead of the new one. So you will not be able to change and save it again. You can easily check it with the following line:
var sqlQueryRaw = BitConverter.ToString(dbContext.Database.SqlQueryRaw<byte[]>("UPDATE PrinterOperations SET CommandJson = '111111' WHERE Id = '73cf2572-4ba3-4693-922c-b062be47707c' RETURNING Version").ToArray().First());

Just run it couple of times

Answer 2

I have been using Ivan's answer with great success up to this point. When I updating to EntitiyFrameworkCore 3.1, though, I started getting this warning:

The property '{column name}' on entity type '{entity name}' is a collection or enumeration type with a value converter but with no value comparer. Set a value comparer to ensure the collection/enumeration elements are compared correctly.

To address this, I enhanced his solution by adding:

property.SetValueComparer(new ValueComparer<byte[]>(
    (c1, c2) => c1.SequenceEqual(c2),
    c => c.Aggregate(0, (a, v) => HashCode.Combine(a, v.GetHashCode())),
    c => c.ToArray()));

(based off of a response to a GitHub issue)

Answer 3

Inspired by this thread on GitHub and the Ivan's answer I wrote this code to ensure on my unit testing to mimic the SQL Server concurrency.

var connection = new SqliteConnection("DataSource=:memory:");

var options = new DbContextOptionsBuilder<ActiveContext>()
               .UseSqlite(connection)
               .Options;

var ctx = new ActiveContext(options);

if (connection.State != System.Data.ConnectionState.Open)
{
    connection.Open();

    ctx.Database.EnsureCreated();

    var tables = ctx.Model.GetEntityTypes();

    foreach (var table in tables)
    {
        var props = table.GetProperties()
                        .Where(p => p.ClrType == typeof(byte[])
                        && p.ValueGenerated == Microsoft.EntityFrameworkCore.Metadata.ValueGenerated.OnAddOrUpdate
                        && p.IsConcurrencyToken);

        var tableName = table.Relational().TableName;

        foreach (var field in props)
        {
            string[] SQLs = new string[] {
                $@"CREATE TRIGGER Set{tableName}_{field.Name}OnUpdate
                AFTER UPDATE ON {tableName}
                BEGIN
                    UPDATE {tableName}
                    SET RowVersion = randomblob(8)
                    WHERE rowid = NEW.rowid;
                END
                ",
                $@"CREATE TRIGGER Set{tableName}_{field.Name}OnInsert
                AFTER INSERT ON {tableName}
                BEGIN
                    UPDATE {tableName}
                    SET RowVersion = randomblob(8)
                    WHERE rowid = NEW.rowid;
                END
                "
            };

            foreach (var sql in SQLs)
            {
                using (var command = connection.CreateCommand())
                {
                    command.CommandText = sql;
                    command.ExecuteNonQuery();
                }
            }
        }
    }
}

Answer 4

Looks like EF Core SQLite provider does not handle properly [TimeStamp] (or IsRowVersion()) marked byte[] properties when binding them to SQL query parameters. It uses the default byte[] to hex string conversion which is not applicable in this case - the byte[] actually is a string.

First consider reporting it to their issue tracker. Then, until it gets resolved (if ever), as a workaround you can use the following custom ValueConverter:

class SqliteTimestampConverter : ValueConverter<byte[], string>
{
    public SqliteTimestampConverter() : base(
        v => v == null ? null : ToDb(v),
        v => v == null ? null : FromDb(v))
    { }
    static byte[] FromDb(string v) =>
        v.Select(c => (byte)c).ToArray(); // Encoding.ASCII.GetString(v)
    static string ToDb(byte[] v) =>
        new string(v.Select(b => (char)b).ToArray()); // Encoding.ASCII.GetBytes(v))
}

Unfortunately there is no way to tell EF Core to use it only for parameters, so after assigning it with .HasConversion(new SqliteTimestampConverter()), now the db type is considered string, so you need to add .HasColumnType("BLOB").

The final working mapping is

    modelBuilder.Entity<Blog>()
        .Property(p => p.Timestamp)
        .IsRowVersion()
        .HasConversion(new SqliteTimestampConverter())
        .HasColumnType("BLOB")
        .HasDefaultValueSql("CURRENT_TIMESTAMP");

You can avoid all that by adding the following custom SQLite RowVersion "convention" at the end of your OnModelCreating:

if (Database.IsSqlite())
{
    var timestampProperties = modelBuilder.Model
        .GetEntityTypes()
        .SelectMany(t => t.GetProperties())
        .Where(p => p.ClrType == typeof(byte[])
            && p.ValueGenerated == ValueGenerated.OnAddOrUpdate
            && p.IsConcurrencyToken);

    foreach (var property in timestampProperties)
    {
        property.SetValueConverter(new SqliteTimestampConverter());
        property.Relational().DefaultValueSql = "CURRENT_TIMESTAMP";
    }
}

so your property configuration could be trimmed down to

modelBuilder.Entity<Blog>()
    .Property(p => p.Timestamp)
    .IsRowVersion();

or totally removed and replaced with data annotation

public class Blog
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    [Timestamp]
    public byte[] Timestamp { get; set; }
}

Answer 5

This is because you use Guid:

public Guid Id { get; set; }

This issue is discussed and reproduced in Gitub:

The error here is due to ApplicationUser.ConcurrencyStamp property. ApplicationUser in identity uses ConcurrencyStamp of type Guid for concurrency. When creating new class it sets the value to NewGuid(). When you create new ApplicationUser like that and set its state to Modified EF Core does not have data about what was ConcurrencyStamp in database. Hence it will use whatever is the value set on the item (which will be NewGuid()) Since this value differ from value in database and it is used in where clause of update statement, exception is thrown that 0 rows modified when expected 1.

When updating entity with concurrency token you cannot create new object and send update directly. You must retrieve record from database (so that you have value of ConcurrencyStamp) then update the record and call SaveChanges. Since the ApplicationUser.ConcurrencyStamp is client side concurrency token you also need to generate a NewGuid() while updating the record. So it can update the value in database.

Find more info about how to deal with ApplicationUser.ConcurrencyStamp here.