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...

Looks like I get my wish!

UPDATE: Hmph- now it's been marked Postponed. D'oh! I guess I'll just have to live with separate contracts and string coercion for the AJAX GET requests to deal with null values.

Cool- according to the comments on my connect requests (here and here), nullable support in WebGet/WebInvoke contracts is on DevDiv's list of "things we'd like to fix before RTM" (of Orcas). Being able to remove all those stupid conditional string->enum and string->int casts from my contracts will make all the time spent filing bugs and suggestions worth it!

Null semantics in LINQ to SQL

The fun with Orcas Beta2 continues! Other than some incompatibility problems with ComponentArt's AJAX controls (which we're expecting a true fix for any day now), all the big showstoppers have been worked around thus far.

Hit a new snag today- not a showstopper, but a change from B1 behavior for sure. Awhile back, Dinesh was talking about the null impedance mismatch between SQL and C# and how the LINQ to SQL team was proposing handling it. Basically, C# null semantics ruled, so LINQ to SQL was consistent with all the other LINQ types (in other words, null == null). That proposal stuck until Beta2, where it appears they've flip-flopped back to SQL null semantics (null != null, or anything else for that matter). I've started an MSDN forum discussion on it as well.

I can definitely understand both sides of this argument. That said, the change makes life a little harder. The following worked in Beta1 (when the "filter" arg was null, rows where "Name" is null came back):

public IList GetFoos(string filter)
{
var query = from j in jdc.ATable where j.Name == filter select j;

return query.ToList();
}

This query doesn't work in Beta2- since SQL null doesn't equal anything (including null), I get no rows back. The following hack restores the correct behavior in Beta2:

var query = from j in jdc.ATables where filter == null ? j.Name == null : j.Name == filter select j;

This works, but is a little verbose on the C# side (especially when I have lots of potentially null filters on a query), and generates SQL that is also verbose. Instead of generating an IS NULL statement when filter is null, it generates an inline CASE statement in the query. Works, but probably not ideal.

What I'd really like to do is wrap the comparison in a little expression generator that would result in an IS NULL getting generated on the SQL side. Something like:

var query = from j in jdc.ATable where CheckNullCompare(j.Name == filter) select j;

I might try to get to this over the weekend- I'm still not terribly clear on how I'd mix this, since it is sort of a mix of runtime values and expressions. More to come...