使用EF Core和MySQL实现行版本的更好方法? [英] 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; }

，然后将该列定义为

`RowVersion` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

我从EF获得了适当的乐观并发行为.就是说，我对使用时间戳不感到兴奋，因为它似乎只是第二个分辨率.并且虽然没有很大的机会让2个客户端尝试在1秒内更新同一记录，但这肯定会发生，不是吗?

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?

因此，考虑到这一点，我希望使用一个简单的整数，该整数在每次更新时原子地增加1.这样就不会丢失冲突.我将定义更改为:

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

问题是，MySQL不会自动增加它.所以我创建了一个触发器:

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;

现在这一切都可以了. EF会在需要时引发DbUpdateConcurrencyException，并且由于计时窗口而不会丢失任何更新.但是，它使用了一个触发器，我一直在阅读有关它们对性能的不利影响.

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.

那么有更好的方法吗?也许有某种方法可以覆盖DbContext的SaveChanges()以便在客户端上执行RowVersion增量，因此在数据库上只有一个更新(我假设触发器实际上每次都进行这两个更新)?

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

推荐答案

好吧，我发现了一种无需触发即可有效的策略.

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++;
    }
}

然后我像这样覆盖SaveChanges:

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

这一切都按预期进行. ConcurrencyCheck属性是使EF在UPDATE SQL的SET和WHERE子句中都包含RowVersion字段的关键.

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.

这篇关于使用EF Core和MySQL实现行版本的更好方法?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！