By: Chris Beckett | Comments | Related: > SharePoint
Problem
As the amount of business data stored in SharePoint lists and libraries increases, it is becoming a necessity to be able to access and integrate with that data across many devices and platforms using a variety of tools and technologies. How can we access SharePoint data from an iPhone? How can we access SharePoint data from a PHP-based web site?
Solution
The Open Data Protocol (ODATA) is a platform and language agnostic protocol proposed by Microsoft as a standard for reading and writing data with RESTful web services over HTTP. Although an ODATA-compliant web service can be written or consumed across just about any HTTP-enabled language or device, Microsoft introduced ADO.NET Data Services in .NET 3.51 (later renamed to WCF Data Services in .NET4) to make it easy to develop services that meet the ODATA standard.
The List Data service was introduced in SharePoint 2010, and provides an ODATA-compliant web service based on ADO.NET Data Services that provides query/insert/update/delete capabilities against SharePoint list data. This tip will demonstrate how to use LinqPad to query the List Data service using Linq to SQL syntax, and examine how Linq queries are translated to the ODATA protocol, can be used with the List Data service.
Introducing LinqPad
LinqPad is a free development tool for learning and testing Language Integrated Query (LINQ) against SQL, ODATA, or an Entity Framework solution. One of the advantages of the LinqPad utility is that it will translate LINQ syntax into ODATA compliant HTTP requests, and show the results in a data grid, as well as the generated ODATA URL syntax. It is a great learning tool for exploring both LINQ and ODATA.
Introducting the List Data Service
The List Data service is a WCF RESTful web service based on ADO.NET Data Services and .NET 3.51. Like most of the SharePoint Web Services technologies including the SOAP-based ASMX web services, and the Client Object Model, it lives under "_vti_bin", a site-relative virtual path, and can be referenced at "/_vti_bin/ListData.svc". You can easily test the List Data service directly from your browser, by appending the web service to the URL of any SharePoint site.
By default, without any additional parameters, the List Data service is designed to return $metadata about addressable entities (lists and libraries) available on the site that can be accessed. The data formats supported by ODATA and the List Data service are ATOMPUB (default) and JSON. Metadata about the site is returned as an ATOMPUB formatted feed.
Querying the List Data Service with LinqPad and ODATA
The first step after installing and running LinqPad is to create a data connection by click "Add Connection". The next step is to select "WCF Data Services (OData)" and click Next.
The next step is to enter the URL to an ODATA compliant web service. You can utilize the URL of any SharePoint, and then append the reference to the List Data service. For this demonstration, I am using a custom site that I developed that has implemented the SQL Server 2000 Northwind sample database as SharePoint lists. The relational database model has been implemented using lists relationships based on Lookup columns between lists, complete with data. This is an important feature of ODATA and the List Data service - it supports the ability to query across relationships between entities.
Once you have successfully added a connection, LinqPad will automatically query the service for metadata on available entities and load the entity explorer with available lists, columns, and even identify lookup columns as available list relationships. To begin learning about querying list data, you can right-click on any list to select a set of pre-defined LINQ query templates.
Let's take a look at executing a simple query against a list. The Skip(#) and Take(#) functions determine how many rows in the dataset to skip over, and how many rows to return to the client respectively. ODATA supports these operations providing a simple mechanism to allow data to be paged between a client and server. It is always a good idea when querying data over an HTTP connection to use these operations to control how much data is returned in a single operation.
After executing the query against the data souce, LinqPad will automatically parse and display the results in a data grid. By clicking the SQL view of the results, we can view the generated ODATA query executed against the List Data service connection. As shown below, the LINQ query against the Products list has been appended to the HTTP URL for the List Data service complete with Skip and Take operations. In fact, you can cut and paste the URL directly from LinqPad into a browser to execute and view the results in ATOMPUB format.
Let's take a look at a slightly more complex example that includes a query filter. In the sample below, I have expanded the LINQ query to include a "where" filter that uses a list relationship between the Products and Category lists defined using a standard SharePoint Lookup column. Traversing a relationship between entities is referred to as "expanding" the relationship. To make things interesting, I have also utilized a string function looking for categories that "contain" beverages.
After executing the query, we can see that LinqPad has translated the LINQ query, and utilized the ODATA $filter operation to form the appropriate query. By now you should be appreciating why LinqPad is a useful way to learn ODATA syntax, and the benefits of being able to query an ODATA compliant web services using a set of standard operations.
For our final example, let's take a look at a complete sample that includes ordering data, and defining which columns are returned by the query. In this final sample, we have included an additional list relationship with the Supplier list, including an 'order by' clause, and a select operation that returns the name of the supplier from the Supplier list based on the list relationship.
For this final query, I have expanded the generated URL into multiple lines to make it easier to read. We can see that each of the elements of a LINQ query have been mapped to a set of standard ODATA operations that include $filter, $orderby, and $select. List relationships are identified using a "Related List/Column" format, and the $expand operation was added to support selecting columns across multiple lists.
ODATA supports a powerful set of operations and functions that includes not only querying, but also inserting, updating and deleting data. The SharePoint List Data service makes accessing list items using standard web protocols both easy, and powerful.
Next Steps
- Learn more about the Open Data Protocol (ODATA) including samples in Java, PHP and other languages.
- Learn more about Using Microsoft ADO.NET Data Services on MSDN.
- Learn more about
Using the REST interface with the SharePoint List Data service on MSDN.
- Check out these other tips:
About the author
Chris Beckett is a Business Solutions Architect, Mentor and Trainer with 20 years of experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
