NOLOCK with Linq to SQL

Question

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

Answer 1

Yes, it is, here is a post from my blog :

The NOLOCK hint is essentially the same as wrapping a request in a transaction whose isolation level is set to read uncommitted. This means that the query does not care if the material is in the process of writing the lines that it reads - it will read the "dirty" data and return it as part of the result set.

It turns out that you can do all the “read uncommitted” transactions using the old System.Transactions namespace introduced in .NET 2.0. Here is a sample code:

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

So, I am creating a new TransactionScope object and telling it to use the level of uncommitted recording. The query in the using statement acts as if all its tables were reading with the NOLOCK hint.

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

InfoQ: NOLOCK implementation with LINQ to SQL and LINQ for objects

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

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

Answer 2

In addition to King LinqPad My Extensions :

 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 latter means that you can do NOLOCK for any evaluative queries for which NOLOCK is not explicitly specified (for example, I got ToListNoLock above). So for example:

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

will evaluate the request using NOLOCK .

Please note that you must ensure that you are evaluating the request. For example. .NoLock((x)=>x.Distinct()).Count().Dump() will not be anything useful different from .Distinct().Count().Dump() .

Answer 3

An easy way would be to run a command in 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 for 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 name 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"); }