Entity Framework Core: Read and Select Records using NOLOCK

How do I read/select a in Entity Framework Core with NOLOCK? (to avoid locking/blocking/deadlocks in OLTP Database). This is a sample select query.

var data= _dbContext.Set<ProductOrder>()
            .Where(c => c.ProductTypeId == this.productTypeId && c.saleYear == this.saleYear)
            .ToList();

Using Net Core 3.1 with SQL Server 2016 database.

Solution 1:

You can use NOLOCK with EF Core like this

using (new TransactionScope(TransactionScopeOption.Required, new TransactionOptions
{
    IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
    using (var db = new YourDbContext())
    {
        var data = db.Set<ProductOrder>()
            .Where(c => c.ProductTypeId == this.productTypeId && c.saleYear == this.saleYear)
            .ToList();
    }
}

Better solution:

You can create an extension method that creates a TransactionScopeOption with ReadUncommitted state:

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

Usage:

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

Note:

If you want to create a transaction with ReadUnCommited state for async methods, you should use TransactionScopeAsyncFlowOption.Enabled in your TransactionScope.

This repository can be helpful for you Github