Getting LINQ to SQL and LINQ to Entities to use NOLOCK
I was visiting a local company where a bunch of friends work and they asked how to get LINQ to SQL to use NOLOCK. They were explicitly asking how to get the SQL generated by LINQ to SQL to add the NOLOCK hints to the end.
However, with NOLOCK (even though "everyone" has used it at some point or another) is generally considered a last resort. Queries that use NOLOCK aren't guaranteed to return correct results or technically, return any results at all.
SQL 2005 has snapshot-based isolation levels that prevent readers from blocking writers or writers from blocking readers without allowing dirty reads.
Now, I have said that NOLOCK has served me personally very well in the past on systems of some size, but I hear what folks who say no to NOLOCK are saying. It certainly depends on one's definition of "correct results." ;)
There's three ways to get the behavior your want. Using TransactionScope is the recommended way to affect the transaction options associated with the commands generated by either LINQ to SQL or LINQ to Entities.
LINQ to SQL also supports explicitly setting the transaction on the context, so you could get the connection from the context, open it, start a transaction, and set it on the context. This can be desirable if you think SQL 2005 is promoting transactions too often, but the preferred method is TransactionScope.
ProductsNewViewData viewData = new ProductsNewViewData();
using (var t = new TransactionScope(TransactionScopeOption.Required,
new TransactionOptions {
IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted
}))
{
viewData.Suppliers = northwind.Suppliers.ToList();
viewData.Categories = northwind.Categories.ToList();
}
Here's an example where I used it in some recent code. This TransactionScope could be hidden (layered away) in your DAL (Data Access Layer) or in your Data Context directly if you wanted it to be neater.
A second way is that you can still create and call Stored Procedures (sprocs) from LINQ to SQL and those sprocs could include NOLOCK, TransactionScope is a better choice for LINQ to SQL or LINQ to Entity generated SQL if you feel that your query doesn't need to lock down the table(s) it's reading from.
Note that you'll want to be aware of which statement in your LINQ to SQL actually starts talking to the database. You can setup a query ahead of time and it won't be executed, for example, until someone calls ToList() or the like. It's at this point you'll want to wrap it in the using(TransactionScope){}.
Another third way you could set it at a DataContext level (which, to be clear, would affect every generated LINQ to SQL query executed on that context) would be to execute the command:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
...using the connection available inside your DataContext.
Thanks to David Browne and Michael Pizzo for their help on this!
About Scott
Scott Hanselman is a former professor, former Chief Architect in finance, now speaker, consultant, father, diabetic, and Microsoft employee. He is a failed stand-up comic, a cornrower, and a book author.
About Newsletter
Any problem in computer science can be solved with one additional layer of indirection. But that usually will create another problem. - David Wheeler
Download