Entity Framework with Nolock

Get Entity Framework 6 use NOLOCK in its underneath SELECT statements

First of all... You should NEVER EVER use NOLOCK for each and every SQL Statement. It could compromise the integrity of your data.

It’s like any other query hint a mechanism you should only use when you do something out of the ordinary.

There is no way to tell the EF Provider to render the NoLock hint. If you really need to read uncommitted data you have the following option.

Write your own EntityFramework Provider.
Use a Command Interceptor to modify the statement before it is
executed. http://msdn.microsoft.com/en-us/data/dn469464.aspx
Use a TransactionScope with IsolationLevel.ReadUncommited.

I know you said you do not want to use Transactions but it's the only out-of-the box way to read uncommitted data. Also it does not produce much overhead as each statement in SQL Server “implicitly” runs in a transaction.

using (new TransactionScope(
                    TransactionScopeOption.Required, 
                    new TransactionOptions 
                    { 
                         IsolationLevel = IsolationLevel.ReadUncommitted 
                    })) 
{
        using (var db = new MyDbContext()) { 
            // query
        }
}

EDIT:
It's important to note also that NOLOCK for Updates and Deletes (selects remain intact) has been Deprecated by Microsoft as of SQL Server 2016 and and will be removed in 'a' future release.

https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2016?view=sql-server-2017

Entity Framework with NOLOCK

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

Entity Framework Core: Read and Select Records using NOLOCK

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

NoLock in Entity Framework

Surprisingly Entity Framework inbuilt Transaction Class worked !!

public async Task<KeyValuePair<String, List<BE_Category>>> CategoryList(BE_Category obj)
{
    try
    {
        using (var categoryContext = new ModelGeneration())
        {
            using (var dbContextTransaction = categoryContext
                      .Database
                      .BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
            {
                categoryContext.Configuration.ProxyCreationEnabled = false;

                //Code

                dbContextTransaction.Commit();

                return new KeyValuePair<String, List<BE_Category>>("", data);
            }
        }
    }
    catch (Exception ex)
    {
        return new KeyValuePair<String, List<BE_Category>>(ex.Message, null);
    }
}

Reference

Entity Framework Joins with NOLOCK

from a in context.table1.ToList()
join b in context.table2.ToList on a.Id equals b.Id

That statement will materialize all items in table1 into memory, all items in table2 to memory and then join in memory for the rest of the query. Do not do that unless you just do not care about performance at all. Instead remove the ToList calls and join like so.

from a in context.table1
join b in context.table2 on a.Id equals b.Id

That would yield a join in sql server and now you can proceed with the remainder of the query in Linq. The query would then materialize the results from the database as soon as you start iterating over the results or use an extension method ToList, ToArray, AsEnumerable, or one of the other methods that retrieves a single item like Single or First etc (the Async versions are also implied here).

Secondly I do not recommend using NOLOCK, you can have unexpected results that pop up unless you know for a fact that incorrect data is not a big deal like maybe showing volatile data where no decisions are being made on that data. Now if this does not bother you and you still want to use the equivalent of NOLOCK then create a new transaction around your EF calls.

using (new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadUncommitted })) 
{
    using (var context = new MyDbContext()) 
    { 
        var result = from a in context.table1
            join b in context.table2 on a.Id equals b.Id
            // rest of your linq query here.

    }
}

Why does Entity Framework ignore TransactionScope (not adding with NOLOCK)?

So it looks like Entity Framework does respect the IsolationLevel, only it does not use the NOLOCK hint (probably because it is too database specific) and this by the way my main complaint against EF - that it is not very optimized for different database types, another example is where the new identity is saving a GUID primary key for AspNetUsers as a string (again for lack of optimization) other than that (and few other things) EF is awesome!

I could not find a solution to my problem anywhere, I definitely didn't want to make all my queries use NOLOCK - just the uncommitted ones, so I ended up combining two solutions (with some changes):

