SHAREPOINT 2013, REST, ODATA, PART 1: READING/FETCHING INFORMATION (NO CODE)

Introduction

In this post I will give you a quick overview of the SharePoint 2013 REST/oData service; I will only use the browser (and Http GET calls) to illustrates several REST statements; in other posts, we will start writing code (.net and javascript)

Step1.The big picture.

In SharePoint 2013, Microsoft has extended the impact of CSOM which is often considered as the reference api instead of the good old server side api. In the past SharePoint developers started their SharePoint training path buy learning the server side api, but today with new the app model, learning the client side apis should be the first priority. CSOM can be used in remote .Net applications, but also in javascript : however the javascript CSOM can only be used within SharePoint pages : keeping the client side code as independent of SharePoint as possible might be very important for some customers and that’s where the new REST api is becoming very interesting; moreoever the REST api is more and more (but not fully) in line with the new industry standard that Microsoft promotes : oData v3.

Fetching list data is one of the many possibilities provided by the new REST/odata api, we can do much more with it, like updating/querying the following types of objects:

Site collections
Web site
Search,
Publishing
User profile

This is much much less than what CSOM provides:

User Profiles
Search
Taxonomy
Feeds
Publishing
Sharing
Workflow
E-Discovery
IRM
Analytics
BCS

CSOM and REST are based on the same web service, Client.svc (the ListData.svc web service is still there): the endpoints in the SharePoint 2013 REST service correspond to the types and members in the SharePoint client object models. By using HTTP requests, you can use these REST endpoints to perform typical CRUD (Create, Read, Update, and Delete) operations against SharePoint artifacts, such as lists and sites.

Client.svc can be invoqued directly or via an alias (_api instead of__vti_bin/client.svc): for instance here is the syntax to use if you want to manipulate :

Typically, endpoints that represent Read operations map to HTTP GET commands. Endpoints that represent update operations map to HTTP POST commands, and endpoints that represent update or insert operations map to HTTP PUT (or MERGE) commands.

The Rest api allows 2 encoding as required by the oData specification : JSON or ATOM (XML based feed data); we will using ATOM in the beginning (for clarity) but we will move to JSON in part 2 of the next post.

Microsoft is promoting an industry standard, oData; last version of oData is v3 and more details about it can be provided in www.odata.org. However, at the time of this writing the rest implementation of SharePoint 2013 is far from compatible with oData v3 : many functions are not implemented and basic stiff like $metadata , which is supposed to return an EDMX (Entity Data Model) document that contains a complete description of the feeds, types, properties, relationships exposed by the service in EDM (Entity Data Model).

Also, always keep in mind that REST/oData is based on Http, and therefore a REST expression is limited to 256 characters.

I don’t want to be negative, but you can do much more with CSOM, which remains my first option in any SharePoint 2013 project; in my opinion, the REST implementation of SharePoint 2013 is only interesting for CRUD operation from LAMP /IOS/Android platforms.

BCS & managed taxonomy data cannot be consumed from REST, we can use the CAML syntax in CSOM (join manipulation is more flexible), and we can batch commands in CSOM.

Step2.Installing the prerequisites.

Install the site template provided here, create a web site based on it and take a look at the generated lists: we have a list Suppliers, Products and Countries; a Product can be provided by a supplier, a supplier is from one country: this logic is provided by lookup field between the 3 lists.

Step3.Fetching list information

We can fetch list items in SharePoint by referencing a list via its Id or its name. Of course the id is not subject to changes, so we recommend the id; however do never forget that url have a limitation of 256 characters in http and this alone can force you to move to CSOM instead of staying with REST.

Now type following url in the browser (replace ‘litware’ with your web site name)

http://litware/_api/web/lists/getbytitle(‘Products’)

Check the returned information: here you only get information about the list itself, not its data; one of the information you will get is the list id:

So grab the id and, in the browser, type

http://litware/_api/Web/Lists(guid’9e7934bd-65ec-482c-8d68-b8decc431177′)

and you will get exactly the same information. However, for the sake of clarity we will stick to the list name.

Step4.Fetching list items

To fetch list items, just append /items() to the previous url :

