Get Entity Framework 6 use NOLOCK in its underneath SELECT statements

sql

database-performance

entity-framework

sql-hints

byNikita Barsukov·Feb 8, 2025

Enable NOLOCK behavior in Entity Framework 6 by wrapping queries in a TransactionScope with IsolationLevel.ReadUncommitted like this:

using (var scope = new TransactionScope(TransactionScopeOption.Required,
               new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted }))
{
    // Here is where the magic happens; EF query with NOLOCK
    var query = context.Entities.ToList();
    
    // Don't forget to complete the scope. Or we ruin the magic.
    scope.Complete();
}

Note: This technique mirrors NOLOCK, possibly leading to dirty reads. Use with caution, we do not want a messy database.

Custom command interception: The master disguise

Rather than manipulating your database, tag-along DbCommandInterceptor to modify SQL commands.

Getting fancy with interceptors

Create a custom interceptor inheriting DbCommandInterceptor and override the ReaderExecuting method to add your SQL hint (in this case, NOLOCK), execute the override with style:

public class NoLockInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        // Shhh! Don't tell SQL we've changed its command
        command.CommandText = command.CommandText.Replace("FROM", "FROM (NOLOCK)");
        
        // Acting innocent
        base.ReaderExecuting(command, interceptionContext);
    }
}

DbInterception.Add(new NoLockInterceptor());

No free lunch

NOLOCK offers seductive performance gains. But remember, with great power comes great responsibility. Dirty reads are a real danger, potentially reading uncommitted data or skipping rows. Handle with care!

Transaction Management: Keep your ducks in a row

Transactions offer another way to achieve NOLOCK-like behavior without the side-effects.

Use of your trusty friend, TransactionScope

TransactionScope is best for batch operations. Be like this:

using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, 
    new TransactionOptions { 
        IsolationLevel = IsolationLevel.ReadUncommitted 
    }))
{
    // Your database operations are safe here
    transactionScope.Complete();
}

Build an Isolation Level Interceptor

Define an IsolationLevelInterceptor and configure it in EF's DbConfiguration. Be elegant:

public class IsolationLevelInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuting(DbCommand command,
        DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        // Apply ReadUncommitted only on text command. Because Text does not lie!
        if (command.CommandType == CommandType.Text)
            command.Connection.BeginTransaction(IsolationLevel.ReadUncommitted);

        // Ensure DbCommandInterceptor is not left out of the party 
        base.ReaderExecuting(command, interceptionContext);
    }
}

Note to self: Dispose of the transaction properly after the command executes. No dirty dishes please!

Advanced performance tuning: For those who dare

Push the envelope with these not-so-common techniques to squeeze out max performance with Entity Framework:

Profiling and optimization: Show SQL who is boss

Use SQL Profiler to observe NOLOCK performance. Analyze to uncover bottlenecks and stop unexpected behaviors.

Boost resource utilization with SqlConnectionPool to keep up performance when using NOLOCK. Connection establishments are pesky resource hogs.

Controlling transaction lifespan: It's about timing

In IDataReader scenarios, extend DataReaderDisposing to control transaction lifespan. Clean-up your resources promptly to avoid standing in garbage.

Clean and maintainable code: Because we all love a clean house

Incorporate design patterns like UnitOfWork and Repository to wrap interception behaviors.

Consolidate your forces

Avoid sprinkling transaction logic all over your code. Cook a centralized service or repository to handle transactions for applying NOLOCK uniformly.