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.

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 whose Age 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 SOAP

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