CODEWITHSUNDEEP
.netsqllinq-to-sqltransactionsread-uncommitted
ligaoren
ligaoren

Reputation: 1073

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

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?

Upvotes: 4

Views: 9843

Answers (1)

Steven
Steven

Reputation: 172676

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.

Upvotes: 11

Related Questions