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
GET /ContactTable()?$filter=Name%20eq%20'Steve%20Jobs' HTTP/1.1


<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<feed xml:base=""
<title type="text">ContactTable</title>
<link rel="self" title="ContactTable" href="ContactTable" />
<entry m:etag="W/&quot;datetime'2009-04-21T06%3A38%3A28.242Z'&quot;">
<title type="text"></title>
<name />
<link rel="edit" title="ContactTable"
RowKey='')" />
<category term="sriramk.ContactTable"
scheme="" />
<content type="application/xml">
<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>

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 =
var svc = new TestDataServiceContext(account.TableEndpoint.ToString(),

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

foreach(Contact c in query)

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
TakeSupported, but with values less than or equal to 1,000

Table 2. Supported comparison operators
Comparison operatorProperty types supported

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.


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
- First look: Apple Watch

- 10 Amazing Tools You Should Be Using with Dropbox
- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 1)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 2)

- How to create your first Swimlane Diagram or Cross-Functional Flowchart Diagram by using Microsoft Visio 2010 (Part 3)
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Most View
- Exchange 2007: How Do I Modify a Database Size Limit?
- Windows 7 : Enhancing Your Browsing Security (part 1) - Blocking Pop-Up Windows
- Enable the Global Audit Policy by Using the Command Line
- Windows Phone 7 : Updating Your Phone Software
- SOA with .NET and Windows Azure : WCF Discovery (part 1) - Discovery Modes
- Microsoft ASP.NET 3.5 : WCF Services for ASP.NET AJAX Applications
- Securing Windows 7 : Thwarting Snoops and Crackers (part 2) - Locking Your Computer Manually, Automatically
- Windows 7 : Accessing a Shared Printer
- SQL Server 2008 : Data Encryption - SQL Server Key Management
- Windows 7 : Firing Up the Registry Editor