Pete Skelly

Pete is a VP of Technology at ThreeWill. Pete’s primary role is driving the overall technology strategy and roadmap for ThreeWill. Pete also serves as ThreeWill’s Hiring Manager and is constantly looking for new talent to join the ThreeWill Team.

Recently, as part of some of the work for porting the Fab 40 to the new SharePoint 2013 App Model, we needed to expand some of the user information for the currently logged in user.  Sounded simple at first, since I assumed the OData REST query to the SharePoint list would return the information.  Unfortunately, it was not that easy, and the whole SharePoint 2013 OData implementation still seems a bit opaque to me at this point.  Since it is still clear as mud for me, here’s my attempt an an explanation of the $expand query option, and some of the challenges I ran into when using it with SharePoint 2013.

OData Primer

The OASIS OData specification states:

“The OData Protocol is an application-level protocol for interacting with data via RESTful interfaces. The protocol supports the description of data models and the editing and querying of data according to those models.”  In addition there is a description of how the OData protocol differs from other approaches.  “The OData Protocol is different from other REST-based web service approaches in that it provides a uniform way to describe both the data and the data model.”

Fair enough.  Sounds like I should be able to interrogate an OData service(s), query and edit data, and work with the services and data in a very REST-ful way.  I’ll leave reading the complete specification as an exercise for the reader, but I do want to expand (pun intended) on a couple of things to at least explain my findings with SharePoint 2013 and OData.

OData is built on an abstract data model which describes the data which a service provides. The Entity Data Model (or EDM) is the “schema” for a given set of entities and their relationships. For example, Customer and Account entities might be related by AccountId. Note: I have NOT called them lists yet on purpose.   Given the model above, I would  expect to be able to access the User, Customer, and Account entities, and combinations of those entities.  To continue our example above, you can imagine calling the following URLs to return User, Customer and Account entity information respectively.

• http://host/service/Users
• http://host/service/Customers
• http://host/service/Accounts

In fact, if I create Account and Customer lists in SharePoint and call a form of the above, I get something like the following back.  For example, calling

[code]
https://company.sharepoint.com/sites/dev_site/_api/web/lists/Customers
[/code]

in Microsoft 365 yields:

Wait a second… that’s not what I expected!  Well, let’s dig a little deeper.

OData Query Options, Actions and Functions

Based on the OData spec, that URL should work, right?  Not really – trust me read the spec.  If I can’t call the list directly via that URL, what are my options?  If we want to know more about the lists, lets try using the $metadata query option.  To continue our example, since the OData spec indicated that it “provides a uniform way to describe both the data and the data model,” I can find out more about a given entity by calling $metadata.  The spec states the “…Metadata Document URL MUST be the root URL of the service with $metadata appended.”  Well then, calling our previous URL as an HTTP GET request should have worked – I know, I can’t let it go!  Let’s back up a step, literally, and try one of the following URLs.

• https://company.sharepoint.com/sites/dev_site/_api/web/lists/
• https://company.sharepoint.com/sites/dev_site/_api/web/lists?$metadata

Now we’re getting somewhere! It looks like the second URL gives us a wealth of metadata and data to consume.

OK, now that we’re finding our way a bit; we obviously need some mechanism for querying, filtering, ordering and managing the data which the service returns.  This is where the Query Options come in.  The standard, expected capabilities to provide filtering, ordering, and paging are:

• $metadata
• $select
• $filter
• $orderby
• $skip
• $top
• $count

Awesome.  The $metadata query above gives us some clues to how to retrieve information for a given list (see line 5 in the image above).  But how in the world would I know the GUID of list to call _api/web/Lists(guid’xxxx’)?   Well, you really can’t, except with the metadata.  This is where the OData spec affords some extensibility.  Through Actions and Functions a service can provide specialized extension points.  See http://docs.oasis-open.org/odata/odata/v4.0/os/part1-protocol/odata-v4.0-os-part1-protocol.html#_Functions_1.  We found out the actual id of the list (line 5), but that is not very friendly way to access the list itself.  [Note: the image above is elided, but trust me, run that query and review the output, VERY interesting stuff.] 

The short story on the Actions and Functions – Actions can modify data, Functions cannot!  The trick to understanding what Actions and Functions are available from SharePoint 2013 REST seems to be “hidden” in the documentation.

