LINQ to SQL NOLOCK'ing

Scott Hanselman's ComputerZen.com - 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.

..."

I use the NOLOCK hint in my SQL 2000 world a bit so this tip may come in handy (I'm trying to get us to SQL 2005 but... well... yeah...). Yeah, yeah, I know it's "bad" but sometimes you just got to do what you've got to do...

Now that said, I'm still not going to "endorse" LINQ to SQL for production usage yet at work, UNLESS our existing stored procedures (or new SP's) are used. And you know what? SP's work nicely with LINQ to SQL. I was able to drop some tables into the design surface, drag over some SP's, set some properties and I was LINQ'ing quite well. Select, inserts and updates working easily all the time through our SP's. There were quite a few "oh that's just cool" moments.

Why SP's? Because they have saved my bacon a number of times over the years (I've been coding against SQL Server since 4.21a...). Practical real world experience has re-enforced my belief in SP's as my primary DB level API. Not a religious thing, just an experience thing...