Lost In Tangent

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?

1. expand
2. orderby
3. skip
4. top
5. 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:

1. eq - Equal
2. ne - Not equal
3. gt - Greater than
4. gteq - Greater than or equal to
5. lt - Less than
6. lteq - Less than or equal to
7. and - Logical and
8. or - Logical or
9. not - Logical negation

The arithmetic operators are:

1. add - Addition
2. sub - Subtraction
3. mul - Multiplication
4. div - Division
5. 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:

1. String
2. Date
3. Math
4. 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.

12/30/2007 - Added clarification about the identifier criteria when using an EDM data source

Welcome to the first installment of the multi-part article series on ADO.NET Data Services. Throughout the entire series we are going to follow the story of Phillip, who runs a cell phone resale company, and is looking to ramp up his business. Phillip has an extensive database of cell phones, carriers, and manufacturers. Lots of time and effort has been put into the population and maintenance of his precious data. One day he realizes that all that time he put into his database should be making him more money, so he decides to provide access to his data store to other companies looking to build a web presence but don’t have the resources Phillip has.

“I could write ASMX web services that provide access to my data”, Phillip says, “but I’d really like to expose my data RESTfully in as few lines of code as possible. I could use WCF. That makes creating RESTful services really easy! But once I’ve got the service plumbing in place I still would have to write all the data retreival, paging, filtering, and sorting code myself, which seems like it could get really redundant and painful as the need for more services grows. As much as I love web services, it just seems like there should be a special kind of service that is tailored specifically for data-driven scenarios and would provide me all the data manipulation functionality I needed out of the box, so that I could focus purely on my business logic. Now that I think about it”, said Phillip, “Microsoft developed a new technology they’re calling ADO.NET Data Services that is built on top of WCF and is supposed to address this exact need! I’m going to give that a try!”

So now that Phillip has made up his mind to use ADO.NET Data Services to create his arsenal of cell phone services, we’re going to peer in as he chips away at implementing this new feature into his application. The remainder of the article will be written from Phillip’s perspective (who knows very little about data services) not mine.

[Fog covers the stage]

[We now see Phillip sitting at his computer table]

So let’s see…I’ve already got my database setup the way I want…

And I’ve already created a LINQ To SQL model that I’ve been using in my application…

It would be really great if I could somehow re-use what I already have. Let me try to add a new ADO.NET Data Service to my project and see what kind of options it gives me.

I see that it’s telling me to do two things:

1. Specify what my service’s data source is
2. Configure access rights for my data

What do they mean by data source? I remember hearing that the data service would work with any implementation of IQueryable<T>, which certainly seems flexible enough. In fact, now that I think of it, the Table<T> class (which the DataContext wraps each entity in a LINQ To SQL model) implements IQueryable<T>. I also remember hearing that the ObjectQuery<T> class (which the ObjectContext wraps each entity in an Entity Data Model) implements IQueryable<T> as well, which means that my data service would be able to use the Entity Framework if I were to migrate my model down the road.

It would be really cool if I could just specify my data context as the data source and it be smart enough to pick up on the fact that it contains properties of type Table<T> which are of type IQueryable<T>. Let’s give it a try…

Hit F5 and see what happens:

Wow, I can’t believe that actually worked. Oddly though I don’t see any entities. Oh wait, I almost forgot item #2 from the above TODO list. ADO.NET Data Services are locked down by default, and only expose the data you tell it to, with the access rights you want. Of all the entities in my data model, the only one I want to expose is CellPhone, and I want it to be read-only.

Where am I going to put this access code? According to the generated comments, the static InitializeService method is called when the service is being initialized (go figure), which seems like a perfect place to add policy configuration code. Since Visual Studio generated the InitializeService method for me all I should have to do is add a single line of code within it to setup the access rule I want. Let’s give it a try…

I could have put “*” in the first parameter if I wanted to expose the service’s entire underlying data source, but I only want to grant access to the CellPhone entity. Note also that “CellPhones” is the name of the IQueryable<T> exposed property on my data source (the data context), not the name of the actual entity class. The ResouceContainerRights enumeration has a slew of options, but I’m comfortable with AllRead, which allows all cell phones to be queried, as well as individual ones. I hit F5 again and I get the same results. Now what am I doing wrong?

I do a little reflecting into the ADO.NET Data Services DLL and notice a requirement placed on each object exposed by your data source: each object has to have a property that represents its identifier, that way it can be queried individually and uniquely. On top of that, the identifier property has to match at least one of the following criteria:

1. Be named “ID”
2. Be named “[EntityName]ID”
3. Be marked with the DataWebKey attribute

Well that explains what my problem here is. Not only have I named my identifiers “Id” (note the lowercase “d”) instead of “ID”, but in the case of the CellPhone entity, its identifying property is called “ModelName”, which isn’t satisfying any of the above criteria. I don’t want to rename my property to “ID” or “CellPhoneID” and lose the domain-meaning of “ModelName”, so I’m just going to go with option #3 and apply the DataWebKey attribute to the ModelName property of my CellPhone entity class.

Let’s hit F5 and try again:

Beautiful! We’ve now got a service that is exposing just my CellPhone entity from my LINQ To SQL model, and giving it read-only access. Because I was able to re-use my existing model, the lines of code required to get my service running amounted to only a couple. I had to annotate my CellPhone entity’s identifier property, but that only took 12 characters to type, and it makes sense why the data service needs it. If I wanted to migrate my LINQ To SQL model to use an Entity Data Model (EDM) or even a custom implementation of IQueryable<T>, my service would be able to take full advantage of it regardless, with minor tweaks. All-in-all it took about 5 minutes to stumble to this point. Imagine how quick I could write a data service now that I’m aware of the real-world “gotchas”?

One thing to point out is that the above identifier criteria doesn’t apply if your data service is using an EDM as its data source. This is because the EDM has its identifier property determined by the EntityKeyProperty property of the EdmScalarProperty attribute.

[EdmScalarProperty(EntityKeyProperty=true, IsNullable=false)]

This means that if you’re using an EDM with your data service then your identifier properties can be called whatever you want and they don’t need to be salted with the DataWebKey attribute. If you are using LINQ To SQL or a custom IQueryable<T> implementation, then you will have to abide by the identifier criteria.

So now that this service is up and running, how do I actually get to my cell phone data? How can I request the data for a specific cell phone? How can I query for all cell phones that have Bluetooth capabilities?

We’ll cover all of this and more in part 2 of this series on service addressing.