NOLOCK with Linq to SQL

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

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 ...

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().

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
}

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

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