In this post I would like to talk about LINQ to SQL and transactions.When I have a LINQ to SQL class I always get asked this question, "How does LINQ treat Transactions?".

When we use the DeleteOnSubmit() method or the InsertOnSubmit() method, all of those commands at some point are translated into T-SQL commands and then are executed against the database.

All of those commands live in transactions and they follow the basic rules of transaction processing. They do succeed together or fail together.

I am going to demonstrate this with a hands on example.

I assume that you have access to a version of SQL Server and Northwind database.

If you do not, you can download and install the free SQL Server Express edition from here. If you need the installation scripts for the sample Northwind database, click here

1) Launch Visual Studio 2010/2008 (express editions will work fine). Create a new empty website and choose a suitable name for it. Choose C# as the development language.

2) Add a new item in your site. Add a LINQ to SQL Classes and name it Northwind.dbml.Place this file in the App_Code special folder.

3) From the Server Explorer / Data Explorer window drag the Region table and drop it on the Northwind.dbml.

4) Have a look at the generated code from the Linq engine in Northwind.designer.cs

5) We will try to update and insert some values using LINQ to SQL. We will make our application to fail in purpose so we see that all the transactions are rolled back.

6) Do a "Select * from Region"  and have a good look at the data.

6) In the Page_Load() event handling routine of the Default.aspx page type

 NorthwindDataContext ctx = new NorthwindDataContext();

      
        var myregion = (from reg in ctx.Regions
                        where reg.RegionID==1
                        select reg).Single();

        myregion.RegionDescription="Eastern Country";

  

        Region mynewregion = new Region();


        mynewregion.RegionID = 2;
        mynewregion.RegionDescription = "Midlands";   

        ctx.Regions.InsertOnSubmit(mynewregion);
  

        try
        {
            ctx.SubmitChanges();
            Response.Write("Saved in the database");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

 

7) We have an update operation that should succeed.We update the first record from Eastern to Eastern Country. Then we do insert a value in the RegionID field that we know will cause a primary key violation.

8) Launch Profiler and start a new Trace. Make sure that you include the events Begin Tran Completed, Commit Tran Completed, Rollback Tran completed in that trace.

9) Run your application and you will receive the following error - "Violation of PRIMARY KEY constraint 'PK_Region'. Cannot insert duplicate key in object 'dbo.Region'. The statement has been terminated."

10) Have a look at the Profiler Trace. It should look like my picture below

It is profound that the Transaction has failed. The Update statement was valid but the Insert statement was not.That is why both operations failed.

11)  Let's fix that.Comment out everything inside the Page_Load() event handling routine of the Default.aspx page and then type

NorthwindDataContext ctx = new NorthwindDataContext();

      
        var myregion = (from reg in ctx.Regions
                        where reg.RegionID==1
                        select reg).Single();

        myregion.RegionDescription="Eastern Country";

        Region mynewregion = new Region();

        mynewregion.RegionID = 5;
        mynewregion.RegionDescription = "Midlands";   

        ctx.Regions.InsertOnSubmit(mynewregion);
  

        try
        {
            ctx.SubmitChanges();
            Response.Write("Saved in the database");
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }

This is the same code as before , I just changed this line of code

 mynewregion.RegionID = 2;  wih this one mynewregion.RegionID = 5;

12) Run your application and also make a note of the Trace in the Profiler. The output you will see in the screen will be this one

Saved in the database 

13) Your Trace results should look like this

Now the transaction commits.  Do a "Select * from Region" again and have a good look at the new/updated data.

I always say to developers who build their data access layer or LINQ to SQL, that Profiler is their best friend.

Email me if you need the source code.

Hope it helps.