如何安全地增加实体框架中的计数器
假设我有一个跟踪文件下载次数的表，并且我通过 EF 将该表公开给我的代码。下载文件后，我想将计数更新为 1。起初，我写了这样的内容：
var fileRecord = (from r in context.Files where r.FileId == 3 select r).Single();
fileRecord.Count++;
context.SaveChanges();
但是当我检查这些语句生成的实际 SQL 时，我注意到增量不是发生在数据库端，而是发生在我的内存中。因此，我的程序读取数据库中计数器的值（例如 2003），执行计算（新值为 2004），然后使用新的 Count 值 2004 显式更新该行。显然，从并发角度来看，这并不安全。
我希望查询最终会看起来像：
UPDATE Files SET Count = Count + 1 WHERE FileId=3
任何人都可以建议我如何完成这个任务吗？我不想在读取之前锁定该行，然后在更新后解锁，因为我害怕阻止其他用户的读取（除非有某种方法只锁定一行以进行写入而不是阻止读取）。
我还考虑过执行 Entity SQL 命令，但 Entity SQL 似乎不支持更新。
谢谢
Let's say I have a table that tracks the number of times a file was downloaded, and I expose that table to my code via EF. When the file is downloaded I want to update the count by one. At first, I wrote something like this:
var fileRecord = (from r in context.Files where r.FileId == 3 select r).Single();
fileRecord.Count++;
context.SaveChanges();
But then when I examined the actual SQL that is generated by these statements I noticed that the incrementing isn't happening on the DB side but instead in my memory. So my program reads the value of the counter in the database (say 2003), performs the calculation (new value is 2004) and then explicitly updates the row with the new Count value of 2004. Clearly this isn't safe from a concurrency perspective.
I was hoping the query would end up looking instead like:
UPDATE Files SET Count = Count + 1 WHERE FileId=3
Can anyone suggest how I might accomplish this? I'd prefer not to lock the row before the read and then unlock after the update because I'm afraid of blocking reads by other users (unless there is someway to lock a row only for writes but not block reads).
I also looked at doing a Entity SQL command but it appears Entity SQL doesn't support updates.
Thanks
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当然欢迎您使用 EF 调用存储过程。使用您显示的 SQL 编写存储过程，然后在映射到所述存储过程的 EF 模型中创建函数导入。
You're certainly welcome to call a stored procedure with EF. Write a sproc with the SQL you show then create a function import in your EF model mapped to said sproc.
您需要进行一些锁定才能使其正常工作。但您可以最大限度地减少锁定量。
当您读取计数并想要更新它时，必须锁定它，这可以通过将读取和更新放在事务范围内来完成。这将保护您免受竞争条件的影响。
当你读取该值并且只想读取它时，可以使用 ReadUncommited 的事务隔离级别来执行此操作，这样该读取就不会被上面的读/写锁锁定。
You will need to do some locking in order to get this to work. But you can minimise the amount of locking.
When you read the count and you want to update it, you must lock it, this can be done by placing the read and the update inside a transaction scope. This will protect you from race conditions.
When you read the value and you just want to read it, you can do this with a transaction isolation level of ReadUncommited, this read will then not be locked by the read/write lock above.