CODEWITHSUNDEEP
c#.netentity-framework.net-coreentity-framework-core
user12425844
user12425844

Reputation:

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.

Upvotes: 14

Views: 24675

Answers (1)

Farhad Zamani
Farhad Zamani

Reputation: 5861

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.

More information: Implementing NOLOCK in EntityFramework.

Upvotes: 28

Related Questions