Using NoLock in EF Core

I typically will include this extension method as an option for using NoLock (read uncommitted) within my Entity Framework projects. I have been using this latest version of the IQueryableExtensions class listed below with EF Core 6 / .Net 6.

Often for performance reasons, a SQL query will be written using WITH (NOLOCK) at the table level or using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the query level to perform what is commonly described as a 'dirty read'. The primary advantages of using read uncommitted isolation are the reduced potential for blocking and deadlocking, which can often result in a perceived increase in performance.

With Entity Framework, we can accomplish this by wrapping the EF query in a TransactionScope and setting the IsolationLevel to ReadUncommitted. Note in the Async versions, we also set the TransactionScopeAsyncFlowOption to Enabled.

I have had several versions of this file over the years, and this latest one is an adaptation of an example provided by farhadzm found here on GitHub: https://github.com/farhadzm/EFCoreWithNoLock

	public static class IQueryableExtensions
	{

		#region Public Methods

		public static int CountWithNoLock<T>(this IQueryable<T> query, Expression<Func<T, bool>> expression = null)
		{
			using (var scope = CreateTransaction())
			{
				if (expression is object)
				{
					query = query.Where(expression);
				}
				int toReturn = query.Count();
				scope.Complete();
				return toReturn;
			}
		}

		public static async Task<int> CountWithNoLockAsync<T>(this IQueryable<T> query, CancellationToken cancellationToken = default, Expression<Func<T, bool>> expression = null)
		{
			using (var scope = CreateTransactionAsync())
			{
				if (expression is object)
				{
					query = query.Where(expression);
				}
				int toReturn = await query.CountAsync(cancellationToken);
				scope.Complete();
				return toReturn;
			}
		}

		public static T FirstOrDefaultWithNoLock<T>(this IQueryable<T> query, Expression<Func<T, bool>> expression = null)
		{
			using (var scope = CreateTransaction())
			{
				if (expression is object)
				{
					query = query.Where(expression);
				}
				T result = query.FirstOrDefault();
				scope.Complete();
				return result;
			}
		}

		public static async Task<T> FirstOrDefaultWithNoLockAsync<T>(this IQueryable<T> query, CancellationToken cancellationToken = default, Expression<Func<T, bool>> expression = null)
		{
			using (var scope = CreateTransactionAsync())
			{
				if (expression is object)
				{
					query = query.Where(expression);
				}
				T result = await query.FirstOrDefaultAsync(cancellationToken);
				scope.Complete();
				return result;
			}
		}

		public static List<T> ToListWithNoLock<T>(this IQueryable<T> query, Expression<Func<T, bool>> expression = null)
		{
			List<T> result = default;
			using (var scope = CreateTransaction())
			{
				if (expression is object)
				{
					query = query.Where(expression);
				}
				result = query.ToList();
				scope.Complete();
			}
			return result;
		}

		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 = CreateTransactionAsync())
			{
				if (expression is object)
				{
					query = query.Where(expression);
				}
				result = await query.ToListAsync(cancellationToken);
				scope.Complete();
			}
			return result;
		}

		#endregion

		#region Private Methods

		private static TransactionScope CreateTransactionAsync()
		{
			return new TransactionScope(TransactionScopeOption.Required,
				new TransactionOptions()
				{
					IsolationLevel = IsolationLevel.ReadUncommitted
				},
				TransactionScopeAsyncFlowOption.Enabled);
		}

		private static TransactionScope CreateTransaction()
		{
			return new TransactionScope(TransactionScopeOption.Required,
				new TransactionOptions()
				{
					IsolationLevel = IsolationLevel.ReadUncommitted
				});
		}

		#endregion

	}