Entity Framework WITH (NOLOCK)

Question

We can use the NOLOCK in the SELECT, thus avoiding the blockages with the commands of INSERT

SELECT COUNT(Descricao) FROM Produtos WITH (NOLOCK)

Is there any way to use the WITH(NOLOCK) in the Entity Framework?

Answer 1

You can use raw SQL. Or set to read data that hasn’t been committed which is as close as you want.

Has a example on Scott Hanselman’s blog:

ProductsNewViewData viewData = new ProductsNewViewData();
using var t = new TransactionScope(TransactionScopeOption.Required,
    new TransactionOptions { 
        IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted 
    }));
viewData.Suppliers = northwind.Suppliers.ToList();
viewData.Categories = northwind.Categories.ToList();

I put in the Github for future reference.

Answer 2

You can’t exactly use the WITH (NOLOCK) in a query generated in Entity Framework, but you can change the ISOLATION LEVEL, which in practice offers the same result.

The difference is that NOLOCK is applied at table level, and set the ISOLATION LEVEL changes the context for the entire section or transaction, that is, if your query has multiple tables, they will all be read at the same isolation level.

A simple example would be (adapted from https://msdn.microsoft.com/en-us/library/dn456843(v=vs.113). aspx):

using (var conn = new SqlConnection("...")) 
{ 
   conn.Open(); 

   using (var sqlTxn = conn.BeginTransaction(System.Data.IsolationLevel.Snapshot)) 
   { 
       try 
       { 

           using (var context =  
             new NomeDoContext(conn, contextOwnsConnection: false)) 
            { 
                context.Database.UseTransaction(sqlTxn);