how to query across multiple partition keys in an azure storage table?

user_1078009 0
Viewed 541

I have the following use case:

  • user requests creation of a widget. A new record is created with the details in a "request" partition in my storage table.
  • when the request is fulfilled - the original request is deleted from the "request" partition and created anew in the "provisioned" partition.

When it comes to querying for a widget - I need to search both the request and provisioned partitions. From reading the docs, it seems that I can create filters? But it seems to be a lot of overhead because I know i will only ever have 1 record returned (hopefully, unless I have a bug). But it seems I have to add a lot of logic to handle pagination.

So far this is what i'm moving from:

 entity = tableClient.GetEntity<TableEntity>(
        "requested",
         requestId);

To something like this:

            //entity = tableClient.GetEntity<TableEntity>(
            //                    "requested",
            //                    requestId); 

            List<string> filters = new List<string>();
            filters.Add($"PartitionKey eq '{"requested"}'");
            filters.Add($"PartitionKey eq '{"provisioned"}'");
            filters.Add($"RowKey eq '{requestId}'");
            string filter = String.Join(" and ", filters);
            //should only ever return one ... but ...
            var entities =  tableClient.QueryAsync<TableEntity>(filter);

            await foreach (Page<TableEntity> page in entities.AsPages())
            {
                Console.WriteLine("This is a new page!");
                foreach (TableEntity qEntity in page.Values)
                {
                    Console.WriteLine($"i found this request:  {qEntity.GetString("requestId")} with status: {"status"}");
                }
            }

This code presently doesn't work - will need to debug it. but I thought i'd check to see if there's an easier way to do this.

Thanks.

azure-table-storage azure
2 Answers

Considering an entity will be present in just one partition only, you could change your code to something like:

List<string> filters = new List<string>();
filters.Add($"(PartitionKey eq 'requested' and RowKey eq '{requestId}')");
filters.Add($"(PartitionKey eq 'provisioned' and RowKey eq '{requestId}')");
string filter = String.Join(" or ", filters);
//should only ever return one ... but ...
var entities =  tableClient.QueryAsync<TableEntity>(filter);

await foreach (Page<TableEntity> page in entities.AsPages())
{
    Console.WriteLine("This is a new page!");
    foreach (TableEntity qEntity in page.Values)
    {
        Console.WriteLine($"i found this request:  {qEntity.GetString("requestId")} with status: {"status"}");
    }
}
user_1880960

I would recommend first writing a TryGetEntityAsync extension method:

public static async Task<Response<T>?> TryGetEntityAsync<T>(this TableClient tableClient, string partitionKey, string rowKey, IEnumerable<string>? select = default, CancellationToken cancellationToken = default) where T : class, ITableEntity, new()
{
  try
  {
    return await tableClient.GetEntityAsync<T>(partitionKey, rowKey, select, cancellationToken);
  }
  catch (RequestFailedException ex) when (ex.Status == 404)
  {
    return null;
  }
}

Then, you can do two point queries one at a time:

var entity =
    (await tableClient.TryGetEntityAsync<TableEntity>("requested", requestId)) ??
    (await tableClient.TryGetEntityAsync<TableEntity>("provisioned", requestId));

or concurrently:

var requestedEntityTask = tableClient.TryGetEntityAsync<TableEntity>("requested", requestId);
var provisionedEntityTask = tableClient.TryGetEntityAsync<TableEntity>("provisioned", requestId);
var entities = await Task.WhenAll(requestedEntityTask, provisionedEntityTask);
var entity = entities[0] ?? entities[1];
user_2636930
Related