使用EF Core和MySQL实现行版本的更好方法?

本文介绍了使用EF Core和MySQL实现行版本的更好方法?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

如果我在模型中使用以下字段:

If I use the following field in my model:

[DatabaseGenerated(DatabaseGeneratedOption.Computed)] 1699944966 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)] 1699944966 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.