Second Life of a Hungarian SharePoint Geek

March 28, 2013

Limitations of the $expand REST query option when working with Project Server OData services

Filed under: Project Online, PS 2013, REST — Tags: , , — Peter Holpar @ 13:43

Recently I prepared a REST request for the ProjectData service of Project Server. I assembled the following simple query using LINQPad :

from a in Assignments
select new

LINQPad sent the following query to the server:


The server responded with:

Unsupported query option encountered: $expand

I tried to submit a similar query, but specified exactly the property I needed (used Resource/ResourceCanLevel in place of former value Resource) in the $expand option:


The response did not change.

Then – as a last desperate try – I’ve tried to simply omit the $expand option, like this:


Of course, I’ve received this message:

Only properties specified in $expand can be traversed in $select query options

The reason and explanation for this behavior I found as a note on MSDN:

The ProjectData service does not implement the $links query option or the $expand query option. Excel 2013 internally uses the Association elements and the AssociationSet elements in the entity data model to help create associations between entities, for pivot tables and other constructs.”

Bad enough… The good news: the ProjectServer service (_api/ProjectServer) has no such limitation.

March 8, 2013

Accessing Office 365 REST services using LINQPad

LINQPad is a great tool, even for a SharePoint developer when working with the RESTful web services. However, it does not provide an authentication mechanism against Office 365, a major issue when there is no on-premise SharePoint or Project Server at your hand to develop and test your queries (as suggested by Andrew Lavinsky in this post), as illustrated by the figures below.

We add a new connection of type WCF Data Service 5.1 (OData 3) to LINQPad:


Specify the URI of the ListData.svc at our  O365 tenant, and Default (XML) as Formatter.


Then we receive the following error (you can try to specify username and password in the previous step, but it makes no difference):

Error: The remote server returned an error: (403) Forbidden.


I’ve found a workaround for this issue on the web, but for me it was so complex at the first sight (even though I later understood how it should work), so I decided to find another way, using my other favorite tool Fiddler.

Our “solution”: we will “cache” the authentication cookies from an Internet Explorer session, then inject the same cookies to the LINQPad sessions.

Start Fiddler, choose Roles / Customize Rules…, and edit the CustomRules.js file (don’t forget to create a backup!).

Before the OnBeforeRequest function add this code:

static var authCookies = "";
static var o365Site = ""; // modify this value!

At the beginning of the OnBeforeRequest function add this code:

