azure table storage custom rowKey auto increment

Question

I have a azure table storage where I use a custom "number" as Rowkey(format: YYYY-1XXXX) is there I way, that when I insert a new item (via React js ajax Post request to my webApi) to increase the 1XXXX part by 1 ? like so: 2017-10000 -> next id should be 2017-10001.

Answer 1

No. Azure table storage has no method to automatically increment a columns' value.

To achieve this you would need to manually set the RowKey in the new entity, which in turn means that you would need to know what the RowKey of the previous entity was before saving.

Answer 2

There is no out of shelf support for it but there is a pattern to do what you want for the entities with the same Partition Key.

Firstly subtract the row key you want to increment from max int and use that value as your row key instead. Within the same partition key, this pattern is going to cause azure table service keep the latest inserted entity on top of the table since on the backend azure table stores the entities in a lexicographical order.

Next when you want to insert a new entity make a query with only the partition key and Take(1), that query is going to return you the latest inserted entity without any table scan for that partition (because the row keys are inverted).

Then you calculate the next row key based on row key of the latest inserted entity and insert the new entity to table storage.

At this stage 2 things can happen, either everything works fine and you inserted your new entity at the top of the table for that partition key or between the time you read and insert someone else inserted the same entity with the same row key already, at this stage you get http status code 409 and simply repeat the steps in a retry loop.

Answer 3

According to your scenario, I recommend that you could use Blob Storage as a central store for storing your latest auto-increment value, you need to read/update the value when you adding a new item to your table storage. It's simple to use the optimistic concurrency when updating resources in Azure Blob Storage, with the specific Preconditions and Entity Tags (ETags) for updating your blob, if the blob changes, the ETag would also change and if the ETag does not match, then a HTTP Status Code 412 (Precondition failed) would encounter, then you could retry and regenerate the new value.

You could refer to this blog for more details. Also, you could refer to here for the source code about BlobOptimisticSyncStore.cs and UniqueIdGenerator.cs. Additionally, here is a similar issue about Auto-increment on Azure Table Storage, you could refer to it.