Click here to Skip to main content
12,298,379 members (52,861 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

16.2K views
2 bookmarked
Posted

LINQ to SQL with NOLOCK

, 21 Jul 2011 CPOL
Rate this:
Please Sign up or sign in to vote.
LINQ does not understand query hints: a workaround.

If your project uses LINQ to SQL for your database access, you may want to use NOLOCK to set the isolation level to ReadUncommitted on your Select statements. The problem is that LINQ does not understand query hints. The way this works with LINQ is by running your Select statements under the context of a transaction scope, as follows:

string type = string.Empty;

using (DataContext ctx = new DataContext(ConnectionString))
{
  using (var tx = new System.Transactions.TransactionScope(TransactionScopeOption.Required,
         new TransactionOptions() { IsolationLevel = IsolationLevel.ReadUncommitted }))
  {
     var row = (from record in ctx.Log
                where record.Id == 99999
                select record).FirstOrDefault();
     type = row.Type;
  }
}

The code here creates a transaction scope and sets the isolation level to ReadUncommitted (NOLOCK). The Select statement runs under the scope of the transaction, and it returns the value from a query. The using clause ensures that both the transactions as well as the data context are disposed once the code goes out of scope.

There is also the preferable option to use Stored Procedures to eliminate the need to use transaction scope. In the Stored Procedure, you have the flexibility to use query hints.

I hope this helps.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

ozkar garcia
Architect OG-BITechnologies
United States United States

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 4 Pin
Hal Diggs24-Nov-15 10:19
professionalHal Diggs24-Nov-15 10:19 
Questionmssql2005 > Pin
10der25-Jul-11 4:06
member10der25-Jul-11 4:06 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.160525.2 | Last Updated 21 Jul 2011
Article Copyright 2011 by ozkar garcia
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid