Execute LINQ queries without locking any tables

At work I'm building an application which queries a SQL Server backend with LINQ to SQL, and I recently found that some of my queries were slow due to table locking. I found this excellent blog post referencing Scott Hanselman's blog post which describes how to execute LINQ queries without locking the source tables:

Deciding to go with option 1, I came up with an elegant solution for easily adding this to any LINQ queries. Let's say you are using a LINQ query like this:

var query = from f in Foo
    select new { Foo = f };

foreach (var item in query)
{
    //Do something
}

In this case, the query is actually executed in the foreach loop. By implementing an extension method, you can easily change this to use a transaction with the "Read Uncommitted" Isolation Level like so:

var query = from f in Foo
    select new { Foo = f };

foreach (var item in query.ToListNoLock())
{
    //Do something
}

The extension method is implemented like so:

public static class IEnumerableExtension
{
   public static TransactionScope CreateNoLockTransaction()
   {
       var options = new TransactionOptions
       {
           IsolationLevel = IsolationLevel.ReadUncommitted
       };
       return new TransactionScope(TransactionScopeOption.Required, options);
   }


   public static List<T> ToListNoLock<T>(this IEnumerable<T> query)
   {
       using (TransactionScope ts = CreateNoLockTransaction())
       {
           return query.ToList();
       }
   }
}

And then of course you can implement new versions of various other extension methods on IEnumerable/IQueryable, such as SingleOrDefault, FirstOrDefault, etc.


Note that this is the same as adding (NOLOCK) hints to EVERY table in your query. I do not currently know of a way to add nolock hints to individual tables.