Saturday, August 30, 2008

ADO.NET Data Services (Project "Astoria") Query String Options and Expression Syntax

I'm stealing this directly from the ADO.NET Data Services documentation here:

http://msdn.microsoft.com/en-us/data/bb931106.aspx

However, it's all in a Word document (??) right now, so I thought I'd repost to something that might be a bit easier to find.

Structure of Web Data Services URLs

The basic format for URLs is:

http://host/vdir/<service>/<EntitySet>[(<Key>)[/<NavigationProperty>[(<Key>)/...]]]

Note: in the syntax above [ ] imply optional components

The 4 main elements of the URL syntax are:

1. The data service URL. The data service URL is the first part of the URL that points to the data service .svc/.rse file. For example, http://host/myapp/northwind.svc. The examples below assume that the URLs start with that prefix for brevity.

2. The entity-set name (optional). If you include an entity-set name, then all the entities in that entity-set are returned. For example, /Customers would return all of the customers in the Northwind data service. The system allows for an optional filter predicate contained in parenthesis to subset the response to a single entity. For single-key entities, you can simply indicate the key value, and the resulting URL will point to that entity specifically. For example, if there is a customer entity with a key ‘ALFKI’, its URL would be /Customers(‘ALFKI’). Additional expression-based filtering on a set is enabled by using query string parameters, which are described later in this document

3. A navigation property (optional). A navigation property can be placed after the entity-set name (separated by a “/”), indicating that you want to traverse the relationship being pointed to. For example, /Customers(‘ALFKI’)/Orders would return the sales orders of the customer with the primary key ‘ALFKI’. As noted above, a filter can also be applied to the navigation property using query string operators (described later in this document) to return only a subset of the related entities. For example, /Customers(‘ALFKI’)/Orders?$filter=OrderDate gt '1998-1-1' returns all the orders posted after Jan 1st, 1998, for the customer with a key ‘ALFKI’. Since the result of traversing a relationship through a navigation property is another set of entities, you can continue to add navigation properties to the URL to navigate through the relationship graph specified in the data service schema. For example, /Customers(‘ALFKI’)/Orders(1)/Employees returns the employees that created sales order 1 for the customer with a key of ‘ALFKI’.

Query string options

While the URL format allows for filtering and traversing through the graph of entities in the store, it does not have constructs to control the output. For that, a number of optional query string parameters are supported by the Astoria data services. Table 2 below lists all of the query options along with their description and some usage examples.

expand

The ‘expand’ option allows you to embed one or more sets of related entities in the results. For example, if you want to display a customer and its sales orders, you could execute two requests, one for /Customers(‘ALFKI’) and one for /Customers(‘ALFKI’)/Orders. The ‘expand’ option returns the related entities in-line with the response of the parent URL request.

You may specify multiple navigation properties to expand by separating them with commas, and you may traverse more than one relationship by using a dot to jump to the next navigation property.

--a customer with related sales orders

/Customers[ALFKI]?$expand=Orders

--a customer with related sales orders and employee information related to those orders

/Customers[ALFKI]?$expand=Orders.Employees

--Orders with related employees information and related shipper information

/Orders[10248]?$expand=Employees,Shippers

orderby

Sort the results by the criteria given in this value. Multiple properties can be indicated by separating them with a comma. The sort order can be controlled by using the “asc” (default) and “desc” modifiers.

/Customers?$orderby=City

/Customers?$orderby=City desc

/Customers?$orderby=City desc,CompanyName

skip

Skip the number of rows given in this parameter when returning results. This is useful in combination with “top” to implement paging (e.g. if using 10-entity pages, saying $skip=30&top=$10 would return the fourth page). Note that skip only makes sense on sorted sets; if an orderby option is included, ‘skip’ will skip entities in the order given by that option. If no orderby option is given, ‘skip’ will sort the entities by primary key and then perform the skip operation.

--return all customers except the first 10

/Customers?$skip=10

--return the 4th page, in 10-row pages

/Customers?$skip=30&$top=10

top

Restrict the maximum number of entities to be returned. This option is useful both by itself and in combination with skip, where it can be used to implement paging as discussed in the description of ‘skip’.

/Customers?$top=5

--top 5 sales orders with the highest TotalDue

/Orders?$orderby=TotalDue&$top=5

filter