if (oSession.HostnameIs(o365Site)) {
  var cookie = oSession.oRequest["Cookie"];
  if ((cookie == "") && (authCookies != "")) {
    //oSession.oRequest["Accept"] = "text/html, application/xhtml+xml, */*";
    oSession.oRequest["Cookie"] = authCookies;

At the beginning of the OnBeforeResponse function add this code:

if (oSession.HostnameIs(o365Site)) {
  var cookie = oSession.oRequest["Cookie"];
  if (cookie != "") {
    authCookies = cookie;

Done! Save the changes of CustomRules.js. Then (having Fiddler running and capturing network traffic!) start IE, navigate to your O365 site, and authenticate yourself when requested. Cookies are cached in Fiddler at this point.

Note: In my development environment I always enable Fiddler to decrypt HTTPS traffic. I have not tested this solution with decryption disabled, and have doubts, if it should work. If you test it, please, leave us a comment with the results.


In the next step (the same Fiddler instance is still running and capturing network traffic!), try to reconnect LINQPad to the same O365 site. Cookies are replayed by Fiddler, authentication in LINQPad should work this time.


To test the functionality, I submitted a simple query:


So far the good news. After “solving” the authentication issue, let’s see a further problem, and that is bound to the (missing) $metadata support of Microsoft’s OData implementation in SP 2013.

As you might have noticed, in the example above I used the “old-style”, SP 2010 compatible version of the REST API (_vti_bin/ListData.svc), and not the “new-school” format, including _api (like _api/web/), and that is no just accidentally.

Since LINQPad needs the $metadata to build up the object structure, it simply does not work without that:


Error: The remote server returned an error: (404) Not Found.


In Fiddler (HTTP status 404):

Cannot find resource for the request $metadata.

One of the workarounds may be (again with Fiddler) to use an existing beta installation of SP 2013, capture the response for the $metadata request to a file, then in the development environment send it as a response for the $metadata request from LINQPad automatically, but it is rather hacky, even for me. In my opinion it simply does not worth monkeying so much with that, we should rather learn and use the syntax of the OData requests.

Developers (including myself) who need to work against Project Online are luckier. Although the OData service of PS seems to be available only through the new _api interface, the $metadata support is still there in this case.


And a sample query:


Have fun using LINQPad against your O365 site and Project Online!

UPDATE: The same trick can be applied, when we would like to add a service reference to our Visual Studio project, referring to an O365 / Project Online site (just like described here for an on-premise PS). Although VS 2012 displays an authentication dialog for my – in this case German – O365 site (it is not the case with VS 2010),


it seems to have no effect (at least, not always?):


Note, that based on the error details it is likely not just a simple authentication issue, as VS would like to append /_vti_bin/ListData.svc to the service URL.

In this case you can use Fiddler again to replay the cookies and authenticate on behalf of VS:


UPDATE 2 (3/22/2013): I was wrong when I wrote that developers working against Project Online were much luckier. I’ve just realized, that although $metadata is really available for the ProjectData service, it is not supported for the ProjectServer service, that you should use to access entities like enterprise resources, calendars or custom fields (as illustrated by the next screenshot, requesting



Error: The remote server returned an error: (404) Not Found.


In Fiddler (HTTP status 404):

Cannot find resource for the request $metadata.

March 6, 2013

Handling calendar exceptions with CSOM on Project Online

Filed under: CSOM, Project Online, PS 2013 — Tags: , , — Peter Holpar @ 15:28

In the recent days I’m busy with Project Online and its CSOM API (if you would like to avoid some traps, see my first experiences with the Project 2013 SDK here). One of the tasks includes manipulating calendar exceptions for enterprise resources, like creating, listing and deleting calendar exceptions for our resources (I mean colleagues) as their holiday requests get approved / rejected.

I should note, that editing calendar exceptions seems to be not available on the Project Server user interface, only using the desktop client Project Professional. As far as I see (using Fiddler), Project Professional still uses the Project Server Interface (PSI) web services, although it is officially not the way we have to choose when working against Project Online:

“To develop applications for Project Online, you must use the Microsoft.ProjectServer.Client namespace instead of the PSI.” (source: MSDN)

I made some quick checks, and technically (with some minor hacks) it still seems to be possible to use PSI with Project Online, but let’s try to keep with CSOM this time, at least as long as it covers our needs.

Before delving deeper into code details, you can find some useful links about authentication against O365 in my former post, that I suggest to read as we apply a similar technique in this case.

I’ve created a new console application (C#, Target framework: .NET framework 4, Platform target: x86), and added some assembly references as shown below. To have Microsoft.IdentityModel, you should download the Windows Identity Foundation SDK 3.5.


I borrowed the helper classes Office365ClaimsHelper and WcfClientContracts from this post of Sundararajan Narasiman. The link to the source code of those classes can be found at the bottom of that post. You should alter the namespace of the classes to match the one of your application.

I’ve created an internal static ProjectOnlineTest class to wrap the functionality, with the following namespaces imported:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Net;
using Microsoft.ProjectServer.Client;

The most important values are declared first, you should edit them to match your own site and user credentials.

Code Snippet
  1. private static readonly string projName;
  2. private static readonly string connectUserName;
  3. private static readonly string connectPwd;  
  4. private static readonly string pwaPath;
  5. private static readonly string projDomain;
  7. static ProjectOnlineTest()
  8. {
  9.     // update the values here
  10.     projName = "YourProjSite";
  11.     connectUserName = "john.smith";
  12.     connectPwd = "password";
  14.     projDomain= string.Format("{0}", projName);
  15.     pwaPath = string.Format("https://{0}", projName);
  17. }

I added some helper properties and a method to make my code a bit simpler to read and modify. Note the format of the Project Online user name in the GetFullUserName method.

Code Snippet
  1. private static ProjectContext ProjectContext
  2. {
  3.     get
  4.     {
  5.         var pc = new ProjectContext(pwaPath);
  6.         var claimsHelper = ClaimsHelper;
  7.         pc.ExecutingWebRequest += claimsHelper.clientContext_ExecutingWebRequest;
  9.         return pc;
  10.     }
  11. }
  13. private static Office365ClaimsHelper ClaimsHelper
  14. {
  15.     get
  16.     {
  17.         return new Office365ClaimsHelper(new Uri(pwaPath),
  18.                                             string.Format("{0}@{1}", connectUserName, projDomain),
  19.                                             connectPwd);
  20.     }
  21. }
  23. private static string GetFullUserName(string userName)
  24. {
  25.     return string.Format("i:0#.f|membership|{0}@{1}", userName, projDomain);
  26. }

The CreateCalendarException method creates a new calendar exception for the resource (user). The key is to use the CalendarExceptionCreationInformation class and calling the Update method of the EnterpriseResourceCollection class. Credits go for the latter one again to Jim Corbin, the member of the Microsoft Project SDK team:

When you make a change to one of the properties, and then save the change, Project Server automatically checks out the resource, make the change, and checks the resource back in — similar to the process of getting an enterprise resource in Project Pro, editing it, and then saving.” (source: MSDN Forum thread)

Code Snippet
  1. internal static void CreateCalendarException(string userName, string exceptionName, DateTime exceptionStart, DateTime exceptionFinish)
  2. {            
  3.     using (var projContext = ProjectContext)
  4.     {
  5.         var ers = projContext.EnterpriseResources;
  6.         var usrs = projContext.Web.SiteUsers;
  7.         var usr = string.IsNullOrEmpty(userName) ? projContext.Web.CurrentUser : usrs.GetByLoginName(GetFullUserName(userName));
  8.         var er = ers.GetByUser(usr);
  9.         projContext.Load(ers);
  10.         projContext.Load(usrs);
  11.         projContext.Load(usr);
  12.         projContext.Load(er);
  13.         projContext.ExecuteQuery();
  15.         var rcExs = er.ResourceCalendarExceptions;
  17.         CalendarExceptionCreationInformation cexCI = new CalendarExceptionCreationInformation
  18.         {
  19.             Name = exceptionName,
  20.             Start = exceptionStart,
  21.             Finish = exceptionFinish
  22.         };
  23.         rcExs.Add(cexCI);
  25.         ers.Update();
  26.         projContext.ExecuteQuery();
  27.     }
  28. }

Note: The Associate resource with a user account option for your resource should be checked (or if no user name is specified, your authenticated user should have a resource mapped to) to successfully lookup an enterprise resource by the user (e.g. GetByUser method). Otherwise, you should iterate through the resources and find the one with the specified name, or use a Guid if you know one.


The DeleteCalendarException method deletes the calendar exceptions of the selected user (resource) with the specified name.

Code Snippet
  1. internal static void DeleteCalendarException(string userName, string exceptionName)
  2. {
  3.     using (var projContext = ProjectContext)
  4.     {
  5.         var ers = projContext.EnterpriseResources;
  6.         var usrs = projContext.Web.SiteUsers;
  7.         var usr = string.IsNullOrEmpty(userName) ? projContext.Web.CurrentUser : usrs.GetByLoginName(GetFullUserName(userName));
  8.         var er = ers.GetByUser(usr);
  9.         var rcExs = er.ResourceCalendarExceptions;
  10.         projContext.Load(ers);
  11.         projContext.Load(usrs);
  12.         projContext.Load(usr);
  13.         projContext.Load(er);
  14.         projContext.Load(rcExs);
  15.         projContext.ExecuteQuery();
  17.         foreach (CalendarException ce in rcExs)
  18.         {
  19.             rcExs.Remove(ce);
  20.         }
  22.         ers.Update();
  23.         projContext.ExecuteQuery();
  24.     }
  25. }

The DumpCalendarExceptions method simply dumps the existing resources and their calendar exceptions to the console.

Code Snippet
  1. internal static void DumpCalendarExceptions()
  2. {
  3.     using (var projContext = ProjectContext)
  4.     {
  5.         var ers = projContext.EnterpriseResources;
  6.         projContext.Load(ers);
  7.         projContext.ExecuteQuery();
  9.         Console.WriteLine("\nResource ID : Resource name");
  11.         foreach (EnterpriseResource res in ers)
  12.         {
  13.             Console.WriteLine("\n\t{0}\n\t{1}", res.Id, res.Name);
  14.             var rcExs = res.ResourceCalendarExceptions;
  15.             projContext.Load(rcExs);
  16.             projContext.ExecuteQuery();
  17.             foreach (CalendarException ce in rcExs)
  18.             {
  19.                 Console.WriteLine("\n\t{0}\n\t{1}\t{2}\t{3}", ce.Id, ce.Name, ce.Start, ce.Finish);
  20.             }
  21.         }
  22.     }
  23. }

And here is a sample usage of the methods above:

Code Snippet
  1. static void Main(string[] args)
  2. {
  3.     ProjectOnlineTest.DeleteCalendarException("project.user", "Test exception X");
  4.     ProjectOnlineTest.CreateCalendarException("another.user", "Test exception A", new DateTime(2013, 3, 5), new DateTime(2013, 3, 15));
  5.     ProjectOnlineTest.DumpCalendarExceptions();
  7.     Console.WriteLine("Press a key to exit: ");
  8.     Console.ReadKey(false);
  9. }

I hope this sample makes it easier to work with Project Online using CSOM.

Blog at