Use MERGE using Linq to SQL

Question

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

I am creating a gaming site that tracks when a specific player (toon) has hit a specific monster (boss). The table looks something like this:

int ToonId int BossId datetime LastKillTime 

I use the 3d party service, which returns the latest information to me (toon, boss, time).
Now I want to update my database with this new information.
Brute force approach is to take turns in turn. But it looks ugly (by code) and probably too slow.

I think the best solution would be to insert new data (using a temporary table?) And then run the MERGE statement.

Is that a good idea? I know that temporary tables are "better avoided." Should I create a permanent table "temp" just for this operation?
Or should I just read the entire current set (no more than 100 lines), combine and return it from the application?

Any pointers / suggestions are always welcome.

Answer 1

ORM is the wrong tool for performing batch operations, and Linq-to-SQL is no exception. In this case, I think that you have chosen the right solution: quickly store all the records in a temporary table, then use UPSERT using merge.

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

Answer 2

If you use Linq-to-SQL, upserts are not so 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 better than in SQL. Also, with just 100 lines, I wouldn't be too worried about performance.

Answer 3

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

However, I do not know how and how you can use this from LINQ-to-SQL.

Answer 4

To update without first requesting entries, you can do the following. It will still beat db once to check if the 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(); 

See here for an extension method for this.

Answer 5

Looks like a straight 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();