Restrict the entities returned from a query by applying the expression specified in this operator to the entity set identified by the last segment of the URI path

/Customers?$filter=City eq ‘London’

-- all customers in London

/Customers?$filter='Wayne, John' eq insert(ContactName,

length(lastname), ',')

-- Match all Customers with the value of the property  ‘fullname’ equal to ‘Wayne, John’

Table 1. Query string options

Expression Syntax

The simple expression language that is used in filter operators supports references to columns and literals. The literal values can be strings enclosed in single quotes , numbers and boolean values (true or false). If a date/time value needs to be specified, it can be written as a string (in quotes) and the system will attempt to convert it to a date/time if the other operand is a date/time type.

The operators in the expression language use abbreviations of the names instead of symbols to reduce the amount of escaping necessary in the URL. The abbreviations are listed in the table below.

Logical Operators

eq

Equal

/Customers?filter=City eq 'London'

ne

Not equal

/Customers?filter=City ne 'London'

gt

Greater than

/Product?$filter=UnitPrice gt 20

gteq

Greater than or equal

/Orders?$filter=Freight gteq 800

lt

Less than

/Orders?$filter=Freight lt 1

lteq

Less than or equal

/Product?$filter=UnitPrice lteq 20

and

Logical and

/Product?filter=UnitPrice lteq 20 and UnitPrice gt 10

or

Logical or

/Product?filter=UnitPrice lteq 20 or UnitPrice gt 10

not

Logical negation

/Orders?$ ?$filter=not endswith(ShipPostalCode,'100')

Arithmetic Operators

add

Addition

/Product?filter=UnitPrice add 5 gt 10

sub

Subtraction

/Product?filter=UnitPrice sub 5 gt 10

mul

Multiplication

/Orders?$filter=Freight mul 800 gt 2000

div

Division

/Orders?$filter=Freight div 10 eq 4

mod

Modulo

/Orders?$filter=Freight mod 10 eq 0

Grouping Operators

( )

Precedence grouping

/Product?filter=(UnitPrice sub 5) gt 10

Table 2. Operators for filter expressions

In addition to the operators described above, a set of functions are also defined for use with the filter query string operator. The following tables list the available functions. This CTP does not support Aggregate functions (sum, min, max, avg, etc) as they would change the meaning of the ‘/’ operator to allow traversal through sets. For example, /Customers?$filter=average(Orders/Amount) gt 50.00 is not supported. Additionally, ISNULL or COALESCE operators are not defined. There is a null literal which can be used for comparison following CLR semantics.

String Functions

bool contains(string p0, string p1)

bool endswith(string p0, string p1)

bool startswith(string p0, string p1)

int length(string p0)

int indexof(string arg)

string insert(string p0, int pos, string p1)

string remove(string p0, int pos)

string remove(string p0, int pos, int length)

string replace(string p0, string find, string replace)

string substring(string p0, int pos)

string substring(string p0, int pos, int length)

string tolower(string p0)

string toupper(string p0)

string trim(string p0)

string concat(string p0, string p1)

Date Functions

int day(DateTime p0)

int hour(DateTime p0)

int minute(DateTime p0)

int month(DateTime p0)

int second(DateTime p0)

int year(DateTime p0)

Math Functions

double round(double p0)

decimal round(decimal p0)

double floor(double p0)

decimal floor(decimal p0)

double ceiling(double p0)

decimal ceiling(decimal p0)

Type Functions

bool IsOf(type p0)

bool IsOf(expression p0, type p1)

<p0> Cast(type p0)

<p1> Cast(expression p0, type p1)

Examples

  • /Orders?$filter=ID eq 1From
    • From all the Orders in the data store, return only the Orders with the ‘ID’ property equal to 201
  • /Customers?$filter='Wayne, John' eq insert(fullname, length(lastname), ',')
    • Match all Customers with the value of the property ‘fullname’ equal to ‘Wayne, John’
  • /Customer$filter=isof(‘SpecialCustomer’)
    • Match all customers that are of type SpecialCustomer. Entity sets support inheritance and thus customer entities in the entity set may be of different types within a type hierarchy

2 comments:

Anonymous said...

Can we do a skip and take on a expand query?

/Customers[ALFKI]?$expand=Orders$skip=30&$top=10

Jim Fiorato said...

You should be able to, yes.