Subscribe:      
 

Learn to Query the SharePoint List Data Service with LinqPad

Written By: Chris Beckett -- 9/2/2011 -- join -- contribute -- (1307) comments -- printer friendly version

Rating: Rate --

Categories: Integration with other products , Programming and Customizations, SharePoint 2010, SharePoint Foundation 2010

< Prev - 1 | 2 | - Next > | Become a paid author

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

< Prev - 1 | 2 | - Next >



Learn more about SharePoint



Sponsor Information




Copyright (c) 2010-2017 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | contribute | feedback | about
Some names and products listed are the registered trademarks of their respective owners.


MSSharePointTips.com | MSSQLTips.com