Type

http://litware/_api/web/lists/getbytitle(‘Products’)/items()

and you get:

Where each <enty> is a list item (here a Product). Each item features an entry element (in our case a Product) where you will find a <m:properties> element where most column value are stored and associated with their type (an EntitydataModel type, like Edm.Double, Edm.Int32, etc… It looks like when the column type is a line of text, the type is not provided).Actually the REST models the data exposed through the data service using a model called the Entity Data Model (EDM), an Entity-Relationship derivative.The ADO.NET Entity Framework is used to expose it. This is an opportunity for third party tooling.

In figure, we can clearly find the product BMW 320 (column title) and its associated Price (30000) defined with the Edm.Double type.

However columns like Created By and Modified By are represented by <d:AuthorId> and <d:EditorId> and unfortunately the corresponding people cannot be retrieved. This is a first limitation to take into account with the REST api.

You will notice that the returned Xml fragment provides useful information regarding which url to use to reach each field, or list item, etc…and moreover the list guid is referenced almost everywhere as illustrated in the next picture : for instance the first item is referenced as

Web/Lists(guid’9e7934bd-65ec-482c-8d68-b8decc431177′)/Items(1)

But the html representation of the field can also be returned (this can be extrêmely usefull in html clients)

Web/Lists(guid’9e7934bd-65ec-482c-8d68-b8decc431177′)/Items(1)/FieldValuesAsHtml

Step 5.Retrieving specific column values

Use $select : for instance For instance, if we just want to get the Title and the price from the products list

http://litware/_api/web/lists/getbytitle(‘Products’)/items()/?$select=Title,Price

You will notice that even if oData is not case sensitive, the fields names are case sensitive.

Also, the column name you have to specify are internal names, not display name even if you rename them with the SharePoint GUI, your query will still work.

If you don’t specify $select, you will get all columns but there are a few exception like when you try to fetch resource intensive columns: ex effectivebasepermission column is not returned by default, but you will get it if you type

http://litware/_api/web/lists/getbytitle(‘Products’)/items()/?$select=Title,Price,effectivebasepermissions

Step 6.Filtering

To filter items, we have to use the $filter operator

For instance, to retrieve Products where Price is > 30000

type http://litware/_api/web/lists/getbytitle(‘Products’)/items/?$filter=Price gt 30000

Step 7. Joins

We can easily fetch data from 2 lists linked together by a lookup field; for instance each product has a supplier and there is a lookup from the Products list pointing to the Suppliers product.

If we type this:

http://contoserver:31782/_api/web/lists/getbytitle(‘Products’)/items()

we can notice in the next picture, that the supplier_id is returned :

For instance to retrieve the supplier title (from the Products list) of each product (defined in the Products lists) we have to provide the Supplier column name (in my case the column name is ‘Supplier_’) and use the $expand operator like this : (this was first described by Andrew Connel in this post)

http://litware/_api/web/lists/getbytitle(‘Products’)/items()?$select=Title,Price,Supplier_/Title&$expand=Supplier_/Title

To filter on the supplier title:

http://litware/_api/web/lists/getbytitle(‘Products’)/items()?$select=Title,Price,Supplier_/Title&$expand=Supplier_/Title&$filter=Supplier_/Titleeq ‘Luca’

At the time of this writing I cannot see how to grab another field like ‘CompanyType’ in the Suppliers list; or even do a second lookup to the country list.

Step8. Sorting

Just use Orderby:

http://litware/_api/web/lists/getbytitle(‘Products’)/items()?$select=Title,Price,Supplier_/Title&$expand=Supplier_/Title&$orderby=Price

We can also sort on multiple columns:

http://litware/_api/web/lists/getbytitle(‘Products’)/items()?$select=Title,Price,Supplier_/Title&$expand=Supplier_/Title&$orderby=Title,Price

returns Acura before BMW (same price).

Step9.Join and taxonomy

I haven’t be able to retriev taxonmy datadoesn’t work even if we try to have a join with the taxonomyhiddenlist (the metadata are cached in this hidden list, at the site collection level).

Step10.Using the Search API

http://litware/_api/search/query?querytext=’Luca’