17

I'm working with Windows Azure Table Storage and have a simple requirement: add a new row, overwriting any existing row with that PartitionKey/RowKey. However, saving the changes always throws an exception, even if I pass in the ReplaceOnUpdate option:

tableServiceContext.AddObject(TableName, entity);
tableServiceContext.SaveChangesWithRetries(SaveChangesOptions.ReplaceOnUpdate);

If the entity already exists it throws:

System.Data.Services.Client.DataServiceRequestException: An error occurred while processing this request. ---> System.Data.Services.Client.DataServiceClientException: <?xml version="1.0" encoding="utf-8" standalone="yes"?>
<error xmlns="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata">
  <code>EntityAlreadyExists</code>
  <message xml:lang="en-AU">The specified entity already exists.</message>
</error>

Do I really have to manually query for the existing row first and call DeleteObject on it? That seems very slow. Surely there is a better way?

EMP
  • 59,148
  • 53
  • 164
  • 220

6 Answers6

16

As you've found, you can't just add another item that has the same row key and partition key, so you will need to run a query to check to see if the item already exists. In situations like this I find it helpful to look at the Azure REST API documentation to see what is available to the storage client library. You'll see that there are separate methods for inserting and updating. The ReplaceOnUpdate only has an effect when you're updating, not inserting.

While you could delete the existing item and then add the new one, you could just update the existing one (saving you one round trip to storage). Your code might look something like this:

var existsQuery = from e
                    in tableServiceContext.CreateQuery<MyEntity>(TableName)
                    where
                    e.PartitionKey == objectToUpsert.PartitionKey
                    && e.RowKey == objectToUpsert.RowKey
                    select e;

MyEntity existingObject = existsQuery.FirstOrDefault();

if (existingObject == null)
{
    tableServiceContext.AddObject(TableName, objectToUpsert);
}
else
{
    existingObject.Property1 = objectToUpsert.Property1;
    existingObject.Property2 = objectToUpsert.Property2;

    tableServiceContext.UpdateObject(existingObject);
}

tableServiceContext.SaveChangesWithRetries(SaveChangesOptions.ReplaceOnUpdate);

EDIT: While correct at the time of writing, with the September 2011 update Microsoft have updated the Azure table API to include two upsert commands, Insert or Replace Entity and Insert or Merge Entity

SteveC
  • 15,808
  • 23
  • 102
  • 173
knightpfhor
  • 9,299
  • 3
  • 29
  • 42
  • Thanks, I did consider that, but it means that this code has to be aware of every property that needs to be copied (or the entity itself needs a `CopyTo` method) and it needs to be updated whenever the properties change. I think I'd rather pay the cost of the delete and be safe. – EMP Dec 17 '10 at 03:27
  • Well, I ended up doing this, as much as I didn't want to. No matter how I tried to delete and re-insert the entity it just didn't work realiably - one time it would succeed, next time fail with one error, next time fail with another. – EMP Jan 11 '11 at 21:50
  • I find that surprising. Were you trying to save both the add and the delete as part of one .Save() call, or were you doing .AddObject(); .Save(); .DeleteObject(); .Save(); – knightpfhor Jan 12 '11 at 02:37
1

Insert/Merge or Update was added to the API in September 2011. Here is an example using the Storage API 2.0 which is easier to understand then the way it is done in the 1.7 api and earlier.

public void InsertOrReplace(ITableEntity entity)
    {
        retryPolicy.ExecuteAction(
            () =>
            {
                try
                {
                    TableOperation operation = TableOperation.InsertOrReplace(entity);
                    cloudTable.Execute(operation);
                }
                catch (StorageException e)
                {
                    string message = "InsertOrReplace entity failed.";

                    if (e.RequestInformation.HttpStatusCode == 404)
                    {
                        message += " Make sure the table is created.";
                    }

                    // do something with message
                }
            });
    }
lockwobr
  • 1,461
  • 1
  • 15
  • 24
  • 2
    There's a gotcha in this code, to do with optimistic concurrency. You might think that "InsertOrReplace" works like "Insert" and "Replace" in that it'll Upsert as long as the record hasn't been changed since you last checked it. But that would be wrong. "InsertOrReplace" actually means "overwrite that record no matter what, and ignore the optimistic concurrency checks". So if you don't care about optimistic concurrency then great! Otherwise, you probably don't want to be using this as "Upsert". –  Oct 08 '13 at 15:56
1

In order to operate on an existing object NOT managed by the TableContext with either Delete or SaveChanges with ReplaceOnUpdate options, you need to call AttachTo and attach the object to the TableContext, instead of calling AddObject which instructs TableContext to attempt to insert it.

http://msdn.microsoft.com/en-us/library/system.data.services.client.dataservicecontext.attachto.aspx

Igorek
  • 15,716
  • 3
  • 54
  • 92
  • Thanks, but I don't know whether the entity already exists or not when my method is called. – EMP Dec 17 '10 at 02:55
  • Sorry, didn't read carefully enough the initial problem. You'll need to do a read before hand. Don't need to delete that row however. Just do an Update in place if the row exists or insert if it doesnt. No avoiding it. – Igorek Dec 17 '10 at 03:10
1

in my case it was not allowed to remove it first, thus I do it like this, this will result in one transaction to server which will first remove existing object and than add new one, removing need to copy property values

       var existing = from e in _ServiceContext.AgentTable
                       where e.PartitionKey == item.PartitionKey
                             && e.RowKey == item.RowKey
                       select e;

        _ServiceContext.IgnoreResourceNotFoundException = true;
        var existingObject = existing.FirstOrDefault();

        if (existingObject != null)
        {
            _ServiceContext.DeleteObject(existingObject);
        }

        _ServiceContext.AddObject(AgentConfigTableServiceContext.AgetnConfigTableName, item);

        _ServiceContext.SaveChangesWithRetries();
        _ServiceContext.IgnoreResourceNotFoundException = false;
zebra
  • 1,330
  • 1
  • 13
  • 26
  • I think you might need to look into what's really happening under the hood. As far as I'm aware the underlying REST API can't deal with adds and deletes in one call. So while you're just calling .Save() once, it will be sending two calls to the storage service. – knightpfhor Jan 10 '11 at 23:33
0

You may use UpsertEntity and UpsertEntityAsync methods in the official Microsoft Azure.Data.Tables TableClient.

The fully working example is available at https://github.com/Azure-Samples/msdocs-azure-data-tables-sdk-dotnet/blob/main/2-completed-app/AzureTablesDemoApplicaton/Services/TablesService.cs --

public void UpsertTableEntity(WeatherInputModel model)
{
    TableEntity entity = new TableEntity();
    entity.PartitionKey = model.StationName;
    entity.RowKey = $"{model.ObservationDate} {model.ObservationTime}";

    // The other values are added like a items to a dictionary
    entity["Temperature"] = model.Temperature;
    entity["Humidity"] = model.Humidity;
    entity["Barometer"] = model.Barometer;
    entity["WindDirection"] = model.WindDirection;
    entity["WindSpeed"] = model.WindSpeed;
    entity["Precipitation"] = model.Precipitation;

    _tableClient.UpsertEntity(entity);
}
Dariusz Woźniak
  • 9,640
  • 6
  • 60
  • 73
0

The Storage API does not allow more than one operation per entity (delete+insert) in a group transaction:

An entity can appear only once in the transaction, and only one operation may be performed against it.

see MSDN: Performing Entity Group Transactions

So in fact you need to read first and decide on insert or update.

fabsenet
  • 372
  • 2
  • 15