How would you write an Upsert for LINQ to SQL?

Question

So I'd like to write a generic Upsert function for LINQ to SQL and I'm having some trouble conceptualizing how to do it. I'd like it to work something like this:

var db = new DataContext();
db.Customers.UpsertOnSubmit(customer);

So it would have to be generic in some way and I guess and extension method on Table. I've been able to get this far in determining the primary key of the underlying table:

var context = source.Context;
var table = context.Mapping.GetTable(source.GetType());
var primaryMember = table.RowType.DataMembers.SingleOrDefault(m => m.IsPrimaryKey);

I'm assuming it will be necessary to have this to compose a query to tell if the item is in the DB already or not but I don't really know what to do with it at this point.

score 8 · Accepted Answer · edited Sep 11 '09 at 19:22

I do something similar, but with a different approach. Every entity implements IEntity. One of the properties of IEntity is a state if the object is new or existing. I then implement that for each entity, like:

public EntityState EntityState
{
    get
    {
        if (_Id > 0)
            return EntityState.Exisiting;
        else
            return EntityState.New;
    }
}

Then, a generic Upsert could be (on a generic repository type class):

public virtual void Upsert<Ta>(Ta entity)
    where Ta: class
{
    if (!(entity is IEntity))
        throw new Exception("T must be of type IEntity");

    if (((IEntity)entity).EntityState == EntityState.Exisiting)
        GetTable<Ta>().Attach(entity, true);
    else
        GetTable<Ta>().InsertOnSubmit(entity);
}

private System.Data.Linq.Table<Ta> GetTable<Ta>()
    where Ta: class
{
    return _dataContext.Context.GetTable<Ta>();
}

If your attaching from another datacontext, also make sure you have a timestamp on your objects.

Well... the whole point of an upsert is that you don't know if it exists or not, you just want the database to have the new content; the upsert saves you the 'extra roundtrip' to determine that. Your solution doesn't answer that; what's needed is a sql `MERGE` as described here: http://stackoverflow.com/questions/2479488/syntax-for-single-row-merge-upsert-in-sql-server . — atlaste, Jul 03 '15 at 09:39
This answer applies to Entity Framework. See my answer for a LINQ-to-SQL solution. — orad, Nov 23 '16 at 01:16

orad · Answer 2 · 2016-11-23T01:53:22.283

Short Answer:

Grab this: EntityExtensionMethods.cs

Explanation

To do UPSERT in LINQ-to-SQL without querying the records first, you can do the following. It will still hit the db once to check if record exists but will not pull the record:

var blob = new Blob { Id = "some id", Value = "some value" }; // Id is primary key (PK)

if (dbContext.Blobs.Contains(blob)) // if blob exists by PK then update
{
    // This will update all columns that are not set in 'original' object. For
    // this to work, Blob has to have UpdateCheck=Never for all properties except
    // for primary keys. This will update the record without querying it first.
    dbContext.Blobs.Attach(blob, original: new Blob { Id = blob.Id });
}
else // insert
{
    dbContext.Blobs.InsertOnSubmit(blob);
}
dbContext.Blobs.SubmitChanges();

Extension Method

I came up with the following extension method for it.

public static class EntityExtensionMethods
{
    public static void InsertOrUpdateOnSubmit<TEntity>(this Table<TEntity> table, TEntity entity, TEntity original = null)
        where TEntity : class, new()
    {
        if (table.Contains(entity)) // if entity exists by PK then update
        {
            if (original == null)
            {
                // Create original object with only primary keys set
                original = new TEntity();
                var entityType = typeof(TEntity);
                var dataMembers = table.Context.Mapping.GetMetaType(entityType).DataMembers;
                foreach (var member in dataMembers.Where(m => m.IsPrimaryKey))
                {
                    var propValue = entityType.GetProperty(member.Name).GetValue(entity, null);
                    entityType.InvokeMember(member.Name, BindingFlags.SetProperty, Type.DefaultBinder,
                        original, new[] {propValue});
                }
            }

            // This will update all columns that are not set in 'original' object. For
            // this to work, entity has to have UpdateCheck=Never for all properties except
            // for primary keys. This will update the record without querying it first.
            table.Attach(entity, original);
        }
        else // insert
        {
            table.InsertOnSubmit(entity);
        }
    }
}

Use it like below:

var blob = new Blob { Id = "some id", Value = "some value" }; // Id is primary key (PK)
dbContext.Blobs.InsertOrUpdateOnSubmit(blob);
dbContext.Blobs.SubmitChanges();

I added above extension method with more stuff to this gist: EntityExtensionMethods.cs

How would you write an Upsert for LINQ to SQL?

2 Answers2

Linked