Entity Framework - Pessimistic Lock

Question

What I'm trying to do is basically what NHibernate does when you do something like:

var instance = session.Get<Customer>(id, LockMode.Upgrade); 

I need to lock an entity row in a database. Why do I need it? Imagine something like an instance of a workflow that can be updated by several participants (people or processes) at the same time.

The limitations that I have do not allow blocking solutions to be optimized.

Answer 1

EF has no support for this. If you want to block some record on request, you should do something like:

 using (var scope = new TransactionScope(...)) { using (var context = new YourContext(...)) { var customer = context.ExecuteStoreQuery<Customer>("SELECT ... FROM Customers WITH (UPDLOCK) WHERE ..."); // rest of your logic while record is locked scope.Complete(); } } 

Or context.Database.SqlQuery in the case of the DbContext API.

Answer 2

You can also port your SQL code to the EDMX repository model if you don't want simple SQL in your C # code (see here ):

 <Function Name="LockTestTable" IsComposable="false"> <CommandText> SELECT NULL FROM TestTable WITH (UPDLOCK) WHERE TestTableID = @testTableID </CommandText> <Parameter Name="testTableID" Mode="In" Type="int" /> </Function> 

And call it that

 using (var scope = new TransactionScope(...)) { using (var context = new YourContext(...)) { context.LockTestTable(1); // Record locked scope.Complete(); } }