Implementing NOLOCK in EntityFramework

In SQL  when the records to be BULK INSERT stored large number of records SQL  table to Lock  and until all the data into SQL  will not be able to get a data table read (by default). But in SQL  itself you can read data UnCommited  from tables. This can only retrieve data that is not Commit  , and we practically do not wait for all records to enter SQL  and then our command is executed. Here's how to read data UnCommited  :

SELECT * FROM Categories WITH(NOLOCK)

How to read data as UnCommited  is that we have to put the word (WITH (NOLOCK) after the name of the relevant table   , or we can run the following command before running the query to enable reading data as UnCommited   for this transaction.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

But if we want to do the same in EntityFramework  , we can use TransactionScope  when we read data from the database and the data is received UnCommited  . To enable this feature, we can do the following:

using (var scope = new TransactionScope(TransactionScopeOption.Required,
                        new TransactionOptions()
                        {
                            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
                        }))
{
    var result = _context.Categories.ToList();
    scope.Complete();
}

And if we want to receive data async  , we must send the TransactionScopeAsyncFlowOption.Enabled parameter to the constructor of the TransactionScope  class and do the following:

using (var scope = new TransactionScope(TransactionScopeOption.Required,
                        new TransactionOptions()
                        {
                            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
                        },
                        TransactionScopeAsyncFlowOption.Enabled))
{
    var result = await _context.Categories.ToListAsync();
    scope.Complete();
}

We can write the same things as an extension method for ease of work.

public static async Task<List<T>> ToListWithNoLockAsync<T>(this IQueryable<T> query, CancellationToken cancellationToken = default)
{
    List<T> result = default;
    using (var scope = new TransactionScope(TransactionScopeOption.Required,
                            new TransactionOptions()
                            {
                                IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
                            },
                            TransactionScopeAsyncFlowOption.Enabled))
    {
        result = await query.ToListAsync(cancellationToken);
        scope.Complete();
    }
    return result;
}

This data any time for UnCommited  by EntityFramework  read can instead of ToListAsync of ToListWithNoLockAsync  use.

var categories = dbContext.Categories
                          .AsNoTracking()
                          .Where(a => a.IsDelete == false)
                          .ToListWithNoLockAsync();

You can also run this feature without creating a TransactionScope  using the following code:

this.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

If you run the code snippet above, EntityFramework  uses a TRANSACTION ISOLATION LEVEL for a process, and since ProcessID  does not change in a request, running this code once is sufficient for each request.

Note: If one of your queries runs as WITH NOLOCK  , we will run the other queries in the same way. For example, in the following code snippet, the first command changes the value of TRANSACTION ISOLATION LEVEL to READ UNCOMMITED , and the second command executes with the same TRANSACTION ISOLATION LEVEL .

var categoriesNoLock = await _context.Categories.ToListWithNoLockAsync();//READ UNCOMMITED
var categories = await _context.Categories.ToListAsync();//READ UNCOMMITED

In this repository you can see some examples of implemented methods that receive data UnCommited  .

;)

Powered by Froala Editor

Users Comments