Querying SDS - Sorting

This is the fifth part of a series of posts about SQL Data Services (SDS). Last time I showed the basics of how to query SDS, now I will show some more query options. Let’s start with the basic query from last time that returns all the entities in a container

from e in entities select e

By default the entities will be sorted by the Id property. We can sort by other properties like this

from e in entities orderby e["DateDue"] select e

Note that just like in the Where clause flexible properties in the orderby clause use the syntax like e[“DateDue”] but metadata properties use the e.Verison syntax. By default the sort is done in ascending order. You can sort descending like this:

from e in entities orderby e["DateDue"] descending select e

You can also sort by multiple properties

from e in entities orderby e["Completed"],e["DateDue"] select e

As I have mentioned before SDS entities are schema-less so there could be entities in a container that don’t have one of the properties you are sorting on. In these cases the entities will still appear in the output. I have not seen any official documentation that explains how these are handled but it appear they are treated as having a null value and are sorted to the top of the list.

Querying SDS

In my last post I showed how to put data into an SDS database, now we will look at how to get it out. As I mentioned at the end of the last post every entity in SDS has a unique address that can be used to retrieve it directly. For example if I put in this address, https://testsds.data.database.windows.net/v1/Tasks/T1000, and click Get it will retrieve the entity we created last time:

<Entity xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Id>T1000</s:Id><br /> <s:Version>50976472</s:Version><br /> <Message xsi:type="x:string">Finish blog entry on SDS</Message><br /> <DateAdded xsi:type="x:dateTime">2009-02-01T00:00:00</DateAdded><br /> <DateDue xsi:type="x:dateTime">2009-02-07T00:00:00</DateDue><br /> <Completed xsi:type="x:boolean">false</Completed><br /></Entity><br />

This is good for retrieving a single entity, but if we want to find a group of entities based on some parameters we need to use queries. First let’s create two more entities so we have something to query. Change the address so it points back at the Tasks container, for example https://testsds.data.database.windows.net/v1/Tasks/. Enter each of these entities and click Post.

<Entity xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Id>T1001</s:Id><br /> <Message xsi:type="x:string">Take out the trash</Message><br /> <DateAdded xsi:type="x:dateTime">2009-02-02T00:00:00</DateAdded><br /> <DateDue xsi:type="x:dateTime">2009-02-08T00:00:00</DateDue><br /> <Completed xsi:type="x:boolean">true</Completed><br /></Entity><br /><br /><Entity xmlns:s="http://schemas.microsoft.com/sitka/2008/03/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="http://www.w3.org/2001/XMLSchema"><br /> <s:Id>T1002</s:Id><br /> <Message xsi:type="x:string">Buy Valentines day gift</Message><br /> <DateAdded xsi:type="x:dateTime">2009-02-03T00:00:00</DateAdded><br /> <DateDue xsi:type="x:dateTime">2009-02-13T00:00:00</DateDue><br /> <Completed xsi:type="x:boolean">false</Completed><br /></Entity><br />

After creating the entities change the address back to the container once again.

Let’s start with the simplest query, enter this in the query box and click Query:

from e in entities select e

This will return an EntitySet with all three entities in our Tasks container. This query has no conditions so it will return all the entities in a container.

Earlier I showed you how to retrieve a single entity by doing a Get on its address, but you can also retrieve and entity using a query like this:

from e in entities where e.Id=="T1001" select e

Here we have added one condition, e.Id==”T1001” which simply means to retrieve all entities where the Id property is T1001. Since each Id is unique this will return a single entity.

What if we wanted to query for all tasks that have not been completed:

from e in entities where e["Completed"]==false select e

You will notice a difference in the syntax for this condition. Instead of e.Completed, we used e[“Completed”] instead. The e.Id syntax we used last time is only used for metadata properties. When you query flexible properties you have to use the e[“Completed”] syntax.

What if we made a mistake in the query and did this instead:

from e in entities where e["Complete"]==false select e

In a traditional database this would throw and exception since the field Complete doesn’t exist in the database, but since SDS is schema less this will not throw an error, it just won’t return any entities.

We are not limited to just specifying one condition, here we query for all incomplete tasks that are due after 2/12/2009:

from e in entities where e["Completed"]==false && e["DateDue"] >DateTime("2009-02-12") select e

We use the logical operator “&&” to specify one condition and another. Also notice that when we compare to the literal date value we must use the DateTime(“”) function, if you just compared to “2009-02-12” it would not work.

That’s the basics of querying SDS. I will talk about some other query topics in my next post.