Whenever possible, the URI for these REST endpoints closely mimics the API signature of the resource in the SharePoint client object model. For example:

Client object model method:

• List.GetByTitle(listname).GetItems()

REST endpoint:

• http://server/site/_api/lists/getbytitle(‘listname’)/items

That snippet is from the best resource for the SharePoint 2013 implementation of OData I have found: http://msdn.microsoft.com/en-us/library/office/dn292556(v=office.15).aspx#sectionSection1  Bookmark it! Go… Now… Do it!  This page and all the links, combined with the OData spec will help you greatly.  Main take away from that quote should be the use of the Client Object Model methods from REST!  Given that nugget, using the following URLs should now be a bit obvious.

[code]
https://comany.sharepoint.com/sites/dev_site/_api/web/lists/getByTitle(‘Customers’)/items
https://comany.sharepoint.com/sites/dev_site/_api/web/lists/getByTitle(‘Customers’)/items?$select=Id, Title
https://comany.sharepoint.com/sites/dev_site/_api/web/lists/getByTitle(‘Customers’)/items?$select=Id,Title&$filter=Id eq ‘1’
[/code]

The OData $expand Query Option

Now, given all of the above context, the OData spec provides us with an $expand query option.  The $expand option *should* provide you the ability to retrieve “related entities that MUST be represented inline. “Excellent, getting more data from one of more related entities in a single query. Sounds great, but…you knew there was a “but” right?  If you read the OData spec, then you know $expand expects a “comma-separated list of navigation property names, optionally followed by expand options…” (filtering, sorting, etc.)   The end result should mean providing an entity name for expansion and the properties you want expanded.  For example, to expand the Account list columns in the image below:

[code]
_api/web/lists/getByTitle(‘Customers’)/items?$select=Id,Title,AccountName&$expand=Accounts
[/code]

Try it, I dare you!  It won’t work.  Remember I said “entity” earlier and that I did not use “list” on purpose.  Well if you know SharePoint lists, you know you can change field display names, and many other things in List Settings. In the screen shot above, the “Affiliation” columns are columns which are lookups to the Accounts list. I renamed the column name in the Customers list to be “Affiliate”. This is an important point to remember. The “entity” you are referencing here is the “Affiliation” entity, not the “Accounts” entity.  If I call the Account Lookup field “Affiliate” and link by Id, then project the Name and AccountId columns I have to use the following to get the AffiliationId back.

[code]
_api/web/lists/getByTitle(‘Customers’)/items?$select=Title,AffiliationId
[/code]

Next, if you specified the following $expand query option, you would expect to receive the related content “inline” as the spec describes, right?

[code]
_api/web/lists/getByTitle(‘Customers’)/items?$select=Title,AffiliationId&$expand=Affiliation
[/code]

Wrong! Apparently, if you are expanding entities you need to “dereference” (my term) your fields for expansion with a forward slash (/).  Not only do you need to “dereference” the fields, but you need to provide all of the fields you want.

[code]
_api/web/lists/getByTitle(‘Customers’)/items?$select=Title,Affiliation/Id&$expand=Affiliation
[/code]

So, if you have been following along, can you guess if the following will work?

[code]
_api/web/lists/getByTitle(‘Customers’)/items?$select=Title,Affiliation/Id,Affiliation/Name&$expand=Affiliation
[/code]

Silly goose! Of course not, we renamed the Title field to ‘Name,’ so we must refer to this as the InternalName not the display name.  It’s so obvious! How could you have missed that?

[code]
_api/web/lists/getByTitle(‘Customers’)/items?$select=Title,Affiliation/Id,Affiliation/Title&$expand=Affiliation
[/code]

Finally, we see what we expect from the query above (result elided).

[code language=”css”]
{
__metadata:
{
id: "b5fa6848-7b63-4be2-9a1a-e9b2f81166b8"
uri: "https://company.sharepoint.com/sites/dev_site/_api/Web/Lists(guid’44fba6e3-1be8-41a0-a186-d7c42503ea21′)/Items(1)"
etag: "1"
type: "SP.Data.CustomersListItem"
}
Affiliation:
{
__metadata:
{
id: "88a23a12-eeba-4178-bf3d-2a66074b8d1f"
type: "SP.Data.AccountsListItem"
}
Id: 1
Title: "ACME,Inc"
}
Title: "JBU"
}
[/code]