IF NOT EXISTS in LINQ to SQL

SQL Server is a bit limited compared to other DB platforms where common DB idioms like "Insert if not exists" and "Insert or update" are concerned. You can do it, but you have to jump through some hoops. LINQ surfaces many of the shortcomings of the underlying SQL platform... However, with all the goodies in C# 3.0, you can roll your own special purpose extension methods to paper over some of these shortcomings.

A recent forum discussion on MSDN was rolling about different ways to achieve the "Insert if not exists" behavior. I came up with a little extension method to do it. Granted, this isn't atomic- I'll probably bake in some behavior later to make it catch a duplicate key violation and refetch (eg, another process inserted the value before you), but it serves my needs well at the moment.

(sorry- code doesn't post so well on Blogger)

public static class FetchOrCreateExtension
{
   public static T FetchOrCreate<T>(this Table<T> table, Expression<func<T,>> where, T newValue) where T:class
   {
      T existing = table.SingleOrDefault(where);
      if (existing != null)
         return existing;
      // clone the DataContext
      Type dataContextType = table.Context.GetType();
      string ctxConStr = table.Context.Connection.ConnectionString;
      using (DataContext newDC = (DataContext)Activator.CreateInstance(dataContextType, ctxConStr))
      {
         Table<T> writableTable = newDC.GetTable<T>();
         writableTable.Add(newValue);
         newDC.SubmitChanges();
      }
      return table.Single(where); // fetch on the existing context so the caching behavior is consistent
   }
}

Make use of it by "using" the namespace you put it in- then it shows up on all your table objects. To fetch or create a Foo (matching a particular predicate), do:

FooTable x = dataCtx.FooTable.FetchOrCreate(f => f.Name == "SomeName", new Foo { Name = "SomeName", OtherData="OtherCreateData" });

I'm not trying to do any key inference or anything here- that's up to you. The newly-created object had better match the predicate you passed in (and nothing else) or you'll have a problem. Anyway, this thing does what I need- hopefully it can help someone else...