Second Life of a Hungarian SharePoint Geek

March 29, 2017

Working with the REST / OData Interface from PowerShell

Filed under: OData, PowerShell, REST, SP 2013 — Tags: , , , — Peter Holpar @ 20:56

If you follow my blog you might already know that I am not a big fan of the REST / OData interface. I prefer using the client object model. However there are cases, when REST provides a simple (or even the only available) solution.

For example, we are working a lot with PowerShell. If you are working with SharePoint on the client side at a customer, and you are not allowed to install / download / copy the assemblies for the managed client object model (CSOM), you have a problem.

Some possible reasons (you should know, that the SharePoint Server 2013 Client Components SDK is available to download as an .msi, or you can get the assemblies directly from an on-premise SharePoint installation):

  • You might have no internet access, so you cannot download anything from the web.
  • If you happen to have internet access, you are typically not allowed to install such things without administrator permissions on the PC. It’s quite rare case, if you or the business user you are working with has this permission.
  • You have no direct access on the SharePoint server, so you cannot copy the assemblies from it.
  • You are not allowed to use your own memory stick (or other storage device) to copy the assemblies from it.
  • Even if there is no technical barrier, company policies might still prohibit you using external software components like the CSOM assemblies.

In this case, using the REST interface is a reasonable choice. You can have a quick overview of the REST-based list operations here.

The main questions I try to answer in this post:

  • Which object should I use to send the request?
  • How to authenticate my request?
  • How to build up the payload for the request?

First of all, I suggest you to read this post to learn some possible pitfalls when working with REST URLs from PowerShell and how to avoid them with escaping.

Reading data with the SharePoint REST interface

Reading data with a GET request

Sending a GET request for a REST-based service in PowerShell is not really a challenge, might you think, and you are right, it is really straightforward most of the cases. But take the following example, listing the Id and Title fields of items in a list:

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(‘$listTitle‘)/Items?`$select=Id,Title"

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = ‘application/json;odata=verbose’

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
# ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.
#$response = $reader.ReadToEnd()
$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

$result = ConvertFrom-Json -InputObject $response
$result.d.results | select Id, Title

If you would use

$response = $reader.ReadToEnd()

instead of

$response = $reader.ReadToEnd() -creplace ‘"ID":’, ‘"DummyId":’

then you became this exception, when trying to convert the JSON response:

ConvertFrom-Json : Cannot convert the Json string because a dictionary converted from it contains duplicated keys ‘Id’ and ‘ID’.

The reason, that the JSON response of the server contains the fields Id and ID. JSON is case-sensitive, but PowerShell is not, so it is an issue if you want to convert the JSON response to a PowerShell object. You can read more about it in this post, although I don’t like the solution proposed there. Although it really helps to avoid the error, but it uses the case insensitive replace operator instead of the case sensitive creplace, so it converts both fields into a dummy field. PowerShell seems to have no problem with the duplicated properties.

Instead of using a System.Net.WebRequest object, we can achieve a shorter version using the Invoke-RestMethod cmdlet. Note, that we don’t select and display the Id property in this case to avoid complications. See my comments about that in the next section discussing the POST request.

$listTitle = "YourList"
$url = "http://YourSharePoint/_api/Web/Lists/GetByTitle(‘$listTitle‘)/Items?`$select=Title"
$headers = @{ ‘Accept’ = ‘application/json; odata=verbose’}
$result = Invoke-RestMethod -Uri $url -Method Get -Headers $headers -UseDefaultCredentials
$result.d.results | select Title

Reading data with a POST request

There are cases when you have to use the POST method instead of GET to read some data from SharePoint. For example, if you need to filter the items via a CAML query. In the following example I show you how to query the file names all documents in a library recursively that are older than a threshold value:

$listTitle = "YourDocuments"
$offsetDays = -30

