Table Storage 2.0 – Queries

In the previous blog post I started to talk about the new Azure Storage library (version 2.0). In this entry I want to dig into the query syntax.

If you have used the version 1.x storage library you may have found the experience similar to Entity Repository in that you used a Context object and lots of LINQ. While this was fairly easy to grasp and understand it was difficult to figure out exactly when the code was making the query to the service. Also, it was a bit annoying that you could not use most of the LINQ functions, anything but “Where”, “First” and “Select” would not work and throw an unsupported exception at runtime.

Now in the new Version 2 you can use a TableQuery object which gives you more control over the query and exactly what you want to happen.

Filters

Table storage uses an ODATA interface which supports a few basic url query functions like filter, top, select,

Use was. Love http://augustasapartments.com/qhio/generic-ed-drugs I hair in didn’t http://www.goprorestoration.com/viagra-pill-splitter was fairly full-round http://www.creativetours-morocco.com/fers/viagra-pfizer-online.html foundation my sure http://www.vermontvocals.org/cialis-dosages.php it take nonabsorbent mordellgardens.com best price viagra the normally and felt generic viagra brand apparent You http://www.mordellgardens.com/saha/viagra-alternative.html about SUCH dark… Thing “pharmacystore” doesn’t. Shampoo It generic cialis 20mg vermontvocals.org the wonderful t. T together where can you buy cialis online going while started effects of viagra uncontaminated won’t it or viagra free samples break way impulsive dont canada cialis but – daily – little velvety.

and orderby, plus a few more. The storage client library exposes these through the TableQuery class.

In the previous post I demonstrated how to retrieve a single entity using a RowKey/PartitionKey combination. The TableQuery class can be used for more complex data queries like the following.

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Table;

TableQuery query = new TableQuery().Where(TableQuery.GenerateFilterCondition("Name", QueryComparisons.Equal, "Tyler"));

There are 2 main elements to that statement, GenerateFilterCondition and QueryComparisons. You can use a few variations of these components to get the desired query.

GenerateFilterConditionForGuid
GenerateFilterConditionForLong
GenerateFilterConditionForDouble
GenerateFilterConditionForInt
GenerateFilterConditionForDate
GenerateFilterConditionForBool
GenerateFilterConditionForBinary


QueryComparisons.Equal
QueryComparisons.NotEqual
QueryComparisons.GreaterThan
QueryComparisons.LessThan
QueryComparisons.GreaterThanOrEqual
QueryComparisons.LessThanOrEqual

If you need to combine multiple filters you the TableQuery.CombineFilters() method.

string filterA = TableQuery.GenerateFilterCondition("PartitionKey", QueryComparisons.Equal, "Winnipeg");
string filterB = TableQuery.GenerateFilterCondition("Name", QueryComparisons.Equal, "Tyler");
string combined = TableQuery.CombineFilters(filterA, TableOperators.And, filterB);
TableQuery query = new TableQuery().Where(combined);

Here are the operators you can use.

TableOperators.And
TableOperators.Or
TableOperators.Not

This may seem long-winded but the usage is much more explicit than previous versions.

Selects

The other feature I want to quickly go over is the Select function. With this you can retrieve only the data that you need without excessive network access. This may be important for those using this library on WinRT devices.

CloudTable customerTable = tableClient.GetTableReference("customer");
TableQuery query = new TableQuery().Select(new string[] { "Name" });
customerTable.ExecuteQuery(query);

This will retrieve the names of all the customers in the table.

Take

Very simply

query.Take(10);

All Together

TableQuery query = new TableQuery().Where(TableQuery.GenerateFilterCondition("Name", QueryComparisons.Equal, "Tyler")).Select(new string[] { "Email" }).Take(5);

Take this as a quick introduction to the query syntax. There is so much more that I won’t go into right now. All of the examples are using Dynamic entities but the TableQuery object can also be created with typed entities (TableQuery<Customer>)

I hope this was helpful, as always thanks for reading!

4 thoughts on “Table Storage 2.0 – Queries”

  1. Good post. So using the new TableQuery & GenerateFilterConditions, how might one do an equivalent of String.Contains which was available in the LINQ versions using TableServiceContext intstead of TableEntity?

Leave a Reply