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

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

Answer 3

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

        }
    }