WCF Data Services Cheat Sheet
I was working with some SharePoint REST queries and couldn’t find a good list of the operations supported by the REST API. Namely, I was trying to figure out if it supported a “contains” operation (it does using indexof).
I found a very nicely put together set of tables which shows the LINQ queries, the corresponding URL, and the SQL produced by the query.
This is the result of my tiny research about translating LINQ to REST into oData URI Conventions and finally into T-SQL. Event though I knew what I needed on the T-SQL side, I was not sure which LINQ statement would create the respective SQL query. So, as an experimental test bed I created a little console application and used Netflix oData Service to see how LINQ to REST gets translated to URIs
Very useful indeed.
On a related note, for those that are working with the REST APIs for SharePoint, I’ve found that you can actually reduce your network traffic when using $expand by taking only the fields that you’re interested in for the expanded item as well. For example:
|
1 |
http://[site]/_vti_bin/listadata.svc/Tasks?$expand=AssignedTo&$select=AssignedTo/Name,AssignedTo/Account,Id,Title |
The downside is that this will barf out invalid JSON if you have a null value for the expanded field (in this case, if the task is not assigned to any user). Here’s a sample output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
{ "d":{ "results":[ { "__metadata":{ "uri":"http://project.dev.com/PWA/ts1/_vti_bin/listdata.svc/Tasks(2)", "etag":"W/\"3\"", "type":"Microsoft.SharePoint.DataService.TasksItem" }, "Id":2, "ContentType":"Task", "Title":"Action 1", "Path":"/PWA/ts1/Lists/Tasks/Milestone 1", "PriorityValue":"(2) Normal", "StatusValue":"Not Started", "AssignedTo":{ "__metadata":{ "uri":"http://project.dev.com/PWA/ts1/_vti_bin/listdata.svc/UserInformationList(18)", "etag":"W/\"7\"", "type":"Microsoft.SharePoint.DataService.UserInformationListItem" }, "Name":"Charles Chen", "Account":"DEV\\charles" }, "StartDate":"\/Date(1310428800000)\/", "DueDate":"\/Date(1310601600000)\/" }, { "error":{ "code":"", "message":{ "lang":"en-US", "value":"An error occurred while processing this request." } } } |
You can see that the second item in the “results” array is where the task was not assigned to a user. The response is abruptly cut off with the JSON being completely broken at that point and in an invalid state. I would have expected that the result would just set “AssignedTo”:null
A bummer, but it doesn’t detract from my love for the REST APIs 😀
This is cool. The REST APIs for SharePoint 2010, especially when utilized with jQuery, really do provide an extremely robust and flexible data access framework for SharePoint. This type of information isn’t always easy to find. Good post!