Transactional Linq2SQL

September 17, 2011

I’ve now built 2 systems that use LINQ2SQL as the RDML. I’ve come to love LINQ and the power/speed it brings. To date, I have never had the need to be concerned with transactions on either of the two systems – Either the business logic did not require it, or it has been handled via my exception handling framework, so have never really needed to know how transactions are handled when using LINQ2SQL.

I decided recently that I probably should know – As with most jobs/vocations, more knowledge equals better results, so spent a couple of lunch breaks investigating transactio management in LINQ2SQL.

It seems the best way (read – method that suits most common requirements) is to wrap the operations in a transaction scope:

protected void ButtonGo_Click(object sender, EventArgs e)
	using (System.Transactions.TransactionScope transactionScope = new System.Transactions.TransactionScope())
		Random random = new Random();
        int randomNumber = random.Next(1,1000);

        UpdateName(1, "Apple " + randomNumber.ToString());
        UpdateName(2, "Orange Renamed " + randomNumber.ToString());

        throw new ApplicationException("Error Occurred");


private void UpdateName(int FruitID, String NewName)
	using (DataClassesDataContext db = new DataClassesDataContext())
	    Fruit fruitToRename = (from f in db.Fruits where f.ID == FruitID select f).FirstOrDefault();

            if (fruitToRename != null)
	        fruitToRename.Name = NewName;

Note that in the code above, I am making calls to a method (UpdateName) within the scope of the transaction. It is only when .Complete() is called that the changes are committed to the database. If UpdateName was called outside of the scope of an explicit transaction, LINQ2SQL would detect that there is no transaction on the thread, and generate its own implicit transaction.

Its also worth noting that I have purposely thrown an exception in the first block – This was to prevent transactionScope.Complete() from being called – In which case the changes are not committed to the database.

I  also tried the above code with a transaction scope wrapped around the changes made within ‘UpdateName’ to see what transaction took precedence – As expected, the higher/earlier transaction had control, meaning no updates would be made to the database if any of the attempts caused an exception.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: