Entity Framework WITH (NOLOCK)

6

We can use the NOLOCK feature in SELECT , thus avoiding locks with INSERT

SELECT COUNT(Descricao) FROM Produtos WITH (NOLOCK)

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

    
asked by anonymous 22.02.2017 / 17:08

2 answers

5

You can use raw SQL . Or set to read data that has not yet been committed which is closest to what you want.

You have 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 placed it on GitHub for future reference.

    
22.02.2017 / 17:21
4

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

The difference is that NOLOCK is applied at the table level, and setting the ISOLATION LEVEL changes the context for the whole section or transaction, ie if your query has multiple tables, all will be read with the same level of insulation.

A simple example would be (adapted from link ):

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); 
    
22.02.2017 / 17:22