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

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.

CodePudding user response：

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"}");
    }
}

CodePudding user response：

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(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];