Transaction in LINQ

Published on Saturday, October 22, 2011

If you use LINQ, and consider Transaction, you might choose either explicit transaction or no. Because, due to MSDN, LINQ to SQL supports 3 distinct transaction models:

1: Explicit Local Transaction
2: Explicit Distributable Transaction
3: Implicit Transaction.

About Implicit transaction, because when you call SubmitChanges, LINQ to SQL will automatically starts a local transaction for you.

About more detail, please read MSDN.

The following is a sample that using Explicit Transaction:

           Northwnd db = new Northwnd(@"c:\northwnd.mdf");
            using (TransactionScope ts = new TransactionScope())
            {
                try
                {
                    Product prod1 = db.Products.First(p => p.ProductID == 4);
                    Product prod2 = db.Products.First(p => p.ProductID == 5);
                    prod1.UnitsInStock -= 3;
                    prod2.UnitsInStock -= 5;
                    db.SubmitChanges();
		    ts.Complete();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.Message);
                }
            }

If you use Visual Studio 2010, you might find “new TransactionScope()” got compile error, like the following:

linqTransError

The reason is that you have to add Transaction reference, Visual Studio does not include Transaction reference by default:

linqTransError1