NoLockInterceptor - for adding NOLOCK on the fly (Entity Framework with NOLOCK):

/// <summary>
/// Add "WITH (NOLOCK)" hint to SQL queries, SQL Server specifc - may break queries on different databases.
/// (conditionally turn off with NoLockInterceptor.AddNoLockHintToSqlQueries = false to change on runtime)
/// <para>
/// https://stackoverflow.com/questions/926656/entity-framework-with-nolock
/// </para>
/// </summary>
public class NoLockInterceptor : DbCommandInterceptor
{
    private static readonly Regex TableAliasRegex = new Regex(
        @"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))",
        RegexOptions.Multiline | RegexOptions.IgnoreCase);

    /// <summary>
    /// Add "WITH (NOLOCK)" hint to SQL queries - unique to each thread 
    /// (set to true only when needed and then back to false)
    /// </summary>
    [ThreadStatic]
    public static bool AddNoLockHintToSqlQueries;

    public NoLockInterceptor()
    {
        // Do not use by default for all queries
        AddNoLockHintToSqlQueries = false;
    }

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

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

TransactionWrapper - to invoke the NoLockInterceptor behaviour and also useful for repeated use of transactions (http://haacked.com/archive/2009/08/18/simpler-transactions.aspx/):

/// <summary>
/// Transaction wrapper for setting pre-defined transaction scopes
/// <para>
/// http://haacked.com/archive/2009/08/18/simpler-transactions.aspx/
/// </para>
/// </summary>
public static class TransactionWrapper
{
    /// <summary>
    /// Set transaction scope and using NoLockInterceptor for adding SQL Server specific "WITH (NOLOCK)" 
    /// to ReadUncommitted isolation level transactions (not supported by Entity Framework)
    /// </summary>
    /// <param name="isolationLevel"></param>
    /// <param name="transactionScopeOption"></param>
    /// <param name="timeout"></param>
    /// <param name="action"></param>
    public static void SetScope(IsolationLevel isolationLevel, TransactionScopeOption transactionScopeOption,
        TimeSpan timeout, Action action)
    {
        var transactionOptions = new TransactionOptions { IsolationLevel = isolationLevel, Timeout = timeout };
        using (var transactionScope = new TransactionScope(transactionScopeOption, transactionOptions))
        {
            if (isolationLevel == IsolationLevel.ReadUncommitted)
                NoLockInterceptor.AddNoLockHintToSqlQueries = true;

            action();
            transactionScope.Complete();

            if (isolationLevel == IsolationLevel.ReadUncommitted)
                NoLockInterceptor.AddNoLockHintToSqlQueries = false;
        }
    }
}

Use it like this:

var timeout = TimeSpan.FromSeconds(ConfigVariables.Instance.Timeout_Transaction_Default_In_Seconds);
TransactionWrapper.SetScope(IsolationLevel.ReadUncommitted, TransactionScopeOption.Required, timeout, () =>
{
    using (var db = new MyDbContext(MyDbContextConnectionStringEntities))
    {
       // Do stuff...
    }
});

NOLOCK is now added just to queries with a ReadUncommitted transaction isolation level scopes.

What open source .Net utility projects or code libraries do you use?

Rhino.Mocks
NUnit
NHibernate
Spring.NET
Castle
My own somewhat eclectic MiscUtil
PostSharp

Use the option IsolationLevel.ReadUncommited for only one query in DbContext

You can use raw SQL query (there is a similar SqlQuery() method for EF6, as well) and specify with (nolock) table hint. Something like this:

var res = shopContext.Orders.FromSqlRaw("select sum(Total) from dbo.Orders with (nolock) where Status = 1").ToList();

However, once you will deploy this into production environment and put your code under a decent concurrent load, most probably you will not like the outcome.

UPD: For EF Core 2.2, the syntax is a bit different:

var res = shopContext.Orders.FromSql("select * from Orders with(nolock)")
  .Where(x => x.Status == 1).Sum(p => p.Total);