Proper RowVersion simulation in SqLite with conversion to ulong on Entity Framework Core

Question

I'm trying to make RowVersion work properly both on SqLite and SqlServer with easy query on the rowversion column. To be able to do that I need to convert the rowversion column to ulong instead of byte[] and still have it work properly.

public abstract class VersionEntity
{
    public ulong RowVersion { get; set; }
}

public class Observation : VersionEntity
{
    public Guid Id { get; set; }
    public Guid TaskId { get; set; }
    public string Description { get; set; }
    public DateTime DueDate { get; set; }
    public Severity Severity { get; set; }
}

public class TestDbContext : DbContext
{
    public static string ConnectionString { get; set; } = "Data Source=dummy.db";
    public DbSet<Observation> Observation { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
         modelBuilder.Entity<Observation>().HasKey(o => o.Id);
         modelBuilder.Entity<Observation>().Property(o => o.RowVersion).HasConversion(new NumberToBytesConverter<ulong>()).IsRowVersion();
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlite(ConnectionString);
        optionsBuilder.UseLazyLoadingProxies();
    }

}

Inside my first add migration, changed RowVersion to have rowVersion: true (did not add automatically). Also added

private string _triggerQuery = @"CREATE TRIGGER Set{0}RowVersion{1}
   AFTER {1} ON {0}
   BEGIN
      UPDATE {0}
      SET RowVersion = current_timestamp
      WHERE rowid = NEW.rowid;
   END
";

migrationBuilder.Sql(String.Format(_triggerQuery, tableName, "UPDATE"));
migrationBuilder.Sql(String.Format(_triggerQuery, tableName, "INSERT"));

This way it's created with triggers to simulate SqlServer RowVersion incremental global value.

Migration works, and first save works

context.Database.Migrate();
var id = Guid.NewGuid();
context.Observation.Add(new Observation
{
    Id = id,
    Description = "Test description1",
    TaskId = Guid.NewGuid(),
    Severity = Severity.Low,
    DueDate = DateTime.Now
});
context.Observation.Add(new Observation
{
    Id = Guid.NewGuid(),
    Description = "Test description2",
    TaskId = Guid.NewGuid(),
    Severity = Severity.Low,
    DueDate = DateTime.Now
});
context.SaveChanges(); // This works, and saves data
var observation = context.Observation.FirstOrDefault(o => o.Id == id);
observation.Description = "changed.."; // Checking here will show a value on RowVersion property
context.SaveChanges(); // This fail with concurrency error

Concurrency error: 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.

I can't see why this should be a problem. Anyone have any idea why this does not work? The fetched Entity seems to have a value on the RowVersion property. But when it's saved, it thinks it has changed.

Answer 1

I gave up trying to use IsRowVersion with SqLite.

Ended up setting the type of RowVersion to long, and using IsConcurrencyToken().ValueGeneratedOnAddOrUpdate().HasDefaultValue(0); instead of IsRowVersion().HasConversion(..)

Also used julianday instead of current_timestamp

private static string _triggerQuery = @"CREATE TRIGGER Set{0}RowVersion{1}
        AFTER {1} ON {0}
        BEGIN
            UPDATE {0}
            SET RowVersion = CAST(ROUND((julianday('now') - 2440587.5)*86400000) AS INT)
            WHERE rowid = NEW.rowid;
        END
    ";

migrationBuilder.Sql(String.Format(_triggerQuery, tableName, "UPDATE"));
migrationBuilder.Sql(String.Format(_triggerQuery, tableName, "INSERT"));

Now it works properly, and it goes down to ms for changes. This way it's also easy to query all changes after a point in time.