Click here to Skip to main content
12,450,977 members (24,504 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

11.2K views
4 bookmarked
Posted

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;

You may also be interested in...

Pro
Pro

Comments and Discussions

 
GeneralMy vote of 5 Pin
AJSON22-Jul-16 3:42
mvpAJSON22-Jul-16 3:42 
QuestionWhat if the Column contains a number? Pin
MyNameIsCode16-Jun-15 6:09
memberMyNameIsCode16-Jun-15 6:09 
QuestionUnicode languages Pin
abdurahman ibn hattab29-Jun-14 15:43
memberabdurahman ibn hattab29-Jun-14 15:43 
AnswerRe: Unicode languages Pin
Sergiy Korzh29-Jun-14 18:28
professionalSergiy Korzh29-Jun-14 18:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    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
Web01 | 2.8.160826.1 | Last Updated 27 Jun 2014
Article Copyright 2014 by Sergiy Korzh
Everything else Copyright © CodeProject, 1999-2016
Layout: fixed | fluid