Bill Blogs in C#

Mike Champion is one of the PMs on the LINQ to XML team in Microsoft.

He wrote the report referenced below on XML 2006 (he was the track chair for the XML computing sessions). The most interesting portion of his review is the compare and contrast of Linq to XML and XJ.

Local note:  Mike Champion was a long-time member of the Ann Arbor Software community. I've had the pleasure of working with him on a number of projects in the past.

Well, it’s been quite a while since I examined LINQ to SQL.  This installment will show you what LINQ to SQL does when you have some form of errors in the database.  Those errors could be caused by multiple users making modifications, or by a change that violates one of the database constraints. 

The first example shows a path where you read and write the data after another user has modified it:

public void DLinq71() {
    Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");
    // Open a second connection to the database to simulate another user
    // who is going to make changes to the Products table                
    Northwind otherUser_db = new Northwind(connString) { Log = db.Log };

    var otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1);
    otherUser_product.UnitPrice = 999.99M;
    otherUser_db.SubmitChanges();

    Console.WriteLine("YOU:  ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");
    var product = db.Products.First(p => p.ProductID == 1);
    product.UnitPrice = 777.77M;

    bool conflict = false;
    try {
        db.SubmitChanges();
    }
    catch (OptimisticConcurrencyException) {
        conflict = true;
    }
}

In this example, no error occurs because, well, there’s no concurrency violation.

Contrast that with this code:

public void DLinq72() {
    Console.WriteLine("YOU:  ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");
    var product = db.Products.First(p => p.ProductID == 1);

    Console.WriteLine("OTHER USER: ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");
    // Open a second connection to the database to simulate another user
    // who is going to make changes to the Products table                
    Northwind otherUser_db = new Northwind(connString) { Log = db.Log };

    var otherUser_product = otherUser_db.Products.First(p => p.ProductID == 1);
    otherUser_product.UnitPrice = 999.99M;
    otherUser_db.SubmitChanges();

    Console.WriteLine("YOU (continued): ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~");
    product.UnitPrice = 777.77M;
    bool conflict = false;
    try {
        db.SubmitChanges();
    }
    catch (OptimisticConcurrencyException) {
        conflict = true;
    }
}

Well, this time, you’d get an OptimisticConcurrency exception because the other user has modified the data after you read the values, but before you wrote them back. LINQ 2 SQL watches for concurrency conflicts based on when you loaded data using a query, and when you submit those changes back to the database. 
Note that this is a sample. It’s trivial to avoid concurrency violations in a single procedure: don’t open two different connections.  In practice, you’ll run into this situation when you have multiple applications, or multiple users accessing the same database.

Next, let’s look at what happens if you violate one of the database constraints. 

This method creates removes units in stock from a given product, until there are a negative number of items in stock.  That’s a violation of one of the constraints for that table. The method doesn’t do any special error handling (except catch the SQLClientException and print a message).  But still, the database changes are completely rolled back.

public void DLinq73() {

    Console.WriteLine("*** UPDATE WITH IMPLICIT TRANSACTION ***");
    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;    // ERROR: this will make the units in stock negative
        // db.SubmitChanges implicitly uses a transaction so that
        // either both updates are accepted or both are rejected
        db.SubmitChanges();
    }
    catch (Exception e) {
        Console.WriteLine(e.Message);
    }
}

The SQLClientException message will say what went wrong, but all you know is that your transaction failed and was rolled back.
If you want more control, you can use an explicit transaction, which ensures that the database state is consistent until the transaction has completed.

public void DLinq74() {
    // Explicit use of TransactionScope ensures that
    // the data will not change in the database between
    // read and write
    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;    // ERROR: this will make the units in stock negative
            db.SubmitChanges();
        }
        catch (Exception e) {
            Console.WriteLine(e.Message);
        }
    }
}

Essentially, the behavior is the same (in this small example), but creating your own explicit transactions means that you control the scope of the changes to the database.
Next, we’ll look at how the LINQ to SQL libraries handle nullables, and null values in the database tables.