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

• c#
• linq-to-sql

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

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.

Agree with marxidad's answer, but see note 1.

Note 1: IMHO, it is not wise to call db.SubmitChanges() in a helper method, because you may break the context transaction. This means that if you call the InsertIfNotExists<TEntity> in the middle of a complex update of several entities you are saving the changes not at once but in steps.

Note 2: The InsertIfNotExists<TEntity> method is a very generic method that works for any scenario. If you want to just discriminate the entities that have loaded from the database from the entities that have been created from the code, you can utilize the partial method OnLoaded of the Entity class like this:

public partial class MyEntity
{
    public bool IsLoaded { get; private set; }
    partial void OnLoaded()
    {
        IsLoaded = true;
    }
}

Given that (and note 1), then InsertIfNotExists functionality is reduced to the following:

if (!record.IsLoaded)
    db.InsertOnSubmit(record);

Small modification for Mark's answer:

If you only care about checking if the entity exists by its primary key, Marke's answer can be used like this:

public static void InsertIfNotExists<TEntity>
                    (this Table<TEntity> table
                     , TEntity entity
                    ) where TEntity : class
    {
        if (!table.Contains(entity))
        {
            table.InsertOnSubmit(entity);

        }
    }