How to use WITH(NOLOCK) in LINQ to SQL?

Question

we can use SQL just like this:

SELECT * FROM student WITH(NOLOCK);

How can I achieve this with LINQ to SQL without the use of a TransactionScope?

Answer 1

LINQ to SQL does not have any mechanism of doing this, but you can create a transaction with a specific isolation level. Look at the code below:

using (var con = new SqlConnection("constr"))
{
    con.Open();

    using (var transaction = con.BeginTransaction(
        IsolationLevel.ReadUncommitted))
    {
        using (var context = new SchoolDataContext(con))
        {
            // HACK: Setting the context.Transaction is 
            // needed in .NET 3.5 (fixed in .NET 4.0).
            context.Transaction = transaction;
            var q = from s in context.Students select c;
        }
    }
}

Sometimes using this type of isolation is useful, i.e. for performance reasons. But please make sure you don't do any create, update or delete (CUD) operations using this type of database isolation. It of course depends on your situations, but your data could get in an inconsistent state.