Querying SQL Data Services
[This document supports a preliminary release of a software product that may be changed substantially prior to final commercial release. This document is provided for informational purposes only.]
The general syntax of the query language in Microsoft® SQL Data Services (SDS) is:
from e in entities [where condition] [orderby property 1 [,property 2, …]] select e
The query iterates over the set of flexible entities in the specified scope and returns items satisfying the condition. By default the query results are sorted by entity Id
metadata property. The optional orderby
clause can be used to sort the result by any other property. In the where
clause, you can specify a simple or a compound condition (multiple conditions combined by using logical operators). The following table lists the operators supported in this release.
Comparison Operators |
|
Logical operators |
|
These operators follow the same order of precedence as in SQL. Parenthesis can be added to change the order of precedence in a compound condition. Parentheses work same as they do in SQL.
Join queries are supported and they are like equijoin queries in relational databases where you join same table with itself. For more information see, Join Queries.
The query expression can be compared to the C# foreach
or LINQ (Language Integrated Query) constructs:
C#
foreach
construct.foreach entity e in entities { if (e satisfies a condition) { yield return e; } }
C# LINQ constructs.
IEnumerable<FlexibleEntities> results = from e in scope.Entities where condition select e;
Syntax difference when specifying metadata vs. flexible properties
There is a difference when referring to metadata prosperities (Id, Version, and Kind) and flexible properties in a query.
The '.' notation is used when specifying metadata properties in queries. The following query retrieves an entity with specific
Id
metadata property value.from e in entities where e.Id == "someId" select e
However, the flexible properties are stored in the
Properties
collection of the entity. Therefore, when querying over these properties indexer syntax must be used. The following query retrieves all entities whoseAge
flexible property value is 32:from e in entities where e["Age"] == 32 select e
Specifying Query Scope
According to the three-level containment model described in SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities), an authority stores containers, and a container stores entities. When querying the service you must choose the scope over which you wish to query.
- When querying for your authorities the service must be in scope.
- When querying for containers within an authority, the specific authority must be in scope.
- When querying for entities within a container, the specific container must be in scope.
How a scope is set depends on whether the query is executed within an application using the SOAP interface, or the REST interface or the query is specified directly in the browser. For details, see:
Specifying Queries Using REST or the Browser Specifying Queries Using REST or the Browser
Handling Large Query Results
Often queries return large results. By default, the results are limited to a maximum of 500 entities. However, note that:
- The Take() function can be used to change the default. For more information, see Using the Take() function.
- The SSDS paging support allows you to retrieve larger result in chunks called pages. For more information, see Paging Support to Handle Large Query Results.
Querying non-blob and blob entities
GET request returns blob content. Only one blob content can be retrieved at a time. When issuing queries over containers that contain blobs, the queries return only the metadata for blob entities. Note that only the blob entities have Content
metadata property. This can be used to distinguish between the blob and non-blob entities.
For the non-blob entities application/x-ssds+xml
is the SSDS content type. When querying in the browser, it needs to be aware of this content type. For Internet Explorer this can be done by adding a new key in the registry. For more information, see Guidelines and Limitations.
See Also
Concepts
SDS Data Model Overview (Authorities, Containers, Entities and Flexible Entities)
Examples of Using SOAP and REST Interfaces with the SQL Data Services
Paging Support to Handle Large Query Results