First Look: Querying Project Server 2013 OData with LINQPad

As I gradually immerse myself into the world of Project Server 2013, one of the major changes I’ve been forced to come to grips with is the new method of querying Project Server data through OData.  OData is now the preferred mechanism to surface cloud based data, and is designed to replace direct access to the SQL database.

To access Project Server OData feeds, simply add this to your PWA URL:

…//_api/ProjectData/

…meaning that the PWA site at http://demo/pwa would have an OData feed at http://demo/PWA//_api/ProjectData/.

The results look something like this, i.e. pretty much like an RSS feed:

image

In fact, one of the tricks you’ll pick up after working with OData is turning off the default Internet Explorer RSS interface, which tends to get in the way of viewing OData feeds.  Access that via the Internet Explorer > Internet Options page.

image

I can also consume OData feeds directly in Office applications such as Excel.  In Excel 2013, I now have the option to connect to OData directly…

image

That yields the table selection which I may then use to develop my reports.

image

More on that topic in later posts.  In this post, I want to talk about writing queries against OData using LINQ a querying language that some of you are probably familiar with.  I would hardly call myself an expert, but I’ve found the easiest way to get up to speed is to download and install LINQPad, a free query writing tool.

With LINQPad, I can teach myself LINQ, following a simple step by step tutorial.

image

…and then point LINQPad at a hyper-V image of Project Server to test my queries.

image

…even better, LINQPad generates the URL that I’ll need to customize the OData feed from Project Server:

image

I.e. the query above to select the Project Name and Project Start from all projects starting after 1/1/2013 yields a URL of:

http://demo/PWA//_api/ProjectData/Projects()?$filter=ProjectStartDate ge datetime’2013-01-01T00:00:00’&$select=ProjectName,ProjectStartDate

Minor caveat to this approach: out of the box, LINQPad doesn’t authenticate to Office 365 tenants.  It looks like other folks have already figured out a solution to this, which I haven’t gotten around to deciphering on my own.  In the meantime, LINQPad works fine against on-premises installations.  For now, I’ll probably be developing my queries against an on-prem data set, then applying the URLs to my Office 365 tenant.

For example, using my online tenant, I can parse the following URL:

https://lavinsky4.sharepoint.com/sites/pwa//_api/ProjectData/Projects()?$filter=ProjectStartDate ge datetime’2012-01-01T00:00:00’&$select=ProjectName,ProjectStartDate

…and get the correct results. Here’s what it looks like in Excel:

image

Coming up….porting some of my previous report queries into LINQ.

Advertisements
First Look: Querying Project Server 2013 OData with LINQPad

8 thoughts on “First Look: Querying Project Server 2013 OData with LINQPad

  1. Doug Welsby says:

    Andrew – I spent some time troubleshooting…until I finally saw your comment “…Minor caveat to this approach: out of the box, LINQPad doesn’t authenticate to Office 365 tenants.” Have you figured this out yet? Any hints?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s