How to use SQL Server table hints when using LINQ?

Question

How can I use Sql Server table hints, for example, "NOLOCK" when using LINQ?

For example, I can write "SELECT * from employee (NOLOCK)" in SQL.

How can we write the same using LINQ?

Answer 1

Here you can apply NOLOCK: http://www.hanselman.com/blog/GettingLINQToSQLAndLINQToEntitiesToUseNOLOCK.aspx

(Quote for posterity, all rights reserved mr scott):

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(); } 

Answer 2

I highly recommend reading about SQL Server transaction isolation modes before using ReadUncommitted. Here is a very good read.

http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

In many cases, the ReadSnapshot level should be sufficient. Also you, if you really need it. You can set the default transaction isolation level for your database using

 Set Transaction Isolation Level --levelHere 

Other good ideas include packaging. Your context is in a shell that encapsulates each call using the required isolation level. (maybe you need nolock 95% of the time and serializable 5% of the time). This can be done using extension methods or regular methods using code like:

 viewData.Categories = northwind.Categories.AsReadCommited().ToList(); 

Which takes your IQueryable and does the trick mentioned by Rob.

Hope this helps Luke