Transaction in LINQ

2011-10-22


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