4

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?

Steven
  • 166,672
  • 24
  • 332
  • 435
ligaoren
  • 1,043
  • 1
  • 9
  • 10
  • why do you want to use NOLOCK ? – Mitch Wheat Feb 08 '10 at 10:11
  • Ligaoren, was my answer useful to you? – Steven Feb 08 '10 at 21:44
  • Why don't you want to use TransactionScope? – KristoferA Feb 09 '10 at 02:02
  • I can imagine why he doesn't want to. You don't always want everything in one single transaction. Take logging information for instance, you never want that rolled back. You have to take special care to ensure those are not rolled back. However, I love the TransactionScope for my integration tests. – Steven Feb 09 '10 at 07:43

1 Answers1

11

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.

Steven
  • 166,672
  • 24
  • 332
  • 435
  • forgive my late response, it's Chinese Spring Festival recently. The reason is partly historical:the member of my team always write nolock statement in simple three-layer architecture. they don't want to lose the control of DataBase. they even want to use nolock in Nhibernate , i give up Nhibernate just for this.They don't fully understand ORM's job. Now,my question become how to persuade them use ORM without considering details about Data Access???? – ligaoren Feb 21 '10 at 03:41
  • P.S Steven's answer is brilliant – ligaoren Feb 21 '10 at 03:42