Click here to Skip to main content
Click here to Skip to main content

StartsWith comparison for searching in Azure Tables

, 27 Jun 2014 CPOL
Rate this:
Please Sign up or sign in to vote.
How to make search by StartsWith criteria 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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sergiy Korzh
Founder Korzh.com
Ukraine Ukraine
Software developer and entrepreneur.
 
Main projects:
* EasyQuery - ad-hoc data filtering UI for .NET applications;
* Localizer - localization tool kit for Delphi projects;
Follow on   Twitter   Google+

Comments and Discussions

 
QuestionUnicode languages Pinmemberabdurahman ibn hattab29-Jun-14 16:43 
AnswerRe: Unicode languages PinprofessionalSergiy Korzh29-Jun-14 19:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.141216.1 | Last Updated 27 Jun 2014
Article Copyright 2014 by Sergiy Korzh
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid