Do MERGE using Linq to SQL

Question

SQL Server 2008 Ent ASP.NET MVC 2.0 Linq-to-SQL

I am building a gaming site, that tracks when a particular player (toon) had downed a particular monster (boss). Table looks something like:

int ToonId
int BossId
datetime LastKillTime

I use a 3d party service that gives me back latest information (toon,boss,time).
Now I want to update my database with that new information.
Brute force approach is to do line-by-line upsert. But It looks ugly (code-wise), and probably slow too.

I think better solution would be to insert new data (using temp table?) and then run MERGE statement.

Is it good idea? I know temp tables are "better-to-avoid". Should I create a permanent "temp" table just for this operation?
Or should I just read entire current set (100 rows at most), do merge and put it back from within application?

Any pointers/suggestions are always appreciated.

Answer 1

For SQL 2008 you can insert multiple rows with a single SQL statement: http://msdn.microsoft.com/en-us/library/dd776382.aspx

However I don't know if / how you can use that from LINQ-to-SQL.

Answer 2

Looks like a straight-forward insert.

private ToonModel _db = new ToonModel();
Toon t = new Toon();
t.ToonId = 1;
t.BossId = 2;
t.LastKillTime = DateTime.Now();
_db.Toons.InsertOnSubmit(t);
_db.SubmitChanges();

Answer 3

If you're using Linq-to-SQL, upserts aren't that ugly..

foreach (var line in linesFromService) {
   var kill = db.Kills.FirstOrDefault(t=>t.ToonId==line.ToonId && t.BossId==line.BossId);
   if (kill == null) {
      kill = new Kills() { ToonId = line.ToonId, BossId = line.BossId };
      db.Kills.InsertOnSubmit(kill);
   }
   kill.LastKillTime = line.LastKillTime;
}
db.SubmitChanges();

Not a work of art, but nicer than in SQL. Also, with only 100 rows, I wouldn't be too concerned about performance.

Answer 4

An ORM is the wrong tool for performing batch operations, and Linq-to-SQL is no exception. In this case I think you have picked the right solution: Store all entries in a temporary table quickly, then do the UPSERT using merge.

The fastest way to store the data to the temporary table is to use SqlBulkCopy to store all data to a table of your choice.