How to populate a table by Insert or Update

Question

I access DB via BLToolkit. One of my tables has:

• fields: Id, TeamId, PlayerId, Val1, Val2
• primary key - field Id, autoincremented
• composite unique key which consists from 2 fields TeamId and PlayerId
• data fields Val1, Val2

I've created list of Objects:

List<MyObj> objs = new []{
    new MyObj{TeamId=2, PlayerId=3, Val1=1234, Val2=111},
    new MyObj{TeamId=2, PlayerId=4, Val1=2345, Val2=444},
    new MyObj{TeamId=2, PlayerId=5, Val1=3456, Val2=666},
    };

I could insert all these objects into DB using

db.InsertBatch(objs);

But some records with the same composite key could be already in DB, so instead of INSERT I would like to have an update for these objects executed automatically.

I know that BLToolkit has InsertOrUpdate and InsertOrReplace, but they both work with the only 1 object.

Is there any way I can InsertOrUpdate/Replace the list of objects in 1 request to DB?

Thank you.

P.S. In fact, data which I listed in objs are not created programmatically, they are extracted from DB recently based on query other tables. Maybe together with getting those data I should try and fetch IDs for existing objects and use this information to decide insert or update?

Answer 1

If your objects come from another table, you can do a left join to sort which objects need Insert or Update :

var useInsertQuery = from o in db.OtherObjs
            join m in db.MyObjs on new { m.TeamId, m.PlayerId } equals new { o.TeamId, o.PlayerId } into moJoin
            from mo in moJoin.DefaultIfEmpty()
            let useInsert = (mo == null)
            // MyObj f(OtherObj otherObj) creates your updated MyObj value
            group f(m,o) by useInsert into g;
var useInsertDictionary = useInsertQuery.ToDictionary(g => g.Key, g => g.ToArray());

MyObj[] objs;
if (useInsertDictionary.TryGetValue(true, out objs))
    db.InsertBatch(objs);
if (useInsertDictionary.TryGetValue(false, out objs))
    db.Update(objs);