LINQ to SQL insert-if-non-existent

Question

I'd like to know if there's an easier way to insert a record if it doesn't already exist in a table. I'm still trying to build my LINQ to SQL skills.

Here's what I've got, but it seems like there should be an easier way.

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);

        // Can't use table.Context.SubmitChanges()
        // 'cause it's read-only

        db.SubmitChanges();
    }

    return record;
}

Answer 1

public static void InsertIfNotExists<TEntity>
                    (this Table<TEntity> table,
                     TEntity entity,
                     Expression<Func<TEntity,bool>> predicate)
    where TEntity : class
{ 
    if (!table.Any(predicate)) 
    {
        table.InsertOnSubmit(record);
        table.Context.SubmitChanges();
    }
 }


table.InsertIfNotExists(entity, e=>e.BooleanProperty);

Answer 2

As others have pointed out, the if (!Any()) { InsertOnSubmit(); } solutions all have a race condition. If you go that route, when you call SubmitChanges, you have to take into account that either a) a SqlException could be raised for a duplicate insert, or b) you could have duplicate records in the table.

Fortunately, we can use the database to avoid the race condition by enforcing uniqueness. The following code assumes that there is a primary key or unique constraint on the table to prevent the insertion of duplicate records.

using (var db = new DataContext()) {

    // Add the new (possibly duplicate) record to the data context here.

    try {
        db.SubmitChanges();
    } catch (SqlException ex) {
        const int violationOfPrimaryKeyContraint = 2627;
        const int violationOfUniqueConstraint = 2601;
        var duplicateRecordExceptionNumbers = new [] {
            violationOfPrimaryKeyContraint, violationOfUniqueConstraint
        };
        if (!duplicateRecordExceptionNumbers.Contains(ex.Number)) {
            throw;
        }
    }
}

Now... things get a fair bit more complicated if you have to perform the insert in a batch transaction with other database updates.