Entity Framework with NOLOCK

Question

How can I use the NOLOCK function on Entity Framework? Is XML the only way to do this?

Answer 1

No, but you can start a transaction and set the isolation level to read uncommited. This essentially does the same as NOLOCK, but instead of doing it on a per table basis, it will do it for everything within the scope of the transaction.

If that sounds like what you want, here's how you could go about doing it...

//declare the transaction options
var transactionOptions = new System.Transactions.TransactionOptions();
//set it to read uncommited
transactionOptions.IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted;
//create the transaction scope, passing our options in
using (var transactionScope = new System.Transactions.TransactionScope(
    System.Transactions.TransactionScopeOption.Required, 
    transactionOptions)
)

//declare our context
using (var context = new MyEntityConnection())
{
    //any reads we do here will also read uncomitted data
    //...
    //...
    //don't forget to complete the transaction scope
    transactionScope.Complete();
}

Answer 2

Extension methods can make this easier

public static List<T> ToListReadUncommitted<T>(this IQueryable<T> query)
{
    using (var scope = new TransactionScope(
        TransactionScopeOption.Required, 
        new TransactionOptions() { 
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        List<T> toReturn = query.ToList();
        scope.Complete();
        return toReturn;
    }
}

public static int CountReadUncommitted<T>(this IQueryable<T> query)
{
    using (var scope = new TransactionScope(
        TransactionScopeOption.Required, 
        new TransactionOptions() { 
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted }))
    {
        int toReturn = query.Count();
        scope.Complete();
        return toReturn;
    }
}

Answer 3

If you need something at large, the best way we found which less intrusive than actually starting a transactionscope each time, is to simply set the default transaction isolation level on your connection after you've created your object context by running this simple command:

this.context.ExecuteStoreCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;");

http://msdn.microsoft.com/en-us/library/aa259216(v=sql.80).aspx

With this technique, we were able to create a simple EF provider that creates the context for us and actually runs this command each time for all of our context so that we're always in "read uncommitted" by default.

Answer 4

Though I absolutely agreed that using Read Uncommitted transaction isolation level is the best choice, but some time you forced to use NOLOCK hint by request of manager or client and no reasons against this accepted.

With Entity Framework 6 you can implement own DbCommandInterceptor like this:

public class NoLockInterceptor : DbCommandInterceptor
{
    private static readonly Regex _tableAliasRegex = 
        new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))", 
            RegexOptions.Multiline | RegexOptions.IgnoreCase);

    [ThreadStatic]
    public static bool SuppressNoLock;

    public override void ScalarExecuting(DbCommand command, 
        DbCommandInterceptionContext<object> interceptionContext)
    {
        if (!SuppressNoLock)
        {
            command.CommandText = 
                _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
        }
    }

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        if (!SuppressNoLock)
        {
            command.CommandText = 
                _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");
        }
    }
}

With this class in place, you can apply it on application start:

DbInterception.Add(new NoLockInterceptor());

And conditionally turn off adding of NOLOCK hint into queries for current thread:

NoLockInterceptor.SuppressNoLock = true;

Answer 5

Enhancing on Doctor Jones's accepted answer and using PostSharp;

First "ReadUncommitedTransactionScopeAttribute"

[Serializable]
public class ReadUncommitedTransactionScopeAttribute : MethodInterceptionAspect
{
    public override void OnInvoke(MethodInterceptionArgs args)
    {
        //declare the transaction options
        var transactionOptions = new TransactionOptions();
        //set it to read uncommited
        transactionOptions.IsolationLevel = IsolationLevel.ReadUncommitted;
        //create the transaction scope, passing our options in
        using (var transactionScope = new TransactionScope(TransactionScopeOption.Required, transactionOptions))
        {
            //declare our context
            using (var scope = new TransactionScope())
            {
                args.Proceed();
                scope.Complete();
            }
        }
    }
}

Then whenever you need it,

    [ReadUncommitedTransactionScope()]
    public static SomeEntities[] GetSomeEntities()
    {
        using (var context = new MyEntityConnection())
        {
            //any reads we do here will also read uncomitted data
            //...
            //...

        }
    }

Being Able to add "NOLOCK" with an interceptor is also nice but will not work when connecting to other database systems like Oracle as such.

Answer 6

To get round this I create a view on the database and apply NOLOCK on the view's query. I then treat the view as a table within EF.

Answer 7

With the introduction of EF6, Microsoft recommends using BeginTransaction() method.

You can use BeginTransaction instead of TransactionScope in EF6+ and EF Core

using (var ctx = new ContractDbContext())
using (var transaction = ctx.Database.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
{
    //any reads we do here will also read uncommitted data
}

Answer 8

No, not really - Entity Framework is basically a fairly strict layer above your actual database. Your queries are formulated in ESQL - Entity SQL - which is first of all targeted towards your entity model, and since EF supports multiple database backends, you can't really send "native" SQL directly to your backend.

The NOLOCK query hint is a SQL Server specific thing and won't work on any of the other supported databases (unless they've also implemented the same hint - which I strongly doubt).

Marc

Answer 9

One option is to use a stored procedure (similar to the view solution proposed by Ryan) and then execute the stored procedure from EF. This way the stored procedure performs the dirty read while EF just pipes the results.

Answer 10

Necro-ing this thread because it is the first that comes up in a google search. There have been changes to how EF structures its queries. This regex will also cover joins.

as an aside, transaction level read uncommitted works (examples are above with transactionscope), however it will still block updates. Using the table level nolock will allow for updates to continue. Be very aware and do some research on the effects of using dirty reads before doing this

(?<tableAlias>((FROM)|(JOIN))\s\[([^\s]+)\]\sAS\s\[([^\s]+)\](?!\sWITH\s\(NOLOCK\)))

you can test it with any regex online tester (like regex101.com) with this sample

FROM [table1] AS [t]
INNER JOIN [table2] AS [t2] ON ([t].[ID] = [t2].[ID])
INNER JOIN [table3] AS [t3] WITH (NOLOCK) ON ([t].[ID] = [t3].[ID])

I also cleaned up the example but left out the bool flag for trigger. feel free to add if you want

public class NoLockInterceptor : DbCommandInterceptor
    {       

        public override InterceptionResult<DbDataReader> ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result)
        {           
            
            var finishedresult = base.ReaderExecuting(command.NoLockCommand(), eventData, result);    
            return finishedresult;  
        }

        public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<DbDataReader> result, CancellationToken cancellationToken = default)
        {
            
            var finishedresult = base.ReaderExecutingAsync(command.NoLockCommand(), eventData, result, cancellationToken);
            return finishedresult; 
        }

        public override InterceptionResult<object> ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResult<object> result)
        {
            var finishedresult = base.ScalarExecuting(command.NoLockCommand(), eventData, result);
            return finishedresult;
        }

        public override ValueTask<InterceptionResult<object>> ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResult<object> result, CancellationToken cancellationToken = default)
        {
            var finishedresult = base.ScalarExecutingAsync(command.NoLockCommand(), eventData, result, cancellationToken);
            return finishedresult;
        }

        
    } 

    public static class DbCommandExtensions
    {
        private static Regex _tableAliasRegex = new Regex(@"(?<tableAlias>((FROM)|(JOIN))\s\[([^\s]+)\]\sAS\s\[([^\s]+)\](?!\sWITH\s\(NOLOCK\)))",
                RegexOptions.Multiline | RegexOptions.IgnoreCase);

        public static DbCommand NoLockCommand(this DbCommand command)        {          

            string updatedCommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)");

            command.CommandText = updatedCommandText;
            return command;
        }
    }