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.

score 7 · Answer 1 · answered Jul 06 '10 at 16:26

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.

I agree, performance isn't a concern here so use the LINQ approach over the ugly SQL — , Nov 17 '10 at 00:58

score 7 · Accepted Answer · answered Jul 06 '10 at 16:33

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.

score 0 · Answer 3 · answered Jul 06 '10 at 16:24

0

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();

answered Jul 06 '10 at 16:24

gnome

1,113
2
11
19

3

Won't it give me "duplicate id" if I already have Toon with Id = 1? – THX-1138 Jul 06 '10 at 16:40

score 0 · Answer 4 · edited May 23 '17 at 12:09

To update 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();

See here for an extension method for this.

Do MERGE using Linq to SQL

4 Answers4