Pages

Sunday, December 2, 2012

SharePoint 2013: Use CAML into REST request

You might wonder how to use CAML query in your SharePoint 2013 REST endpoints. Basically you can’t pass CAML into REST request (at least till date, there’s so documented approach published). However, REST by itself provides the querying feature which somehow covers almost all CAML querying features. I’ll try to explain today how you can convert your CAML into REST request.

Generate REST Request Url

First of all let’s play with a tool called LINQPad that will be used for REST request generation later from CAML.Let’s describe the process step by step.

  1. Download LINQPad: The tool that I’m going to use for generating REST request url can be downloaded from LINQPad site. So please download the tool and run it.
  2. Add Connection Provider: Once you run the tool click ‘Add Connection’ and then select ‘WCF Data Services (OData)’ data context provider as shown below:
    image
    Figure 1: Add WCF Data Connection
  3. Connect to SharePoint Server: In the connection page, select the url as http://server/_vti_bin/ListData.svc as shown below:
    image
    Figure 2: ListData WCF Connection
  4. Built-in Query Option: Use built-in template for querying as shown below. Right click on your list/library and use any built-in template you want.
    image
    Figure 3: Use built-in template for generating query
  5. Generate Query: Once you finish the query, execute it and click the ‘Request Log’ button to see the REST request generated as shown below:
     image
    Figure 4: C# query and REST request

For details REST operator you can use for SharePoint 2013 REST endpoint can be found at MSDN link under “Table 3. OData query operators”. For licensed version, LINQPad provides intellisense which really exciting feature.

 

Examples of C# query and corresponding REST URL

I’ve provided few basic examples of using the LINQPad tool to generate queries. For this example, let’s consider we have a product list with the following fields:

image

Figure 5: Sample product list

The following examples explains the C# query and it’s corresponding REST Url:

  • Select all products whose name contains ‘sharepoint’
    C# Qeury
    Product.Where (p => p.ProductName.ToLower().IndexOf("sharepoint")!=-1)
    ListData Url http://server/_vti_bin/ListData.svc/Product()?$filter=indexof(tolower(ProductName),'sharepoint') ne –1
    REST URL http://server/_api/web/lists/getbytitle(‘Product’)?$filter=indexof(tolower(ProductName),'sharepoint') ne –1
    So the summary is that the filter is “$filter=indexof(tolower(ProductName),'sharepoint') ne –1
  • Select all products created on 10-Oct-2012
    C# Qeury
    Product.Where (p => p.Created.Value.Day==10 & p.Created.Value.Month==10 & p.Created.Value.Year==2012) 
    ListData Url http://server/_vti_bin/ListData.svc/Product()?$filter=((day(Created) eq 10) and (month(Created) eq 10)) and (year(Created) eq 2012)
    REST URL http://server/_api/web/lists/getbytitle(‘Product’)?$filter=((day(Created) eq 10) and (month(Created) eq 10)) and (year(Created) eq 2012)
      So the filter is “$filter=((day(Created) eq 10) and (month(Created) eq 10)) and (year(Created) eq 2012)

    As shown above two examples, you can find out more by yourself by using the LinqPad and the MSDN function references.

     

    Convert CAML to REST Url

    Now let’s consider you have an CAML XML as shown below which returns all items from a list whose title contains ‘sharepoint’ and takes first 10 items.

    <Where> 
       <Contains> 
          <FieldRef Name='Title' /> 
          <Value Type='Text'>sharepoint</Value> 
       </Contains> 
    </Where> 
    <QueryOptions> 
       <RowLimit>10</RowLimit> 
    </QueryOptions>

    Now using LINQPad, we can convert the CAML to REST url as described below:

    image

    So the final REST Url will be http://server/_vti_bin/ListData.svc/Product()?$filter=indexof(tolower(ProductName),'sharepoint') ne -1&$top=10

     

    Conclusion

    Using a combination of LINQPad, OData query operators at MSDN link and CAML query at your hand, you can generate proper REST URL to be used. So if you have a CAML query at your hand, with a little time investment you can generate corresponding REST URL with filters and select parameters.