Learn to Query the SharePoint List Data Service with LinqPad
Written By: Chris Beckett -- 9/2/2011 --
(3333) comments --
Categories: Integration with other products , Programming and Customizations, SharePoint 2010, SharePoint Foundation 2010
< Prev -
- 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
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.
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"
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.
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.
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.
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.
< Prev -
- Next >