How to safely increment a counter in the Entity Framework

Question

Let's say I have a table that keeps track of the number of uploaded files, and I show this table in my code through EF. When the file is uploaded, I want to update the account by one. 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 studied the actual SQL that is generated by these statements, I noticed that the increment does not occur on the DB side, but instead in my memory. Therefore, my program reads the counter value in the database (for example, 2003), performs the calculation (the new value is 2004), and then explicitly updates the line with the new 2004 Count value. Obviously, this is not safe from a concurrency perspective.

I was hoping that in the end the request would look like this:

 UPDATE Files SET Count = Count + 1 WHERE FileId=3 

Can anyone suggest how I can do this? I would prefer not to block the line before reading, and then unlock after updating, because I am afraid to block reading by other users (unless it is incorrect to block the line for writing only, but not for reading blocks).

I also looked at the execution of the Entity SQL command, but it turned out that Entity SQL does not support updates.

thanks

Answer 1

Of course, you can call the stored procedure using EF. Write sproc with the SQL that you are showing, then create a function import in your EF model mapped to the specified sproc.

Answer 2

You will need to do some locking in order to make this work. But you can minimize the amount of blocking.

When you read the counter and want to update it, you must block it, this can be done by placing the read and update inside the transaction area. This will protect you from race conditions.

When you read a value and you just want to read it, you can do it with ReadUncommited transaction isolation level, this reading will then not be blocked by the read / write lock above.