The Upsert

No, this is not some new dance move taking the country by storm. An Upsert is the combination of an Insert and Update. In SQL this operation can be accomplished by a MERGE statement.

The Upsert is a great way to reduce the overall transaction count of your application. Rather than two calls, a get then update or insert, you can use just one. This also helps with concurrency and performance.

There are two ways to perform an Upsert. 1) with merging and 2) with replacement. As far as the code is concerned the default is merging and to do a replace you just need to add an option to the Save Changes method.


Create a new Windows Azure project and add an empty Worker Role. Note: This did not work using the local emulated storage services. However, I do have an older version on this machine right now, try it with the latest version. If it throws an exception, then use a real storage account.

Create a data entity Item …

namespace WorkerRole1
    public class Item : TableServiceEntity
        public string Name { get; set; }
        public int Number { get; set; }
        public string Discription { get; set; }

Then in the WorkerRole.cs file put this set of code as a test.

var account = CloudStorageAccount.DevelopmentStorageAccount;

var tableClient = account.CreateCloudTableClient();

var context = tableClient.GetDataServiceContext();

//Inserts if new
var existingItem = new Item
    PartitionKey = "1",
    RowKey = "",
    Name = "existing",
    Number = 1,
    Discription = "this exists"

context.AttachTo("Items", existingItem);

If you put a breakpoint after the first Save Changes and you should see the following.


Finish the example with this merge code.

//merge example

var newContext = tableClient.GetDataServiceContext();

var sameItem = new Item
    PartitionKey = "1",
    RowKey = "",
    Discription = "after merge"

newContext.AttachTo("Items", sameItem);

Afterwards you should see


Because the other fields were empty the data stayed the same. If you want to do a replace you just need to call Save with the following option.


That’s all for now. As always thanks for reading!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s