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.

current_timestamp returns a string date in sqlite. – C Perkins Oct 18 '19 at 11:40 — C Perkins, Oct 18 '19 at 11:40

score 2 · Accepted Answer · answered Oct 28 '19 at 08:57

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.

Excellent. I used a version of this but instead of using a timestamp, I just did `SET RowVersion = RowVersion + 1` in the trigger. Works great — Isak Savo, Oct 14 '22 at 06:49

score 0 · Answer 2 · answered Oct 18 '19 at 11:37

0

It is probably because the RowVersion value is changed between the two SaveChanges operations.

I imagine EntityFramework understands a RowVersion for SQL Server and will use only the RowVersion as the "concurrency token". On the SQLite database it probably uses all fields as concurrency tokens, i.e. update where all fields are still as we loaded them. Because of the trigger the simulated RowVersion has actually changed so it thinks there is a concurrency problem.

I'm not familiar with EF, but maybe you can tell it to exclude the simulated RowVersion as a concurrency token, or simply re-load the record between updates.

I found this: https://www.infoworld.com/article/3085390/how-to-handle-concurrency-conflicts-in-entity-framework.html

answered Oct 18 '19 at 11:37

Etherman

1,777
1
21
34

It's marked with IsRowVersion. And it is the token to be used for concurrency check. That is it's main purpose. But since the Entity is fetched, the rowversion is also fetched. And any check from Entity Framework should see that it's the same Entity with the correct RowVersion and update the record (giving it a new rowversion after the update). – Atle S Oct 18 '19 at 11:54
Does EF re-load the RowVersion column after saving the object? If it does not then the value in the object will be stale, so you may need to re-load the object before saving it again. – Etherman Oct 18 '19 at 12:08
The sample loads it directly, so that would be no issue. But yes, when it's flagged with IsRowVersion it will be reloaded once saved to keep the correct value with the entity. – Atle S Oct 22 '19 at 07:49

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

2 Answers2