Thursday, April 14, 2011

How would you write an Upsert for LINQ to SQL?

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.

From stackoverflow
  • 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.

0 comments:

Post a Comment