Querying Azure Table Storage with Multi-Part Partition Key

If you have an Azure Table Storage table with a partition key made up of multiple parts (e.g. “{companyId}:{employeeId}“) where the former is of predictable string length (e.g. a GUID) and want to, for example, return all the rows for a company you could achieve this by searching for rows with a partition key of greater than “{companyId}:” and less than “{companyId + 1}:” (where +1 signifies alphabetically adding 1, so adding 1 to a companyId GUID ending with “a” would result in the GUID having a “b” at the end instead). Simple enough, right?

However, what if your companyId does not have a predictable length, like if it’s an int (not padded with zeros)? Using the previous example, undesired results would be returned. Let me explain with an example:

If the companyId is 1, and we search for greater than “1:” and less than “2:“, we will also see results for companies 20, 21, 22, 23, …, 200, 201, …, 2000… you get the idea. This is because when characters are converted to a numeric (ASCII or Unicode, it doesn’t matter for this example) value “:” has a value (58) higher than the number values (48 to 57).

To resolve this, we can simply search for greater than “1:” and less than 2 followed by a character with a lower value than zero. Space ( ), exclamation mark (!), hash (#), asterisk (*), dollar ($), etc. all meet these criteria! We chose to use a hash as we thought it was the most obvious (a space might go unnoticed!).

int companyId = 0;
var query = new TableQuery().Where(
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, companyId.ToString() + ":"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, (companyId + 1).ToString() + "#")
    )
);

EDIT: My boss pointed out the very next day that actually, not appending anything would work just as well. This relies on the fact that “be” comes before “because” alphabetically.

int companyId = 0;
var query = new TableQuery().Where(
    TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.GreaterThanOrEqual, companyId.ToString() + ":"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.LessThan, (companyId + 1).ToString())
    )
);