To explain, let’s say we have a row key comprised of SomeID and SomeOtherID delimited by a underscore, and we have the following set of records in a table:
1_1
2_3
10_4
100_5
If we wanted to get all records where SomeID is 1, we would use the “compareto” operator to retrieve a partial matching range. Sample code shown here:
startRowKey = “1_”
endRowKey = “2_”
public IList<T> GetAllByPartitionKeyAndRowKeyRange(string partitionKey, string startRowKey, string endRowKey)
{
CloudTableQuery<T> query = (from c in this.CreateQuery<T>(TableName)
where c.PartitionKey == partitionKey
&& c.RowKey.CompareTo(startRowKey) >= 0
&& c.RowKey.CompareTo(endRowKey) < 0
select c).AsTableServiceQuery();
query.RetryPolicy = this.RetrySettings;
IEnumerable<T> results = query.Execute();
return ConvertToList(results);
}
This would produce the following result set because of azures lexicographically ordering, as is compares one character at a time thus making 10 less than 2:
1_1
10_4
100_5
The only solution I could come up with was to build keys with the same character length and prepend x number of 0’s, or zero padding, for each id (we used 36, as that's a guid length). For ex, when inserting or selecting, an numerical id within a key would look like the following:
0...00001
This gives our set of records to look like the following:
0...00001_0...00001
0...00002_0...00003
0...00010_0...00004
0...00100_0...00005
Which will now produce an accurate result set when queried.
Again this is in the situation that you have ID's in your Partition and Row Keys so using the property bag is not a viable solution to get around this.
And remember not to do a partial look up on a Partition Key, that results in a regular old table scan.
This was a tough one, hope this helps!
