customizing Azure Table storage Query Filtering criteria

This article is based on the Azure table storage basic usage article, which describes how to customize the query filtering criteria for Azure table storage. If you are not sure about the basic use of Azure Table storage, please move on to the previous article.

Let's go back to one of the questions mentioned in the previous article, how do I filter out all the logs generated on a given day in the Mylogtable table? Before we go into the details, let's review the design of the Mylogtable class:

Internal class mylogentity:tableentity{    public  mylogentity () {}    public mylogentity ( string string Rkey)    {        this. PartitionKey = Pkey;         this. RowKey = rkey;    }     // ... ..

Where PartitionKey is used to store the year and month in which the log was generated (for example, 201607 for July 2016), RowKey is used to store the day and seconds of the log generation (for example, 160934248492 for 16th, 9:34 ...).

In our designed mylogtable, the day information is stored in the first two bits of RowKey. All we have to do is filter the top two bits of RowKey, that is, find all the records that RowKey with "XX". This is called StartsWith in a string operation. Unfortunately, there is no way to provide this functionality in the interface of the existing Table storage, so we need to implement it ourselves (fortunately the Tablequery implementation supports us to extend it).

This article describes how to customize the query filtering criteria for Azure Table storage by implementing the StartsWith filtering criteria.

Tablequery class

Tablequery is the protagonist of this article, which represents a query on table. The basic usage is to construct an instance of the Tablequery class using the query criteria, and then pass the instance as a parameter to the Cloudtable ExecuteQuery method:

New Tablequery<mylogentity>(). Where (    tablequery.generatefiltercondition ("PartitionKey""  201607")); var queryresult = logtable.executequery (query);

We can also use Tablequery's static method combinefilters to build a custom query condition.
For example, we want to query PartitionKey equals "201607" and RowKey equals "161148372454" record:

tablequery.combinefilters (    tablequery.generatefiltercondition ("PartitionKey  "201607"),    Tableoperators.and,    Tablequery.generatefiltercondition ("RowKey""161148372454 "));

The return result of the function is: "(PartitionKey eq ' 201607 ') and (RowKey eq ' 161148372454 ')".

Then send this filter string to query. The Where function does the argument, or sets it to query. Filterstring properties, you can complete the filtering function.

The lovely thing about the Combinefilters method is that we can constantly use it to combine query conditions until we are satisfied!

Let's take a look at the implementation of StartsWith filtering conditions.

Comparing strings

How do I find strings that start with a substring from some strings? We can start with a comparison of strings.

For example, a string has the following relationship:

"ABC" = = "abc" < "Abd"

"ABC" < "ABCA" < "Abd"

"ABC" < "Abcz" < "Abd"

By the size relationship above we can conclude that the string starting with "ABC" must be greater than or equal to "ABC" and less than "Abd". OK, this is the theoretical basis for our construction of StartsWith filter conditions.

Building StartsWith Filter Conditions

Next we build the StartsWith filter by the Tablequery.combinefilters method:

string startswithcondition = tablequery.combinefilters (    tablequery.generatefiltercondition ( " RowKey " " ABC " ),    Tableoperators.and,    tablequery.generatefiltercondition ("RowKey " " Abd " )    );

The return value of the Tablequery.combinefilters method is a string. Run the above code and we'll get the string:

"(RowKey ge ' abc ') and (RowKey lt ' Abd ')"

We can spell such a string manually, but I'm sure no programmer is willing to do so.

So, we need to continue to refine the above approach:

stringStartstr ="ABC";intEndIndex = Startstr.length-1; Char Lastchar=Startstr[endindex];//find the character that is larger than the character ' C '. Char Afterlastchar = (Char) (Lastchar +1);//spell out the string "abd"stringEndstr = startstr.substring (0, EndIndex) +Afterlastchar;stringStartswithcondition =tablequery.combinefilters (Tablequery.generatefiltercondition ("RowKey", Querycomparisons.greaterthanorequal, Startstr), Tableoperators.and, Tablequery.generatefiltercondition ( "RowKey", Querycomparisons.lessthan, Endstr));

Combine more filter conditions

We have used the Tablequery.combinefilters method to combine different filter conditions when building StartsWith filters in front of us. Unfortunately the Tablequery.combinefilters method has only two overloads of the parameters, and we cannot add more tableoperators operations.

But we can continue to call the Tablequery.combinefilters method to combine the previous result and the new condition. For example, we can do this by combining the Startswith filter conditions with the PartitionKey filter conditions:

string filtercondition = tablequery.combinefilters (    tablequery.generatefiltercondition (  "PartitionKey" "201607"),    Tableoperators.and,     "(RowKey ge ' abc ') and (RowKey lt ' Abd ')"    );

Run the above code, and the resulting result is:

(PartitionKey eq ' 201607 ') and ((RowKey ge ' abc ') and (RowKey lt ' Abd '))

It is clear from here that the main task of the Tablequery.combinefilters method is to organize the filtering conditions into strings that the query engine can recognize.

As a result, we can create very complex filter conditions through constant superposition.

Package StartsWith Filter Conditions

Below we encapsulate the logic of StartsWith into the Startswithbyrowkey type, the following is the complete code:

 Public classmylogentity:tableentity{ Publicmylogentity () {} PublicMylogentity (stringPkeystringRkey) {          This. PartitionKey =Pkey;  This. RowKey =Rkey; }      PublicDateTime Logdate {Get;Set; }  Public stringLogMessage {Get;Set; }  Public stringErrorType {Get;Set; }} Public classStartswithbyrowkey:iquery<cloudtable, list<mylogentity>>{     Private ReadOnly stringPartitionKey; Private ReadOnly stringstartswithstring; InternalStartswithbyrowkey (stringPartitionKey,stringstartswithstring) {          This. PartitionKey =PartitionKey;  This. startswithstring =startswithstring; }      PublicList<mylogentity>Execute (cloudtable coludtable) {varquery =NewTablequery<mylogentity>(); intEndIndex = Startswithstring.length-1; Char Lastchar=Startswithstring[endindex]; Char Afterlastchar= (Char) (Lastchar +1); stringEndstr = startswithstring.substring (0, EndIndex) +Afterlastchar; stringStartswithcondition =tablequery.combinefilters (Tablequery.generatefiltercondition ("RowKey", Querycomparisons.greaterthanorequal, startswithstring), Tableoperators.and, Tablequery.gener Atefiltercondition ("RowKey", Querycomparisons.lessthan, endstr)); stringFiltercondition =tablequery.combinefilters (Tablequery.generatefiltercondition ("PartitionKey", Querycomparisons.equal, PartitionKey), Tableoperators.and, startswithcondition)         ; varentities =coludtable.executequery (query.         Where (filtercondition)); returnentities.     ToList (); } }  Public Interfaceiquery<inchTModel, outTresult>{TResult Execute (TModel model);}

Example of applying startswith

Now the query PartitionKey is "201607", and the records beginning with "16" can be written like this:

New Startswithbyrowkey ("201607"); List<MyLogEntity> result = Mystartswithquery.execute (logtable);

The code is a lot easier to read and clearer (you can also add the same functionality to PartitionKey)!

Briefly, this paper introduces the type of tablequery, and then gives a detailed description of the idea and realization of StartsWith filtration condition. This paper mainly wants to show how to use the existing types and methods to implement the filtering condition of the custom query through the implementation of StartsWith. For friends with similar needs, hope to play a role.

customizing Azure Table storage Query Filtering criteria