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

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 2

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();
                }
            }
        }
    }
}