Better way to implement a row version with EF Core and MySQL?

Question

If I use the following field in my model:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[Timestamp]
public DateTime RowVersion { get; set; }

and then define the column as

`RowVersion` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

I get the proper optimistic concurrency behavior from EF. That said, I'm not thrilled about using a timestamp for this since it appears to only be second resolution. And while there isn't a big chance of having 2 clients try to update the same record within 1 second, it certainly could happen, no?

So with that in mind I would prefer a simple integer that atomically increments by 1 on every update. This way there is no possibility of missing a conflict. I changed my definition to:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)]
[Timestamp]
public long RowVersion { get; set; }

The problem is, MySQL won't automatically increment this. So I created a trigger:

CREATE TRIGGER update_row_version BEFORE UPDATE on client 
FOR EACH ROW
SET NEW.RowVersion = OLD.RowVersion + 1;

And now this all works. EF throws the DbUpdateConcurrencyException when needed and there's no chance of missing an update due to a timing window. But, it uses a trigger and I keep reading about how bad they are for performance.

So is there a better way? Perhaps some way to override DbContext's SaveChanges() to perform the RowVersion increment on the client and therefore only have a single update on the DB (I'm assuming the trigger actually makes this two updates each time)?

Does `[Timestamp]public byte[] RowVersion` work? It's usually for MSSQL, but dunno if MySQL Provider can handle it too. Unlike MSSQL MySQL has no type for row version data type for this — Tseng, Nov 03 '16 at 07:04
As for overriding SaveChanges... no it won't work. Its only useful if you want to change the value before saving, but it have to be unchanged and only changed during the save query. The RowVersion will be used in the update query in the where condition: `WHERE Id=? AND RowVersion=?` — Tseng, Nov 03 '16 at 07:07
*within 1 second* -- The precision of `CURRENT_TIMESTAMP` is microseconds (6 digits). I wouldn't worry too much about synchronous updates. — Gert Arnold, Nov 03 '16 at 07:18
@GertArnold I'm not so sure about that. All the timestamps shown in the database are just down to the second. And more importantly, when they get converted to a DateTime in C# and then sent along via JSON, they are certainly second resolution. So for the purpose of checking it again on an update, anything shorter than seconds seems to be lost. — Jeff, Nov 03 '16 at 13:28
@Tseng I had tried the byte[] type too, but the problem is in any case with the [Timestamp] attribute EF is expecting the DB to be the one modifying the value. I need it to happen client side if I'm going to get rid of the trigger. — Jeff, Nov 03 '16 at 13:31
Well, you can try overriding SaveChanges then and increment the value on all modified records. Im not sure if that will work, as I don't know for certain if EF will use the value from the current record or the previous value (EF core tracks changes if not explicitely disabled) — Tseng, Nov 03 '16 at 14:16
I only looked in the [documentation](http://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html). The precision is what it is. Another thing is how it is *retrieved* or *displayed*. You should be able to work with this precision. — Gert Arnold, Nov 03 '16 at 14:56
@GertArnold Just to follow up on this after playing around some more... It turns out that if you declare a column as datetime, the system only stores seconds. You can later query/cast it other ways but it will always give .00 or .000, for the fractional seconds part. On the other hand, if you declare the column as datetime(3) then the system will store - and return by default - the .xxx part correctly. — Jeff, Nov 05 '16 at 14:23
OK, good finding. I think I'd prefer the built-in `DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` behavior. Keeps the code clean. — Gert Arnold, Nov 05 '16 at 18:00

Jeff · Accepted Answer · 2016-11-05T14:20:42.787

10

Ok, I figured out a strategy that seems to work well with no trigger needed.

I added a simple interface:

interface ISavingChanges
{
    void OnSavingChanges();
}

The model looks like this now:

public class Client : ISavingChanges
{
    // other fields omitted for clarity...


    [ConcurrencyCheck]
    public long RowVersion { get; set; }

    public void OnSavingChanges()
    {
        RowVersion++;
    }
}

And then I overrode SaveChanges like this:

    public override int SaveChanges()
    {
        foreach (var entity in ChangeTracker.Entries().Where(e => e.State == EntityState.Modified))
        {
            var saveEntity = entity.Entity as ISavingChanges;
            saveEntity.OnSavingChanges();
        }

        return base.SaveChanges();
    }

This is all working as expected. The ConcurrencyCheck attribute was the key to getting EF to include the RowVersion field in both the SET and WHERE clauses of the UPDATE SQL.

edited Nov 05 '16 at 14:20

answered Nov 03 '16 at 14:22

Jeff

495
4
14

2

I've had to use this example when using Pomelo.EntityFrameworkCore.MySql driver with an ASP.NET Core app. Though I prefer using Fluent API for configs like this. I've had to modify it a little compared to what is in MS docs [here](https://learn.microsoft.com/en-us/ef/core/modeling/concurrency) modelBuilder.Entity() .Property(p => p.RowVersion) .HasDefaultValue(0) .IsConcurrencyToken(); – David Guerin May 25 '17 at 14:20
What are you calling before save changes to track the 'modified' state of the entity? I can't seem to get this to work. My version is never actually incremented. – cvb Aug 07 '19 at 16:09
Probably SavingChanges event is a better place for this because both SaveChanges() and SaveChangesAsync() may be called – Alex Buchatski Jan 16 '23 at 19:51

Better way to implement a row version with EF Core and MySQL?

1 Answers1

Linked