NOLOCK with Linq to SQL

Question

Is it possible to get Linq2Sql to emit a NOLOCK in its SQL? And if so, how?

Answer 1

Yes it is, so here's the entry from my blog:

The NOLOCK hint is essentially the same as wrapping a query in a transaction whose "isolation level" is set to "read uncommitted". It means that the query doesn't care if stuff is in the process of being written to the rows it's reading from - it'll read that "dirty" data and return it as part of the result set.

Turns out that you can do the whole "read uncommitted" transaction thing using the old System.Transactions namespace introduced in .NET 2.0. Here's some sample code:

using (var txn = new TransactionScope(
    TransactionScopeOption.Required, 
    new TransactionOptions
    {
        IsolationLevel = IsolationLevel.ReadUncommitted
    }
))
{
    // Your LINQ to SQL query goes here
}

So I'm creating a new TransactionScope object and telling it to use a read-uncommitted isolation level. The query within the "using" statement now acts as if all its tables were reading with the NOLOCK hint.

Here are the first results from a Google search for "linq sql nolock":

InfoQ: Implementing NOLOCK with LINQ to SQL and LINQ to Entities

Matt Hamilton - LINQ to SQL and NOLOCK Hints : Mad Props!

Scott Hanselman's Computer Zen - Getting LINQ to SQL and LINQ to ...

Answer 2

Further to theKing's LinqPad My Extensions addition:

public static IQueryable<T> DumpNoLock<T>(this IQueryable<T> query)
{
    using (var txn = GetNewReadUncommittedScope())
    {
        return query.Dump();
    }   
}

public static System.Transactions.TransactionScope GetNewReadUncommittedScope()
{
    return new System.Transactions.TransactionScope(
        System.Transactions.TransactionScopeOption.RequiresNew,
        new System.Transactions.TransactionOptions
        {
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
        });
}
public static IQueryable<T> DumpNoLock<T>(this IQueryable<T> query, string description)
{
    using (var txn = GetNewReadUncommittedScope())
    {
        return query.Dump(description);
    }   
}

public static List<T> ToListNoLock<T>(this IQueryable<T> query)
{
    using (var txn = GetNewReadUncommittedScope())
    {
        return query.ToList();
    }   
}

public static U NoLock<T,U>(this IQueryable<T> query, Func<IQueryable<T>,U> expr)
{
    using (var txn = GetNewReadUncommittedScope())
    {
        return expr(query);
    }   
}

The last one means you can do a NOLOCK on any evaluating queries you haven't a NoLock explicitly written for (like I've got for ToListNoLock above). So, for example:

somequery.NoLock((x)=>x.Count()).Dump();

will evaluate the query with NOLOCK.

Note that you have to ensure you're evaluating the query. E.g. .NoLock((x)=>x.Distinct()).Count().Dump() won't do anything usefully different from .Distinct().Count().Dump().

Answer 3

A simple way may be to run a command on your DataContext class

using (var dataContext = new DataContext())
{
  dataContext.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

  // Your SQL query
}

Answer 4

Here is an extension method to use with LINQPAD

    public static IQueryable<T> Dump2<T>(this IQueryable<T> query)
{
    using (var txn = new System.Transactions.TransactionScope(TransactionScopeOption.RequiresNew, 
        new TransactionOptions
        {       
            IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
        }))
    {
        return query.Dump();
    }   
}

Then you can call it as:

MyTable.Where(t => t.Title = "Blah").Dump2();

Answer 5

In my case, Entity Framework 5 (based on @Soppus answer):

private FoobarEntities db = new FoobarEntities();
public FoobarController()
{
    db.Database.ExecuteSqlCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");
}