In our SQL queries here at work, we use the NOLOCK hint a lot. Not locking rows during reads is very important to us, and our transactions are seldom rolled back so the queries generally return data that's accurate enough for the tasks at hand.

Now that we're starting to investigate LINQ to SQL, then, we're realising that the queries themselves don't allow any kind of hints. They're generated from your code and passed to the server without any intervention from you.

How, then, can we achieve the equivalent of WITH (NOLOCK) when using LINQ to SQL? The answer lies in transactions.

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.

That's pretty handy to know! Of course, I'm still on the cusp of moving to a stored-procedure-based system where I can tailor the queries myself. Anyone got any opinions on that front?