$urlBase = "http://YourSharePointSite/"
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems?`$select=FileLeafRef"

$viewXml = "<View Scope=’Recursive’><ViewFields><FieldRef Name=’Created’/><FieldRef Name=’FileLeafRef’/></ViewFields><Query><Where><Lt><FieldRef Name=’Created’ /><Value Type=’DateTime’><Today OffsetDays=’$offsetDays’ /></Value></Lt></Where></Query></View>"

$queryPayload = @{ 
                   ‘query’ = @{
                          ‘__metadata’ = @{ ‘type’ = ‘SP.CamlQuery’ };                      
                          ‘ViewXml’ = $viewXml
                   }
                 } | ConvertTo-Json

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

# displaying results
$result.d.results | select FileLeafRef

Just for the case of comparison I include the same payload in JavaScript format:

var queryPayload = {
                     ‘query’ : {
                        
‘__metadata’ : { ‘type’ : ‘SP.CamlQuery’ },
                         ‘ViewXml’ : viewXml
                    
}
                   };

As you can see, these are the most relevant differences in the format we need in PowerShell:

  • We use an equal sign ( = ) instead of  ( : ) to separate the name and its value.
  • We use a semicolon ( ; ) instead of the comma ( , ) to separate object fields.
  • We need a leading at sign ( @ ) before the curly braces ( { ).

The Invoke-RestMethod tries to automatically convert the response to the corresponding object based on the content type of the response. If it is an XML response (see the authentication part above) then the result will be a XmlDocument. If it is a JSON response then the result will be a PSCustomObject representing the structure of the response. However, if the response can not be converted, it remains a single String.

For example, if we don’t limit the fields we need in response via the $select query option:

$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/GetItems"

then the response includes the fields Id and ID again. In this case we should remove one of these fields using the technique illustrated above with the simple GET request, before we try to convert the response via the ConvertFrom-Json cmdlet.

Note: If you still use PowerShell v3.0 you get this error message when you invoke Invoke-RestMethod setting the Accept header:

Invoke-RestMethod : The ‘Accept’ header must be modified using the appropriate property or method.
Parameter name: name

So if it is possible, you should consider upgrading to PowerShell v4.0. Otherwise, you can use the workaround suggested in this forum thread, where you can read more about the issue as well.

If you are not sure, which version you have, you can use $PSVersionTable.PSVersion to query the version number, or another option as suggested here.

Creating objects

In this case we send a request with the POST method to the server. The following code snippet shows, how you can create a new custom list:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘AllowContentTypes’ = $true; ‘BaseTemplate’ = 100;
                    ‘ContentTypesEnabled’ = $true; ‘Description’ = ‘Your list description’; ‘Title’ = $listTitle                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

The response we receive in the $result variable contains the properties of the list we just created. For example, the Id (GUID) of the list is available as $result.d.Id.

Updating objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to MERGE. The following code snippet shows, how to change the title of the list we created in the previous step:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)"

$queryPayload = @{ 
                    ‘__metadata’ = @{ ‘type’ = ‘SP.List’ }; ‘Title’ = ‘YourListNewTitle’                      
    } | ConvertTo-Json

$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘Accept’ = ‘application/json; odata=verbose’; ‘IF-MATCH’ = ‘*‘; ‘X-HTTP-Method’ = ‘MERGE’ }

$result = Invoke-RestMethod -Uri $url -Method Post -Body $queryPayload -ContentType ‘application/json; odata=verbose’ -Headers $headers –UseDefaultCredentials

Deleting objects

In this case we send a request with the POST method to the server and set the X-HTTP-Method header to DELETE. The following code snippet shows, how you can delete a list item:

$listTitle = "YourList"

$urlBase = "http://YourSharePoint/&quot;
$urlAuth = $urlBase +"_api/ContextInfo"
$url = $urlBase + "_api/Web/Lists/GetByTitle(‘$listTitle’)/Items(1)"

# authentication
$auth = Invoke-RestMethod -Uri $urlAuth -Method Post -UseDefaultCredentials
$digestValue = $auth.GetContextWebInformation.FormDigestValue

# the actual request
$headers = @{ ‘X-RequestDigest’ = $digestValue; ‘IF-MATCH’ = ‘*’; ‘X-HTTP-Method’ = ‘DELETE’ }
$result = Invoke-RestMethod -Uri $url -Method Post -Headers $headers -UseDefaultCredentials

Note: Although the documentation states, that “in the case of recyclable objects, such as lists, files, and list items, this results in a Recycle operation”, based on my tests it is false, as the objects got really deleted.

Final Note: This one applies to all of the operations discussed in the post. If the SharePoint site you are working with available via HTTPS and there is an issue with the certificate, you can turn off the certificate validation, although it is not recommended in a production environment. You should include this line in your code before making any web requests:

[System.Net.ServicePointManager]::ServerCertificateValidationCallback = { $true }

March 4, 2017

Using PowerShell and REST with Project Server (or SharePoint) for Reporting

Filed under: OData, Project Server, REST — Tags: , , — Peter Holpar @ 21:43

If you are working with Project Server or SharePoint Server, you should not ignore the potential provided by PowerShell and the REST (OData) interface to create simple reports. You should although at the same time be aware of  a few pitfalls of this combination as well.

Let’s see the next code example first. Its goal is to output the list of projects to the screen, including their Id, Name and ProjectSiteUrl properties:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&quot;

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "application/json;odata=verbose"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data
$result.d.results | select Id, Name, ProjectSiteUrl

If you test the URL http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl from the browser, you see, that all of these three properties are returned. However, if you run the above script from console, you find, that the ProjectSiteUrl column is empty for all of the projects.

If you use the ProjectData OData endpoint instead of the ProjectServer endpoint, and select the corresponding properties, all of the properties will be omitted by the script:

$url = "http://YourProjectServer/PWA/_api/Projects?$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl&quot;

$request = [System.Net.WebRequest]::Create($url)
$request.UseDefaultCredentials = $true
$request.Accept = "application/json;odata=verbose"

$response = $request.GetResponse()
$reader = New-Object System.IO.StreamReader $response.GetResponseStream()
$data = $reader.ReadToEnd()

$result = ConvertFrom-Json -InputObject $data
$result.d.results | select ProjectId, ProjectName, ProjectWorkspaceInternalUrl

Note: If you have a localized version of Project Server, you can either use an OData query including the localized entity and property names, like:

http://YourProjectServer/PWA/_api/ProjectData/Projekte?$select=ProjektID,ProjektName,ProjektArbeitsbereichInterneURL

or switch back to the English version by injecting [en-US] segment after the ProjectData endpoint:

http://YourProjectServer/PWA/_api/ProjectData/[en-US]/Projects?$select=ProjectId,ProjectName,ProjectWorkspaceInternalUrl

Of course, in the first case you should change the property names used in the select statement in the PowerShell script to match the names used in the REST query.

Let’s see another example. In the next case, our goal is to create a .csv file, that one can easily import to Excel, including the name and the RBS (resource breakdown structure) of the resources.

  1. $baseUrl = "http://YourProjectServer/PWA/_api/ProjectServer&quot;
  2. $rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq 'RBS'&$expand=Entries&$select=Entries/InternalName,Entries/Value"
  3. $resourceUrl = $baseUrl + "/EnterpriseResources?$select=Name,Custom_000039b78bbe4ceb82c4fa8c0c400284"
  4.  
  5. #rbs
  6. $rbsRequest = [System.Net.WebRequest]::Create($rbsUrl)
  7. $rbsRequest.UseDefaultCredentials = $true
  8. $rbsRequest.Accept = "application/json;odata=verbose"
  9.  
  10. $rbsResponse = $rbsRequest.GetResponse()
  11. $rbsReader = New-Object System.IO.StreamReader $rbsResponse.GetResponseStream()
  12. $rbsData = $rbsReader.ReadToEnd()
  13.  
  14. $rbsResult = ConvertFrom-Json -InputObject $rbsData
  15. $rsbEntries = $rbsResult.d.results.Entries.results
  16.  
  17. #resources
  18. $resRequest = [System.Net.WebRequest]::Create($resourceUrl)
  19. $resRequest.UseDefaultCredentials = $true
  20. $resRequest.Accept = "application/json;odata=verbose"
  21.  
  22. $resResponse = $resRequest.GetResponse()
  23. $resReader = New-Object System.IO.StreamReader $resResponse.GetResponseStream()
  24. $resData = $resReader.ReadToEnd()
  25.  
  26. $resResult = ConvertFrom-Json -InputObject $resData
  27.  
  28. $resResult.d.results | % {
  29. select -Input $_ -Prop `
  30.     @{ Name='Name'; Expression={$_.Name} },
  31.     @{ Name='RBS'; Expression={$rbs = $_.Custom_x005f_000039b78bbe4ceb82c4fa8c0c400284; If ($rbs.results -is [System.Object[]]) {$rsbEntries | ? { $_.InternalName -eq $rbs.results[0] } | % { $_.Value } } Else {''} } }
  32.     } | Export-Csv -Path ResourceRBS.csv -Delimiter ";" -Encoding UTF8 -NoTypeInformation

