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.
