How can I lock a table on read, using Entity Framework?

I was only able to really accomplish this by manually issuing a lock statement to a table. This does a complete table lock, so be careful with it! In my case it was useful for creating a queue that I didn't want multiple processes touching at once.

using (Entities entities = new Entities())using (TransactionScope scope = new TransactionScope()){    //Lock the table during this transaction    entities.Database.ExecuteSqlCommand("SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)");    //Do your work with the locked table here...    //Complete the scope here to commit, otherwise it will rollback    //The table lock will be released after we exit the TransactionScope block    scope.Complete();}

Update - In Entity Framework 6, especially with async / await code, you need to handle the transactions differently. This was crashing for us after some conversions.

using (Entities entities = new Entities())using (DbContextTransaction scope = entities.Database.BeginTransaction()){    //Lock the table during this transaction    entities.Database.ExecuteSqlCommand("SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)");    //Do your work with the locked table here...    //Complete the scope here to commit, otherwise it will rollback    //The table lock will be released after we exit the TransactionScope block    scope.Commit();}

The answer that @jocull provided is great. I offer this tweak:

Instead of this:

"SELECT TOP 1 KeyColumn FROM MyTable WITH (TABLOCKX, HOLDLOCK)"

Do this:

"SELECT TOP 0 NULL FROM MyTable WITH (TABLOCKX)"

This is more generic. You can make a helper method that simply takes the table name as a parameter. No need to know of the data (aka any column names), and there is no need to actually retrieve a record down the pipe (aka TOP 1)

I cannot add a comment to Andre's answer, but I am concerned about this comment "The IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that a Thread 2 cannot read from Table A if Table A has been read by Thread 1 and Thread 1 did not complete the Transaction."

The repeatable read only says that you will hold all locks until the end of a transaction. When you use this isolation level in a transaction and read a row (say the max value) a "Shared" lock is issued and will be held until the transaction completes. This shared lock will prevent another thread from updating the row (the update would try to apply an Exclusive lock on the row and that would be blocked by the existing shared lock), but it will allow another thread to read the value (the second thread will put another shared lock on the row - which is allowed (that is why they are called shared locks)). So to make the above statement correct, it would need to say "The IsolationLevel.RepeatableRead will apply a lock to all rows that are read in such a way that a Thread 2 cannot update Table A if Table A has been read by Thread 1 and Thread 1 did not complete the Transaction."

For the original question, you would need to use a repeatable read isolation level AND escalate the lock to an Exclusive lock to prevent two processes from reading and updating the same value. All the solutions would involve mapping EF to custom SQL (as escalating the lock type is not built into EF),. You could use jocull answer or you could use an update with an output clause to lock the rows (update statements always get Exclusive locks and in 2008 or above can return a result set).