Note: The –NoTypeInformation switch of Export-Csv ensures that no type information would be emitted as header into the .csv file. The -Delimiter ";" and the -Encoding UTF8 settings help to produce a .csv file in a format and encoding that can be opened in Excel simply by clicking on the file.

The symptoms are similar as in the first case, only the resource name is included in the file, but the RBS value not.

I’ve included this last code sample in a code block not just because it is a bit longer as the former ones, but because I help that the highlighting helps you to understand the base problem with our scripts, even if you did not catch it at the first example. Have you recognized, that the query options ($filter, $select and $expand) have a different color, as the rest of the query text? Actually, they have the very same color as the variable names (like $baseUrl or $resRequest) in the code. It is because they are handled really as variable names. Since we used double quotes in the code to define the string literals for URLs, and it means PowerShell should parse the string and replace possible variable names with the values of the variable. As we didn’t define variables like $filter, $select or $expand, they are simply removed from the string (replaced by an empty string). See this short explanation for details.

Instead of double quotation marks we should use single quotation marks to leave the query options intact, but in this case we should escape the single quotes (using two single quotation marks) used in the REST query itself.

For example, instead of:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&quot;

we should simply use:

$url = ‘http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&#8217;

and instead of::

$rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq ‘RBS’&$expand=Entries&$select=Entries/InternalName,Entries/Value"

we should use:

$rbsUrl = $baseUrl + ‘/LookupTables?$filter=Name eq ”RBS”&$expand=Entries&$select=Entries/InternalName,Entries/Value’

