Entity Framework - pessimistic locking

What I am tryi开发者_如何学Pythonng to do is basically what NHibernate does when you do something like:

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

I need to lock the entity row in the database. Why I need that? Imagine something like a workflow instance that can be updated by multiple actors(people or processes) at the same time.

The constraints I have don't allow for optimistic locking solutions.

EF doesn't have support for this. If you want to lock some record by query you must 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 case of DbContext API.

You could also move your SQL Code to EDMX storage model, if you don't want to have plain 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 like this

using (var scope = new TransactionScope(...))
{
    using (var context = new YourContext(...))
    {
        context.LockTestTable(1);

        // Record locked

        scope.Complete();
    }
}