After some investigation I feel I have a decent understanding on how LINQ to SQL concurrency works.

LINQ to SQL uses optimistic concurrency which means that it never takes an exclusive lock on a table.  It caches the information you are working with and then (by default) when you submit changes verifies that the table is in the same state as when you started.  If it isn’t an exception is thrown allowing you to merge values if needed.

Now for more details :)

Let’s say I have two tables:

Albums

• ID (primary key)
• Name
• Year

Songs

• ID (primary key)
• AlbumID (foreign key)
• Name
• Lyrics

LINQ to SQL will turn these tables into two classes that looks vaguely similar but much more complex than this:

class Albums {
    [Column(UpdateCheck=UpdateCheck.Always)]
    public int ID { get; set; }

    [Column(UpdateCheck=UpdateCheck.Always)]
    public string Name  { get; set; }

    [Column(UpdateCheck=UpdateCheck.Always)]
    public int Year { get; set; }
}

class Songs {
    [Column(UpdateCheck=UpdateCheck.Always)]
    public int ID { get; set; }

    [Column(UpdateCheck=UpdateCheck.Always)]
    public string Name  { get; set; }

    [Column(UpdateCheck=UpdateCheck.Always)]
    public string Lyrics { get; set; }

    [Column(UpdateCheck=UpdateCheck.Always)]
    public int AlbumID { get; set; }
}

I oversimplified many of the details and changed some to help describe how the concurrency works.

You may notice that each property in the class maps to a column in the table.  In addition, each property has an attribute on it which gives extra details about it.

The Column attribute means the property maps to a column and inside that attribute there is a UpdateCheck property.  This determines what role this column plays in the optimistic concurrency checking.  The UpdateCheck enum has three possible values:

UpdateCheck.Always – This column is always checked for concurrency

UpdateCheck.Never – This column is never checked for concurrency

UpdateCheck.WhenChanged – This column is only checked for concurrency when you change the corresponding property’s value in your local copy.

When I first read this it meant little to me so I dug deeper to see exactly what SQL queries LINQ to SQL would create.

Assume that (as shown above) I have all columns set with UpdateCheck.Always (which is the default behavior) . Let’s say in the Albums table I have a row with the values: ID = 1, Name = “DanceMix”, Year = 2007.  And we want to update the name of this album using LINQ to SQL :

// connect to db using linqtosql datacontext
DataContext db = new DataContext(CONNECTION_STRING);

// select the album with an ID of 1, this will cache this album
var someAlbum = db.Albums.Single(album => album.ID == 1);

// make the new name "BestDance"
someAlbum.Name = "BestDance";

db.SubmitChanges();

The update SQL call generated behind the scenes will look something like this:

UPDATE Albums
SET Name = 'BestDance'
WHERE
ID = ‘'1' AND Name = 'DanceMix' AND Year=2007

As you can see, the SQL query is checking to make sure ALL fields in the Album table are the same as they were when we queried for it.  All the fields are included since we set the UpdateCheck property for each field to UpdateCheck.Always.  If we had set all of them to UpdateCheck.Never the same query would become:

UPDATE Albums
SET Name = 'BestDance'
WHERE
ID = '1'

Notice now that Name and Year columns are excluding from the where clause since we are telling LINQ to SQL to not check them for concurrency.

If we had UpdateCheck on each column set to UpdateCheck.WhenChanged the generated query would be:

UPDATE Albums
SET Name = 'BestDance'
WHERE
ID = '1' AND Name = 'DanceMix'

Since we modified Name locally it is included in the concurrency check because that is what UpdateCheck. WhenChanged means.

When we do have a conflict the update will fail and then an exception is thrown.  We can catch that exception and LINQ to SQL will give us a list of all items in conflict.  From here we can tell LINQ what do to.  Such as overwrite database values, overwrite local values or merge values.

For some more info on the concurrency please visit: http://msdn.microsoft.com/en-us/library/bb399373.aspx it isn’t very long.

For more information on how to merge if we have a concurrency exception go here: http://msdn.microsoft.com/en-us/library/bb298529.aspx

For a very long but more detailed look at LINQ to SQL in general go here: http://msdn.microsoft.com/en-us/library/bb425822.aspx