Programming4us
         
 
 
Programming

Programming Windows Azure : Table Operations - Querying Data

11/21/2010 11:32:52 AM

In what is probably the most famous sequence in Douglas Adams’ The Hitchhiker’s Guide to the Galaxy (Pan Books), a pan-dimensional, hyper-intelligent race of beings want to know “the ultimate answer to life, the universe, and everything.” They build a supercomputer called Deep Thought that is the size of a small city, and they set it working on this problem. It calculates for seven and a half million years, and finally announces that it has the solution. The ultimate answer to life, the universe, and everything is…(drum roll)…42!

When the flabbergasted audience asks the computer whether it is sure, it answers:

[I] checked it very thoroughly, and that quite definitely is the answer. I think the problem, to be quite honest with you, is that you’ve never actually known what the question was.

Regardless of whether we’re talking about the ultimate answer to life, the universe, and everything, or just Azure’s Table service, the key is to ask the right question. Thankfully, using LINQ and ADO.NET Data Services to query Azure tables is a lot easier than dealing with hyper-intelligent, slightly obnoxious supercomputers.

Queries are the primary mechanism for retrieving data from Azure’s Table service. You can query for any entity (or a set of entities) in a table using the attributes that make up the entity. These are user-defined attributes, as well as the two “system” attributes: PartitionKey and RowKey. Queries can return a maximum of 1,000 entities as results, and there is a built-in pagination mechanism to retrieve more than 1,000 entities.

All queries get formatted into a $filter parameter that is sent as part of an HTTP GET to the Azure Table service. The service returns an Atom feed of entities formatted using the same representation as when the entities were uploaded. Example 1 shows a sample HTTP query to retrieve the entity that has been inserted.

Example 1. Sample request and response
Request
GET /ContactTable()?$filter=Name%20eq%20'Steve%20Jobs' HTTP/1.1


Response

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base="http://sriramk.table.core.windows.net/"
xmlns:d="http://schemas.microsoft.com/ado/2007/08/dataservices"
xmlns:m=http://schemas.microsoft.com/ado/2007/08/dataservices/metadata
xmlns="http://www.w3.org/2005/Atom">
<title type="text">ContactTable</title>
<id>http://sriramk.table.core.windows.net/ContactTable</id>
<updated>2009-04-21T08:29:12Z</updated>
<link rel="self" title="ContactTable" href="ContactTable" />
<entry m:etag="W/&quot;datetime'2009-04-21T06%3A38%3A28.242Z'&quot;">
<id>http://sriramk.table.core.windows.net/ContactTable(
PartitionKey='a844fa27-7ae2-4894-9cc6-dd0dbdcd5ec4',RowKey='')</id>
<title type="text"></title>
<updated>2009-04-21T08:29:12Z</updated>
<author>
<name />
</author>
<link rel="edit" title="ContactTable"
href="ContactTable(PartitionKey='a844fa27-7ae2-4894-9cc6-dd0dbdcd5ec4',
RowKey='')" />
<category term="sriramk.ContactTable"
scheme="http://schemas.microsoft.com/ado/2007/08/dataservices/scheme" />
<content type="application/xml">
<m:properties>
<d:PartitionKey>a844fa27-7ae2-4894-9cc6-dd0dbdcd5ec4</d:PartitionKey>
<d:RowKey></d:RowKey>
<d:Timestamp m:type="Edm.DateTime">2009-04-21T06:38:28.242Z</d:Timestamp>
<d:Address>One Infinite Loop</d:Address>
<d:Name>Steve Jobs</d:Name>
</m:properties>
</content>
</entry>
</feed>


If you look at the verbose XML returned from the service, you see that, apart from an Atom representation of the entities matching the query (in this case, only one), you also get back an ETag. This ETag is unique for every version of the entity, and is used to ensure that your client side updates only entities of which it has the latest copy.

Example 2 shows how you write the same query in LINQ. You can combine arbitrary logical operations in your query, and the “plumbing” underneath ensures that the right $filter query is generated.

Example 2. LINQ query
                       var account =