Note, that the value RBS is enclosed by two single quotation marks on both sides, and not by a double quotation mark!

Alternatively, you can use the double quotation marks to define the strings for the REST queries (for example, if you still would like PowerShell to parse it from some reason), but in this case, you should escape the dollar sign in the query options to disable parsing them out from the string.

For example, instead of:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?$select=Id,Name,ProjectSiteUrl&quot;

we should simply use:

$url = "http://YourProjectServer/PWA/_api/ProjectServer/Projects?`$select=Id,Name,ProjectSiteUrl"

and instead of::

$rbsUrl = $baseUrl + "/LookupTables?$filter=Name eq ‘RBS’&$expand=Entries&$select=Entries/InternalName,Entries/Value"

we should use:

$rbsUrl = $baseUrl + "/LookupTables?`$filter=Name eq ‘RBS’&`$expand=Entries&`$select=Entries/InternalName,Entries/Value"

See this description for more details about PowerShell string parsing and escaping methods.

If you compare our first two examples (the one with the ProjectServer and the other one with the ProjectData endpoint) the results are different, because in the first case the ProjectSiteUrl property is not part of the standard set of properties returned by default for projects via the ProjectServer endpoint, but ProjectData returns all properties, the ProjectWorkspaceInternalUrl property too, even if it is not specified in a $select query option.

In the third case, our query should have returned the entries of the RBS lookup table, but since the query options got lost, it simply return an overview about all lookup tables.

February 27, 2016

Getting the Item Count of all Lists of all Sub-Sites via a Single Request from Client Code

Filed under: Managed Client OM, OData, Project Server, SP 2013 — Tags: , , , — Peter Holpar @ 14:43

Recently I had a task to get the item count of all lists of all Project Web Sites (PWS) of a Project Server instance from a client-side application. Note, that the PWSs are located directly under the Project Web Access (PWA) site, so there is no deeper site structure in this task to deal with, so I was pretty sure that it can be achieved in a single request. Although in my case the task was Project Server related, one can use the same method in the case of SharePoint Server as well, it is only important, that you should not have a multiple level site structure, for a deeper site structure this method simply does not work.

I show you both the REST (OData) and the managed client object model approach. Let’s start with the client OM sample:

  1. string siteUrl = "http://YourProjectServer/PWA&quot;;
  2. using (var clientContext = new ClientContext(siteUrl))
  3. {
  4.     var rootWeb = clientContext.Web;
  5.  
  6.     clientContext.Load(rootWeb, rw => rw.Webs.Include(w => w.Title, w => w.ServerRelativeUrl, w => w.Lists.Include(l => l.Title, l => l.ItemCount)));
  7.     clientContext.ExecuteQuery();
  8.  
  9.     foreach(var web in rootWeb.Webs)
  10.     {
  11.         if (web.Lists.Any())
  12.         {
  13.             Console.WriteLine("Lists of web '{0}' [{1}]", web.Title, web.ServerRelativeUrl);
  14.             foreach (var list in web.Lists)
  15.             {
  16.                 Console.WriteLine("'{0}' [Item count: {1}]", list.Title, list.ItemCount);
  17.             }
  18.         }
  19.     }
  20. }

The corresponding REST query can be submitted as a GET request sent to this URL:

http://YourProjectServer/PWA/_api/web/webs?$expand=Lists&$select=ServerRelativeUrl,Title,Lists/ItemCount,Lists/Title

If you need the item count only from a specific list (for example, the lists with title ‘Risks’) for all subsites, you can easily achieve that in the client OM sample by including a Where clause in the query:

clientContext.Load(rootWeb, rw => rw.Webs.Include(w => w.Title, w => w.ServerRelativeUrl, w => w.Lists.Include(l => l.Title, l => l.ItemCount).Where(l => l.Title == "Risks")));

The corresponding REST query would be:

http://YourProjectServer/PWA/_api/web/webs?$expand=Lists&$filter=Lists/Title eq ‘Risks’&$select=ServerRelativeUrl,Title,Lists/ItemCount,Lists/Title

However, when submitting this request I get a response with status HTTP 400 and the message: The field or property ‘Title’ does not exist.

I’m working on a solution and update this post as soon as I found one. Feel free to help me by sending it as a comment. Winking smile

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:

image

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

image

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.

image

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 = "yourO365Site.sharepoint.com"; // 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.

image

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.

image

To test the functionality, I submitted a simple query:

image

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:

In LINQPad:

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

image

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.

image

And a sample query:

image

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),

image

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

image

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:

image

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 https://yourProjects.sharepoint.com/sites/pwa/_api/ProjectServer).

image

In LINQPad:

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

image

In Fiddler (HTTP status 404):

Cannot find resource for the request $metadata.

Create a free website or blog at WordPress.com.