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.

Example

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();
tableClient.CreateTableIfNotExist("Items");

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);
context.UpdateObject(existingItem);
context.SaveChanges();

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

image

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);
newContext.UpdateObject(sameItem);
newContext.SaveChanges();

Afterwards you should see

image

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.

newContext.SaveChanges(SaveChangesOptions.ReplaceOnUpdate);

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

Leave a Reply