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:
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.
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.