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:
   
   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:
   
   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.
   
   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:
    
   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:

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:

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.