LINQ: DataContexts and You

For the past several weeks, I've been working extensively with LINQ for the first time. One perpetual source of headaches was the use of DataContexts with LINQ to SQL -- specifically, using too many of them. The original code we inherited had, like most LINQ examples, code such as this:

using (var db = new YourDataContext())
{
 var syncRow = (from date in db.Syncs
       where date.Table == "YourTable"
       select date).SingleOrDefault();

 if (syncRow == null)
 {
  // make a new one (*snipped*)
  db.Syncs.InsertOnSubmit();
 }
 else
 {
  // update existing row
  syncRow.Synced = currentSyncDate;
 }

 db.SubmitChanges();
}

This creates a DataContext that exists only within the “using” block, retrieves a row, creates a new one or updates an existing one as appropriate, then submits the changes. There’s nothing wrong with this code from a low level, it all works and the simplicity and automatic disposal provided by the “using” block is actually quite convenient.

There main problem is Data concurrency. When you submit to one DataContext, it does write to the database as you would expect. What it doesn’t do is refresh other DataContext objects you may have open elsewhere in the codebase, particularly in another class or function. In our application, this came up when every function had wrapped the activities in this using wrapper and as a result, we’d have stale data residing in the calling function since its DataContext was stale. Sometimes this would be obvious (for example, a returned object from a previous query was not updated), but other times it wasn’t obvious due to the caching LINQ does behind the scenes. Even if you update and submit changes in one DataContext, this will not necessarily become available in a new query in a pre-existing DataContext.

There is a hack here that could be used, but should not be used. Sometimes this is unavoidable. The hack is to force a refresh on the DataContext objects when you expect for a previous function to write to the database but cannot share a context. In our application, our unit tests make use of this hack after populating the database with dummy data:

db.Refresh(System.Data.Linq.RefreshMode.OverwriteCurrentValues, yourLinqObject); 

The best overall solution to this problem is to simply share DataContexts as much as possible. In our application, we’ve both used class-level (rather than function-level) contexts where appropriate and for other cases where different classes must share the same DataContexts, we simply pass them as parameters from function to function. This eliminates the performance hit as well of constantly creating/destroying database connections, which may not be syntactically obvious from the innocuous “using” statement.

Another big win for having a single, class-global DataContext is how trivial it is to wrap it in a transaction. Just wrap all of the code to fit in the transaction in a using block with a TransactionScope object:

using (TransactionScope ts = new TransactionScope())
{
 try
 {
  // get stuff
  List messages = OtherObject.GetMessages(yourDb);

  // loop through messages
  foreach (YourObject currentObj in messages)
  {
   // update messages
   OtherProvider.UpdateMessages(currentObj);
  }

  // complete the transaction
  ts.Complete();
 }
 catch (Exception ex)
 {
  // insert your favourite exception handler
 }
}
yourDb.Dispose();  

Obligatory first vacuous post

I've noticed over the past several years how valuable the blogosphere can be when troubleshooting issues with software development. It's also been a tremendous resource in expanding my knowledge about various technologies and development methodologies.

While I don't plan to regularly update this blog, when I discover a new "gotcha", issue, idiom, or any other nugget of information that others could benefit from, I'll make an effort to post it here.