Friday, August 17, 2007

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

3 comments:

Chris said...

Thanks for this. I adopted it and tweaked it slightly, to reuse the connection the table is using (instead of creating a new one).


public static TEntity InsertIfNotExists<TEntity>
(
    DataContext db,
    Table<TEntity> table,
    Func<TEntity,bool> where,
    TEntity record
)
    where TEntity : class
{
    TEntity existing = table.SingleOrDefault<TEntity>(where);

    if (existing != null)
    {
        return existing;
    }
    else
    {
        table.InsertOnSubmit(record);
        db.SubmitChanges();
    }

    return record;
}

Ryan & Teena said...

Thank you for this. I've happly incorperated it into a Unit Of Work approach pattern.

Ryan Mrachek

Terrance Smith said...

try using gist from github for posting code. Also is SingleOrDefault better or worse than Any? Or is this more of a personal pref thing?