Figuring our complex REST queries for SharePoint

Posted on 10/8/2011 @ 4:16 AM in #SharePoint by | Feedback | 2698 views

A little while ago, I showed the REST query for a relatively complex query. Some readers have emailed me about how to figure out further queries, and especially for complex insert/delete/update scenarios. Well it is quite easy to figure out almost any query for SharePoint REST API. Okay, this is not just about SharePoint – you can apply what you read here for any REST API interface supported by Microsoft, like WCF data services. But, sometimes when you have many columns, or complex update operations, or are working with weird providers, it is tough to figure out the specific HTTP request you need to craft, error free, using REST. Well fear not, there is hope.

As an example, what I did is, I created a SharePoint site at http://sp2010.winsmarts.internal/sampledata with 3 lists in it -

1. Artists (with one Column, Title)

2. Albums (with two columns, Title, and Artist (lookup to Artist.Title)

3. Songs (with 3 columns, Title, Album (lookup to Album.Title), and Artist (lookup to Album.Artist)

I then put in some data into my lists so I had some data to work with.

Then, I created a ConsoleApplication – remember to target .NET 4.0 or above.

  • In the console app, I added a service reference to http://sp2010.winsmarts.internal/sampledata/_vti_bin/listdata.svc
  • And then, I started writing some interesting code. Lets say, my first task was to figure out all songs done by an Artist, whose title contained the word “Michael”, and the Song title had the word “Crazy” in it, and the Album title had the word “Bad” in it. Well, here is the Linq Query for it -
  •    1:  string siteUrl = "http://sp2010.winsmarts.internal/sampledata/_vti_bin/listdata.svc";
       2:  SampleData.SampleDataDataContext context = new SampleData.SampleDataDataContext(new Uri(siteUrl));
       3:  context.Credentials = System.Net.CredentialCache.DefaultCredentials;
       4:   
       5:  var songs = (from song in context.Songs
       6:                  where song.Artist.Title.Contains("Michael")
       7:                  && song.Title.Contains("Crazy")
       8:                  && song.Album.Title.Contains("Bad")
       9:                  select song);
      10:  Console.WriteLine(songs.ToString());

    This goes ahead and writes out the query for you -

    ../Songs()?$filter=(substringof('Michael',Artist/Title) and substringof('Crazy',Title)) and substringof('Bad',Album/Title)

    Wow, I would have never guessed that :)

    This works for all sorts of “GET” requests, but what if I wanted to do an update into SharePoint, what do I do then? Well easy, see the code below,

       1:              string siteUrl = "http://sp2010.winsmarts.internal/sampledata/_vti_bin/listdata.svc";
       2:              SampleData.SampleDataDataContext context = new SampleData.SampleDataDataContext(new Uri(siteUrl));
       3:              context.Credentials = System.Net.CredentialCache.DefaultCredentials;
       4:   
       5:              var songs = (from song in context.Songs
       6:                              where song.Artist.Title.Contains("Michael")
       7:                              && song.Title.Contains("New")
       8:                              && song.Album.Title.Contains("Bad")
       9:                              select song);
      10:   
      11:              // Console.WriteLine(songs.ToString());
      12:   
      13:              var s = songs.First();
      14:              s.Title = "New Title";
      15:              context.UpdateObject(s);
      16:   
      17:              context.SendingRequest += (sender, e) =>
      18:              {      
      19:                  Console.WriteLine(e.Request.Method + " " + e.Request.RequestUri.ToString());
      20:              };
      21:   
      22:              context.SaveChanges();
      23:              Console.Read();

    This will give you the necessary output as shown below,

    MERGE http://sp2010.winsmarts.internal/sampledata/_vti_bin/listdata.svc/Songs(12)

    .. and you can also use this to find the JSON payload etc. Basically reverse engineer the crap out of this, and replay this in jQuery. WOOHOO!! Or just use data.js to make your life simpler :)

    Sound off but keep it civil:

    Older comments..


    On 10/8/2011 10:31:26 PM Jason Rendel said ..
    Sahil, this is good stuff. Much easier than walking through the OData docs. Thank you for sharing.


    On 10/13/2011 6:01:04 PM Peter Holpar said ..
    Thanks for sharing this technique. I usually use Fiddler to see what request was sent to the server and what the response was.


    On 10/24/2011 3:52:53 AM Mohamed AbdelGhani said ..
    Hi Mr. Sahil,


    I wrote the code in listing 5-26 "Code Required to Delete Using JavaScript and JSON"


    in Chapter#5 "Client Object Model, ADO.NET SERVICES AND CUSTOM SERVICES" and the delete button works fine but actually no deletion and by using the Fiddler i found this issue.

    Header:


    DELETE /raw/_vti_bin/listdata.svc/Artists(undefined) HTTP/1.1

    RAW:


    HTTP/1.1 400 Bad Request


    Cache-Control: private


    Content-Type: application/json


    Server: Microsoft-IIS/7.0


    SPRequestGuid: 285eded9-5e0c-4750-94df-887a478fc03b


    Set-Cookie: WSS_KeepSessionAuthenticated={53b6329d-9d60-482a-a6cf-e2f4a360bcbd}; path=/


    X-SharePointHealthScore: 1


    DataServiceVersion: 1.0;


    X-AspNet-Version: 2.0.50727


    X-Powered-By: ASP.NET


    MicrosoftSharePointTeamServices: 14.0.0.4762


    Date: Mon, 24 Oct 2011 07:49:54 GMT


    Content-Length: 114

    {


    "error": {


    "code": "", "message": {


    "lang": "en-US", "value": "Bad Request - Error in query syntax."


    }


    }


    }

    And i reviewed the code from the downloaded folder and from your book but same problem exist.


    Bye


    On 10/24/2011 11:13:26 PM Sahil Malik said ..
    Mohamed, change ID to Id in the JavaScript, it'll work


    On 10/26/2011 5:51:41 AM Mohamed AbdelGhani said ..
    Sahil, its working now.


    Thank you sir.


    On 1/11/2012 9:50:35 PM russell said ..
    Hey Sahil,


    Thanks for posting this. I'm trying to use listdata.svc in a console app to upload files to a doclib. I'm using vs2008 on a remote box to genreate the proxy for a sp2010 machine. When I generate my proxy, though, it doesn't have a setSaveStream mrthod on it. Any idea why the proxy generated would not have the setsavestream method?


    Thanks


    On 1/24/2012 10:24:28 AM Sagar S. Kadookkunnan said ..
    How can I specify the no:of rows to be received as the result from a SharePoint site?

    In one of my application I need to do a lazzy-loading of documents from SharePoint site. So what is the query/url that I can request. I know there is a mechanism to skip till some row.(http://<server_name>/_vti_bin/ListData.svc/<list_name>/?$skiptoken=300). Is there any mechanism to do the reverse. I need to get the first 300 rows only in the initial request. Could you please help me to find out a solution.

    Thank you in advance,


    Sagar S. Kadookkunnan


    On 2/8/2012 4:28:52 AM Marcin Robaszynski said ..
    OMG this is awesome :D Thx!