How to use Date as partition key in Azure table?

Question

I am creating a table that is going to store a lot of recordas per day on batch processes. Those records will be used for queries for a given date, so I decided it would be good to use short dates ("MM-dd-yyyy") as partition key.

I create the entity like this:

ITableEntity recordEntity = new DynamicTableEntity()
{
    PartitionKey = DateTime.ParseExact((string)record["authorization_date"], "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal).ToShortDateString(),
    RowKey = Guid.NewGuid().ToString(),
    Properties =
    {
        {"customer", new EntityProperty(customer)},
        {"responseId", new EntityProperty(record["response_id"].ToString())},
        {"Date", new EntityProperty(DateTime.ParseExact((string)record["date"], "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal))},
    }
};

The entity is created as intended, but it throwing an storageException with message: "The remote server returned an error: (400) Bad Request.".

This is how an entity looks:

ETag [string]: null
PartitionKey [string]: "2/22/2024"
Properties [IDictionary]: Count = 11
RowKey [string]: "a5768ba3-eb0e-4eb9-97b8-38eec67bc5f3"
Timestamp [DateTimeOffset]: {1/1/0001 12:00:00 AM +00:00}

and how I'm inserting the record into the table.

try
{
    TableOperation insertTransactionOperation = TableOperation.Insert(recordEntity);
    recordTable.Execute(insertTransactionOperation);
    storedRecords++;
}
catch (System.Exception ex)
{
    _logger.LogError(ex.Message);
    errorRecords++;
    throw;
}

I changed the partitionKey value to a normal text string like "ts", and it was sotred successfully. I assume the issue is because of the formatting of the date, but I don't know how to solve it. At the end of the day, it is a plain string.

Thank you,

Answer 1

Observing your code and it's likely a stems from the format of the partition key you're using. Azure Table Storage has specific requirements for partition and row keys. The format you're using, "MM-dd-yyyy", might be causing the problem because of the '/' character, which is not allowed in partition keys.

To fix this, you might consider formatting your partition key differently.

This is an example of how you can modify your code to use the "MMddyyyy" format:

ITableEntity recordEntity = new DynamicTableEntity()
{
    PartitionKey = DateTime.ParseExact((string)record["authorization_date"], "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal).ToString("MMddyyyy"),
    RowKey = Guid.NewGuid().ToString(),
    Properties =
    {
        {"customer", new EntityProperty(customer)},
        {"responseId", new EntityProperty(record["response_id"].ToString())},
        {"Date", new EntityProperty(DateTime.ParseExact((string)record["date"], "yyyy-MM-dd HH:mm:ss", CultureInfo.InvariantCulture, DateTimeStyles.AssumeUniversal))},
    }
};

This will format the date without any special characters in the partition key, which should resolve the issue you're facing with the Bad Request error.

You can read more in the previous links and answers, for examples, here

Answer 2

The reason you are getting the error is because the value of the PartitionKey contains / character which is not allowed. You may want to use - instead of / in the PartitionKey name (same rules apply for RowKey as well).

Please see this link for more details: https://learn.microsoft.com/en-us/rest/api/storageservices/understanding-the-table-service-data-model#characters-disallowed-in-key-fields.

As a side note, you may want to rethink about storing dates as is in PartitionKey. The reason I say that the PartitionKey is of String data type and sorting may become an issue. One recommendation is to convert the date value in ISO 8601 format (yyyy-MM-dd format). Other recommendation would be to convert it into Ticks (DateTime.Ticks) and pad it in front with zeros so that all your PartitionKey values are of same length.