Azure Table Storage - Selecting a partial range with ID's used in Partition and Row Keys

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!

Azure Table Storage - don’t use forward slash / character in PartitionKey or RowKey

 You’ll get the following error: “One of the request inputs is out of range.”

Characters Disallowed in Key Fields:

The following characters are not allowed in values for the PartitionKey and RowKey properties:

• The forward slash (/) character
• The backslash (\) character
• The number sign (#) character
• The question mark (?) character 

http://msdn.microsoft.com/en-us/library/dd179338.aspx

Azure "SetConfigurationSettingPublisher needs to be called before FromConfigurationSetting can be used"

When you get the following exception: 

SetConfigurationSettingPublisher needs to be called before FromConfigurationSetting can be used

A work around is to retrieve you connection string using CloudStorageAccount's Parse() method instead of the FromConfigurationSetting() method.  For example, replace:

        private static CloudStorageAccount storageAccount =

        CloudStorageAccount.FromConfigurationSetting("DataConnectionString");

with:

        private static CloudStorageAccount storageAccount = CloudStorageAccount.Parse(

            RoleEnvironment.GetConfigurationSettingValue("DataConnectionString"));

Azure "DataServiceQueryException was unhandled"

System.Data.Services.Client.DataServiceQueryException was unhandled 

Message -An error occurred while processing this request.
 
Inner Exception Message - One of the request inputs is not valid.

1)Don't forget that you need to create the tables programmatically if they don't already exist. Something like this:

            tableClient.CreateTableIfNotExist("Customer");

2)Insert before Querying a table:

I finally found out that when using local storage, you must insert a record into a table before querying it. Note that a record does not have to be in the table when you are actually querying it, but one has to have existed in the table at some point. Note that this ONLY applies to local storage, cloud storage does not have this limitation.