CloudStorageAccount.Parse(ConfigurationSettings.AppSettings
["DataConnectionString"]);
var svc = new TestDataServiceContext(account.TableEndpoint.ToString(),
account.Credentials);

var query = from contact in svc.CreateQuery<Contact>("ContactTable")
where contact.Name == "Steve Jobs"
select contact;

foreach(Contact c in query)
{
Console.WriteLine(c.Name);
}


You can construct arbitrarily complex queries. For example, let’s say you had an Employee table with a standard Employee type. You could construct queries such as that shown in Example 3. Consult the ADO.NET Data Services documentation on how to construct $filter queries if you’re interested in constructing these by hand.

Example 3. Sample queries
//This assumes an Entity class and table with properties like ID, Salary,
//Name, Department and so on.

//Get employees with specific salary range who don't work in engineering
var query = from emp in svc.CreateQuery<Employee>("EmployeeTable">
where emp.Salary>100000 && emp.Salary<150000
&& emp.Department != "Engineering";

//Get all lawyers. Might be useful when drawing up a list of people
// to get rid of. Just kidding!
var query = from emp.svc.CreateQuery<Employee>("EmployeeTable">
where emp.Title=="Attorney" && emp.Department == "Legal";

One issue that most people run into very quickly is that Azure’s Table service supports only a subset of LINQ and the features supported by ADO.NET Data Services. If you find an exception being thrown on query execution, check your query to ensure that you don’t have an unsupported clause.

Tables Table 1 and Table 2 document what query operations and comparison operators are supported. If it’s not in these tables, it’s not supported.

Table 1. Supported query operators
LINQ operatorDetails
FromSupported
WhereSupported
TakeSupported, but with values less than or equal to 1,000

Table 2. Supported comparison operators
Comparison operatorProperty types supported
EqualAll
GreaterThanAll
GreaterThanOrEqualAll
LessThanAll
LessThanOrEqualAll
NotEqualAll
AndBool
AndAlsoBool
NotBool
OrBool

Also, you typically cannot call any method on objects in your query. For example, calling a ToLower on an object in a query will fail.


Note:

Though these primitives look limited, you can combine them to do some interesting things.

Other -----------------
- Programming Windows Azure : Table Operations - Creating Entities
- Programming Windows Azure : Table Operations - Creating Tables
- iPad Development : Document Management (part 2)
- iPad Development : Document Management (part 1)
- iPad Development : The Split View Concept
- jQuery 1.3 : Developing plugins - Adding new shortcut methods
- jQuery 1.3 : Developing plugins - DOM traversal methods
- Using Cloud Services : Exploring Online Planning and Task Management
- Using Cloud Services : Exploring Online Scheduling Applications
- Using Cloud Services : Exploring Online Calendar Applications
- SOA with .NET and Windows Azure : Service Contracts with WCF (part 3)
- SOA with .NET and Windows Azure : Service Contracts with WCF (part 2)
- SOA with .NET and Windows Azure : Service Contracts with WCF (part 1)
- Cloud Security and Privacy : Data Security and Storage
- iPad SDK : Working with Documents - Desktop Synchronization
- Required Project Images for iPad Apps
- iPhone SDK : GameKit Voice Chat
- iPhone SDK : Creating Basic GameKit Services (part 2) : Sending and Receiving Data
- iPhone SDK : Creating Basic GameKit Services (part 1)
- iPad : Navigating with Maps
 
 
Most View
- What's New in SharePoint 2013 (part 2) - THE APP MODEL
- Exchange Server 2010 : Achieving High Availability
- Windows 7: Managing Wireless Network Connections (part 3) - Reordering Wireless Connections
- Windows Vista : Installing Windows Deployment Services (part 1) - Satisfying prerequisites, Setting up Windows Deployment Services for Server 2003
- SQL Server 2012 : T-SQL Enhancements - The MERGE Statement (part 1)
- Unit Testing in Visual Studio 2010 (part 1) - Creating unit tests
- Windows Phone 7 : Changing Map Views
- Windows 7 Customization : Stopping Delete Confirmations
- Search Engine Basics : Country-Specific Search Engines
- jQuery 1.3 : Working with numeric form data (part 7) - Deleting items
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS