CODEWITHSUNDEEP
c#mysqlasp.net-coreentity-framework-core
Jeff
Jeff

Reputation: 545

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

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)?

Upvotes: 8

Views: 8941

Answers (1)

Jeff
Jeff

Reputation: 545

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.

Upvotes: 10

Related Questions