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

by

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();
}

More Related Contents:

  1. summing values of a column based on id [closed]
  2. MSSQL Error ‘The underlying provider failed on Open’
  3. Conversion of a datetime2 data type to a datetime data type results out-of-range value
  4. Getting data from stored procedure with Entity Framework
  5. Entity framework very slow to load for first time after every compilation
  6. Writing Recursive CTE using Entity Framework Fluent syntax or Inline syntax
  7. Mapping composite keys using EF code first
  8. EF 6 – How to correctly perform parallel queries
  9. Entity-framework code is slow when using Include() many times
  10. ‘datetime2’ error when using entity framework in VS 2010 .net 4.0
  11. Delete a single record from Entity Framework?
  12. Optimistic concurrency: IsConcurrencyToken and RowVersion
  13. Entity Framework 6 transaction rollback
  14. The LINQ expression could not be translated and will be evaluated locally
  15. Dynamic query with OR conditions in Entity Framework
  16. Entity Framework | Sequence contains more than one matching element
  17. Keyword Not Supported: Metadata
  18. Why is EF generating SQL queries with unnecessary null-checks?
  19. How to Specify Primary Key Name in EF-Code-First
  20. How to Identify the primary key duplication from a SQL Server 2008 error code?
  21. Should the order of LINQ query clauses affect Entity Framework performance?
  22. Watch for a table new records in sql database
  23. How to monitor SQL Server table changes by using c#?
  24. composite key as foreign key
  25. How to catch SQLServer timeout exceptions
  26. How can I retrieve a list of parameters from a stored procedure in SQL Server
  27. What’s the best way to test SQL Server connection programmatically?
  28. Why is Asp.Net Identity IdentityDbContext a Black-Box?
  29. Unit testing with EF Core and in memory database
  30. Entity Framework Eager Load Not Returning Data, Lazy Load Does

Leave a Comment