StartsWith comparison for searching in Azure Tables

Problem

If you are using Azure Table Storage service in your application, you may know that its searching and filtering functionality is quite limited. You can create a query and pass it to ExecuteQuery method of CloudTable class but the list of comparison operators you may use in your query includes only such operations as "Equal", "NotEqual", "LessThen", "GreaterThen" (see QueryComparisons class definition for details). No chances to use something like "contains" or "starts with".

Solution

With the following little trick, you can get "starts with" functionality even without a native support for it by Azure SDK classes ("contains" operation still remains unavailable however).

So the trick is based on principles of string comparison. When we compare two strings A and B, we actually compare them character by character and find the first two characters that differ. Then the string whose character comes earlier in the alphabet is the one which comes first in alphabetical order.

This rule allows us to implement "starts with" search using only "greater or equal" and "less than" operations which (thanks God) we already have.

For example, if we trying to find all strings which starts with "hel", we actually need to find all such strings X which: "hel" <= X < "hem" because "m" is the next character in alphabet after "l".

Code

To implement the described approach, I wrote the following extension method for CloudTable class:

public static class CloudExtensions {

    public static IEnumerable<TElement> StartsWith<TElement>
    (this CloudTable table, string partitionKey, string searchStr, 
    string columnName = "RowKey") where TElement : ITableEntity, new()     
    {
        if (string.IsNullOrEmpty(searchStr)) return null;

        char lastChar = searchStr[searchStr.Length - 1];
        char nextLastChar = (char)((int)lastChar + 1);
        string nextSearchStr = searchStr.Substring(0, searchStr.Length - 1) + nextLastChar;
        string prefixCondition = TableQuery.CombineFilters(
            TableQuery.GenerateFilterCondition(columnName, QueryComparisons.GreaterThanOrEqual, searchStr),
            TableOperators.And,
            TableQuery.GenerateFilterCondition(columnName, QueryComparisons.LessThan, nextSearchStr)
            );

        string filterString = TableQuery.CombineFilters(
            TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, partitionKey),
            TableOperators.And,
            prefixCondition
            );
        var query = new TableQuery<TElement>().Where(filterString);
        return table.ExecuteQuery<TElement>(query);
    }
}

By default, we search by RowKey column, but you can pass the name of any other column in your table in the last parameter. Please note that the time of search in the case will be much longer since all other columns in Azure Tables except PartitionKey and RowKey are not indexed.

Using the Code

To use it, just call StartsWith method for your CloudTable object and pass the value of partition key and the string (sub-string actually) you are searching by.

var words = myCloudTable.StartsWith<MyEntity>("SomePartitionKey", word);

That's all. Enjoy!

History

• First publication: 27-Jun-2014