Entity Framework Core: Read and Select Records using NOLOCK

Question

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.

That's the same as reading under the READ UNCOMMITTED transaction isolation level, so executing the whole thing under a transaction with that isolation level will have the same, terrible effect. Do not use NOLOCK if you can help it for anything where you actually need the result to be correct; there are so many ways it can go very wrong. Consider alternatives, like snapshot isolation. — Jeroen Mostert

Farhad Zamani Farhad Zamani · Accepted Answer · 2020-08-26T18:27:42

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

Usage:

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

Github

Entity Framework Core: Read and Select Records using NOLOCK

1 Answers