Do you remember the day when you first started learning web development and you figured out what the stuff after the “?” was in the URL? And then you found out that was called a query string. Can you remember your excitement? Maybe you uttered the words “I’m going to use query strings forever!” Well, you probably didn’t say that exactly, but you definitely recognized the value of it.
Some of you might have been thinking while reading this article series “You know I’m loving all these RESTful service URLs, but I gotta say I’m really jonesin for a query string.” Well, your cravings end here. This article will introduce the notion of query parameters to data services, and explain what they can be used for.
ADO.NET Data Services take parameters in the form of a query string just like normal web pages. The only difference is that there is a set list of parameters you can specify, that following a specific convention: $[parametername]=expression. So if your query was /CellPhones and you were to apply a parameter to it, it would look like: /CellPhones?$[paramatername]=expression. Nothing new is going on here, you basically just have to note that data service parameters are prefixed by a dollar sign, otherwise it’s just a standard query string. I say “expression” instead of “value”, because as you’ll see, the parameters can take sophisticated expressions, complete with operators and functions.
So what parameters does the data service accept?
- expand
- orderby
- skip
- top
- filter
Expand
Remember from the last post, when we queried a specific cell phone (the iPhone)? The returned content included two <link> elements that pointed at the phone’s carrier and manufacturer. That works great for situations where you’re not sure if you’re going to need that additional data (lazy loading). But what if you want both the cell phone and its carrier? This is where the expand parameter comes in. It allows you to specify the name of the relationship property that you want to automatically be retrieved (eager loading). Hence, we could use the following URL to satisfy our above requirement: /CellPhones(’iPhone’)?$expand=Carrier. Which would gives us the following (the returned content has been trimmed for brevity, but you’ll get the point):
Notice that we still have an empty <link> element for the Manufacturer relationship, but the Carrier <link> element has been expanded inline, as we requested. What if you wanted the Manufacturer as well? Luckily, the expand parameter can take a comma-delimited list of relationships to include, so /CellPhones(’iPhone’)?$expand=Carrier,Manufacturer would work just fine.
Orderby
By default the data service returns entity data ordered ascending by key (in our case, ModelName). In some cases this might be sufficient, but in a lot of situations it wouldn’t be. Suppose I wanted to retrieve all cell phones ordered by price. I could use the following URL: /CellPhones?$orderby=Price. If I wanted them ordered descending, I could simply use: /CellPhones?$orderby=Price desc. As you can imagine, there is also an “asc” keyword, but that is the default sort direction, so its use is limited. The orderby paramater takes a comma-delimited list just like expand, so you can build whatever order clause you need (i.e. /CellPhones?$orderby=Price desc,CarrierId,Zoom asc).
Skip
Pretty much all practical, real-world uses of data stores require paging, especially if the client application is on a different server. The skip parameter is 1/2 of the paging functionality that ADO.NET Data Services provides (the second half is discussed below). By applying a skip parameter, you are requesting that the data service skip a certain number of items, and then return all items after that. So if I were to request: /CellPhones?$skip=10, I would not receive the first 10 cell phones, but would get all remaining. The skip parameter determines which items to skip based on the sort expression for the data, so you want to make sure you also apply the orderby parameter if the default sort order isn’t what you want.
Top
If I only wanted to retrieve the top 10 most expensive phones, or the 5 lightest phones (or whatever), I could use the top paramater. You simply give it a number of items, and it will grab that many items off the top of the data source. Like skip, top also works in conjunction with the orderby, so you can imagine how you could use top to also simulate a bottom parameter. For instance, the following URL would retrieve the 10 most expensive phones: /CellPhones?$orderby=Price desc&top=10. This URL would retrieve the 5 lightest phones: /CellPhones?$orderby=Weight&top=5. I could then apply the skip paramater to begin paging: /CellPhones?$orderby=Weight&skip=5&top=5. When used together, the skip, top, and orderby parameters enable you to handle all your paging and reporting needs beautifully.
Filter
Now that we know how to retrieve an object’s entire graph, order, and page it, we start to realize the need for filtering some of this data. If we were looking to only retrieve cell phones for a specific manufacturer, we certainly wouldn’t want to have to pull them all down and do the filtering in our client code.
This is where the filter parameter comes in. It allows you to filter (big surprise) the entity data based on pretty much whatever criteria you want. You can even traverse relationships to filter an entity by a related entity’s properties. Remember when I mentioned that query parameters take expressions not values? That statement is most true for the filter parameter, which has a slew of acceptable operators and functions to handle your filter critieria.
The filter parameter has two types of operators: logical, and arithmetic. The logical operators are:
- eq - Equal
- ne - Not equal
- gt - Greater than
- gteq - Greater than or equal to
- lt - Less than
- lteq - Less than or equal to
- and - Logical and
- or - Logical or
- not - Logical negation
The arithmetic operators are:
- add - Addition
- sub - Subtraction
- mul - Multiplication
- div - Division
- mod - Modulus
The filter parameter also allows the use of parentheses to group expressions. I think the operators are pretty self-explanatory, and you can imagine how they allow you to build pretty complex expressions. For instance, suppose I wanted to get all phones that weight more than 3 ounces, have Bluetooth, have a camera with at least 2 megapixels, but don’t have email, and would be affordable if I had $500 to spend on a phone. I could do the following: /CellPhones?$filter=Weight gt 3 and Bluetooth eq true and Megapixels gteq 2 and Email eq false and 500 sub Price gteq 0. You get the point. The above 14 operators can be combined to do whatever filtering you’ll ever need.
Aside from the list of operators, the filter parameter also has 4 categories of functions:
- String
- Date
- Math
- Type
Each category is comprised of functions that make filtering on data of the respective type possible. For the sake of time I’m not going to go into every single function (there are over 30!), but you have things like: substring, toupper, indexof, round, ceiling, cast, etc. I might do a mini sub post just on the filter functions sometime in the future.
So now that we’ve mastered the basic form of querying ADO.NET Data Services, namely through a RESTful URL, how can we abstract this concept and work with a data service in a more concise fashion? The next article will focus on creating clients that consume data services.
