Second Life of a Hungarian SharePoint Geek

October 22, 2014

How to set the value of a Project Server Enterprise Custom Field via the Project Server Managed Client Object Model

Filed under: Managed Client OM, Project Server — Tags: , — Peter Holpar @ 21:58

Recently I had to set the value of a Project Server Enterprise Custom Field from a .NET client application. The samples I found on the web (like this one) used the PSI , and I found them rather developer-unfriendly. For example, with PSI we should work with DataSet objects, check if the custom field already exists or should be first created, and should set various properties depending of the data type of the custom field.

So I’ve decided to create my own implementation based on the Managed Client Object Model. In my case I had to set a project-related custom field. In the first step I assumed, that the project ID and the internal name of the custom field are known. The internal name of the custom field consists of the prefix Custom_ and the GUID of the field without the separator dashes.

The result is the following code:

  1. var url = @"http://YourProjectServer/pwa";
  2. var projectContext = new ProjectContext(url);
  3.  
  4. var projId = new Guid("98138ffd-d0fa-e311-83c6-005056b45654");
  5. var cfInternalName = "Custom_b278fdf35d16e4119568005056b45654";
  6. object cfValue = "Some value";
  7.  
  8. var proj = projectContext.Projects.GetByGuid(projId);
  9. var draftProj = proj.CheckOut();
  10. draftProj.SetCustomFieldValue(cfInternalName, cfValue);
  11. var cfsX = proj.CustomFields;
  12. draftProj.Publish(true);
  13.  
  14. projectContext.ExecuteQuery();

If the project ID and the internal name of the custom field are not known, it is easy to get them from the names like this:

  1. projectContext.Load(projectContext.Projects, ps => ps.Include(p => p.Id, p => p.Name));
  2. projectContext.Load(projectContext.CustomFields, cfs => cfs.Include(cf => cf.InternalName, cf => cf.Name));
  3. projectContext.ExecuteQuery();
  4.  
  5. var projId = projectContext.Projects.First(p => p.Name == "Your Project Name").Id;
  6. var cfInternalName = projectContext.CustomFields.First(cf => cf.Name == "NameOfTheField").InternalName;

Note, how much this solution is simpler than the PSI-alternative.

September 29, 2014

Importing multi-level Lookup Tables using PowerShell

Filed under: ALM, PowerShell, Project Server, PSI — Tags: , , , — Peter Holpar @ 21:54

Recently I’m working quite a lot with Project Server 2013. My tasks include – beyond development – creation of methods that supports the continuous delivery of the results from the development environment to the test and production environments. I found that my old friend, PowerShell is an invaluable tool in this field as well.

Recently I had to solve a problem, where we had a rather complex, multi-level lookup table (RBS) on the development server, and we had to transfer the same structure on each deployment to the test server. Typing the same structure via the UI each time would have been a very boring and time consuming activity.

If we export the structure via the UI to Excel,

image

the result looks like this:

image

However, when we try to paste the values to the lookup list via the UI, the fields are shifted to the right: the values in the Level field become to the values of the Value field, the Value becomes to the Description, and the original Description is lost, making the whole pasting worthless.

image

I found a very useful PowerShell script on the blog of Paul Mather (the code is available in the TechNet Script Center as well). This script utilizes the PSI interface, however is limited to a single level of values, no hierarchical lookup tables.

I’ve extended the sample using the generic Stack object of the .NET Framework, pushing and popping the Guids of the parent items, and importing the value of the Description field as well. Otherwise most of the code was borrowed from, and the functionality is identical to the original version of Paul. As input file, a TAB separated text file is used without field name headers, including the Level, Value and Description fields, in the case above, for example:

1    Value 1    Desc 1
2    Value 1_1    Desc 1.1
3    Value 1_1_1    Desc 1.1.1
2    Value 1_2    Desc 1.2
3    Value 1_2_1    Desc 1.2.1
2    Value 1_3    Desc 1.3

This sample is limited to lookup tables with character-based code sequences.

The PowerShell script that enables the mulit-level import:

  1. #Get lookup table values to add
  2. $values = Get-Content "C:\Data\PowerShell\RBSValues.txt"
  3.  
  4. #Specify Lookup table to update
  5. $lookupTableName = "RBS"
  6. $lcid = 1033
  7. $emptyString = [String]::empty
  8. $svcPSProxy = New-WebServiceProxy -Uri "http://sp2013/pwa/_vti_bin/PSI/LookupTable.asmx?wsdl" -UseDefaultCredential
  9. $lookupTableGuid = ($svcPSProxy.ReadLookupTables($emptyString, 0, $lcid).LookupTables  | ? {$_.LT_NAME -eq $lookupTableName }).LT_UID
  10. $lookupTable = $svcPSProxy.ReadLookupTablesbyUids($lookupTableGuid, 1, $lcid)
  11. #get lookup table count
  12. $lookuptableValues = $svcPSProxy.ReadLookupTablesbyUids($lookupTableGuid, 0, $lcid).LookupTableTrees
  13. $count = $lookuptableValues.Count + 1
  14. #update lookup table…
  15. $stack = New-Object System.Collections.Generic.Stack[Guid]
  16. $lastLevel = 1
  17.  
  18. $values | % {
  19.     $fields = $_ -split '\t+'
  20.         $level = $fields[0]
  21.         $text = $fields[1]
  22.         $desc = $fields[2]
  23.  
  24.     $guid = [Guid]::NewGuid()
  25.     # Write-Host Count: $count, text: $text, Guid: $guid, Level: $level, Last level: $lastLevel
  26.     $parentGuid = $lastGuid
  27.     If ($lastLevel -lt $level) {
  28.         $stack.Push($lastGuid)
  29.         # Write-Host Parent GUID Pushed: $parentGuid
  30.     }
  31.     Else {
  32.         While (($stack.Count -ge ($level)) -and ($stack.Count -gt 1)) {
  33.             # Write-Host Popping level ($stack.Count + 1)
  34.             $parentGuid = $stack.Pop()
  35.             # Write-Host Parent GUID Popped: $parentGuid
  36.         }
  37.         If ($stack.Count -gt 0) {
  38.             $parentGuid = $stack.Peek()
  39.             # Write-Host Parent GUID Peeked: $parentGuid
  40.         }
  41.     }
  42.  
  43.  
  44.     $LookupRow = $lookuptable.LookupTableTrees.NewLookupTableTreesRow()
  45.     If (-Not [String]::IsNullOrEmpty($desc)) {
  46.         $LookupRow.LT_VALUE_DESC = $desc
  47.     }
  48.     $LookupRow.LT_STRUCT_UID = $guid
  49.     $LookupRow.LT_UID = $lookupTableGuid
  50.     $LookupRow.LT_VALUE_TEXT = $text
  51.     If ($level -gt 1) {
  52.         # Write-Host Parent GUID set: $parentGuid
  53.         $LookupRow.LT_PARENT_STRUCT_UID = $parentGuid
  54.     }
  55.     $LookupRow.LT_VALUE_SORT_INDEX =  ($count++)
  56.     $lookuptable.LookupTableTrees.AddLookupTableTreesRow($LookupRow)
  57.  
  58.     $lastGuid = $guid
  59.     $lastLevel = $level
  60. }
  61.  
  62. $Error.Clear()
  63. Try
  64.     {
  65.         $svcPSProxy.UpdateLookupTables($lookuptable , 0 , 1 , $lcid)
  66.     }
  67. Catch
  68.     {
  69.         Write-Host "Error updating the Lookup table, see the error below:" -ForeGroundColor Red -BackGroundColor White
  70.         Write-Host "$error" -ForeGroundColor Red
  71.     }
  72. If ($Error.Count -eq 0)
  73.     {
  74.         Write-Host "The lookup table $lookupTablename has been updated with the values from the text file specified" -ForeGroundColor Green
  75.     }
  76. Else
  77.     {
  78.         Write-Host "The lookup table $lookupTablename has not been updated with the values from the text file specified, please see error" -ForeGroundColor Red -BackGroundColor White
  79.     }
  80. #force checkin in case of failure
  81. $Error.Clear()
  82. Try
  83.     {
  84.      $svcPSProxy.CheckInLookUpTables($lookupTableGuid, 1)
  85.     }
  86. Catch
  87.     {
  88.         If ($error -match "LastError=CICONotCheckedOut")
  89.             {
  90.     
  91.             }
  92.         Else
  93.         {
  94.             Write-Host "Error checking the Lookup table, see the error below:" -ForeGroundColor Red -BackGroundColor White
  95.             Write-Host "$error" -ForeGroundColor Red
  96.         }
  97.     }

The script includes a lot of Write-Host cmdlets to enable tracking of the process. These are commented in the version above. You are free to either use or delete these lines as you wish.

Note: Don’t forget to alter the file path, the URI and the lookup table name, and the LCID as well, if you are working with a non-English version of PWA.

August 31, 2014

How to use PowerShell to check if a SharePoint Group with a specified ID or name exists–Without error handling

Filed under: PowerShell, SP 2013, Tips & Tricks — Tags: , , — Peter Holpar @ 23:18

Recently I created a PowerShell script that should delete a group that has a specific name. If the script runs the second time, it throws an exception since the group is already deleted.

If you want to get/delete/add a group from/to a SPGroupCollection (like SiteGroups or Groups of an SPWeb) the methods throw typically exceptions of different kinds if the group does not exist (or already does exist in case of addition):

$web.SiteGroups.Remove(12345) throws
Specified argument was out of the range of valid values.

$web.SiteGroups.Remove("YourGroupName") throws
Group cannot be found.

$web.SiteGroups.GetByID(12345) and
$web.SiteGroups.GetByName("YourGroupName") throw
Group cannot be found.

$web.SiteGroups.Add("YourGroupName", $usr, $null, "Group description") throws
The specified name is already in use.

I wanted to eliminate the error messages. If these commands were PowerShell Cmdlets, we could use the common ErrorAction parameter with the value SilentlyContinue (see more here), however with standard .NET object calls only the Try/Catch block would be available.

Throwing and handling exceptions has always a performance penalty. How could we check if the group exists before trying to get/delete/add it from/to the collection?

After a short search on the .NET based samples I found:

  • A generic- and lambda expression-based solution, that is nice, but not easy to transfer to PowerShell.
  • An interesting solution, that uses the Xml property of the SPGroupCollection object .
  • A solution that is based on the GetCollection method of the SPGroupCollection object.

I chose the third sample to transfer to PowerShell. The equivalent PowerShell condition to check the group by ID:

@($web.SiteGroups.GetCollection(@(12345))).Count -eq 1

To check the group by name, we can use:

@($web.SiteGroups.GetCollection(@("YourGroupName"))).Count -eq 1

The parameter of the GetCollection method is an array, so we can use the same method to check if all or any of multiple groups exists.

For example, to check by ID if both of the groups we need exist:

@($web.SiteGroups.GetCollection(@(12345, 54321))).Count -eq 2

To check by name if any of the groups we need exists:

@($web.SiteGroups.GetCollection(@("YourGroupName1", "YourGroupName2"))).Count –gt 0

July 22, 2014

How to validate file names of attachments on SharePoint forms

Filed under: Attachments, jQuery, SP 2010 — Tags: , , — Peter Holpar @ 18:36

A few months ago I already posted a solution to check duplicated attachment names on SharePoint forms. Another common problem source are invalid file names.

For example, if you have a standard list item edit form, and would like to upload an attachment with invalid file name (for example, invalid characters or exceeding length limitation), on submitting the form you receive an exception. Despite of the error, the item itself is saved, however, if you appended other files (with valid names) as well, the ones you appended after the attachment having invalid name are not saved to the item.

The error message you receive if the file name contains invalid characters:

The file or folder name contains characters that are not permitted.  Please use a different name.<nativehr>0x81020073</nativehr><nativestack></nativestack>

The stack trace:

[COMException (0x81020073): The file or folder name contains characters that are not permitted.  Please use a different name.<nativehr>0x81020073</nativehr><nativestack></nativestack>]
   Microsoft.SharePoint.Library.SPRequestInternalClass.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish, String bstrFileName, ISP2DSafeArrayWriter pListDataValidationCallback, ISP2DSafeArrayWriter pRestrictInsertCallback, ISP2DSafeArrayWriter pUniqueFieldCallback) +0
   Microsoft.SharePoint.Library.SPRequest.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish, String bstrFileName, ISP2DSafeArrayWriter pListDataValidationCallback, ISP2DSafeArrayWriter pRestrictInsertCallback, ISP2DSafeArrayWriter pUniqueFieldCallback) +406

[SPException: The file or folder name contains characters that are not permitted.  Please use a different name.]
   Microsoft.SharePoint.SPGlobal.HandleComException(COMException comEx) +27609826
   Microsoft.SharePoint.Library.SPRequest.AddOrUpdateItem(String bstrUrl, String bstrListName, Boolean bAdd, Boolean bSystemUpdate, Boolean bPreserveItemVersion, Boolean bUpdateNoVersion, Int32& plID, String& pbstrGuid, Guid pbstrNewDocId, Boolean bHasNewDocId, String bstrVersion, Object& pvarAttachmentNames, Object& pvarAttachmentContents, Object& pvarProperties, Boolean bCheckOut, Boolean bCheckin, Boolean bMigration, Boolean bPublish, String bstrFileName, ISP2DSafeArrayWriter pListDataValidationCallback, ISP2DSafeArrayWriter pRestrictInsertCallback, ISP2DSafeArrayWriter pUniqueFieldCallback) +28003823
   Microsoft.SharePoint.SPListItem.AddOrUpdateItem(Boolean bAdd, Boolean bSystem, Boolean bPreserveItemVersion, Boolean bNoVersion, Boolean bMigration, Boolean bPublish, Boolean bCheckOut, Boolean bCheckin, Guid newGuidOnAdd, Int32& ulID, Object& objAttachmentNames, Object& objAttachmentContents, Boolean suppressAfterEvents, String filename) +26729805
   Microsoft.SharePoint.SPListItem.UpdateInternal(Boolean bSystem, Boolean bPreserveItemVersion, Guid newGuidOnAdd, Boolean bMigration, Boolean bPublish, Boolean bNoVersion, Boolean bCheckOut, Boolean bCheckin, Boolean suppressAfterEvents, String filename) +26726414
   Microsoft.SharePoint.SPListItem.Update() +161
   Microsoft.SharePoint.WebControls.SaveButton.SaveItem(SPContext itemContext, Boolean uploadMode, String checkInComment) +848
   Microsoft.SharePoint.WebControls.SaveButton.OnBubbleEvent(Object source, EventArgs e) +1315
   System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +70
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +29
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2981

image

The error message you receive if the file name is too long:

The specified file or folder name is too long. The URL path for all files and folders must be 260 characters or less (and no more than 128 characters for any single file or folder name in the URL). Please type a shorter file or folder name.<nativehr>0x800700ce</nativehr><nativestack></nativestack>

image

The issue is even more annoying if you have an edit form with some kind of custom logic behind it. For example, in one of our applications, there is a custom approval workflow attached to the list that is started automatically after the item is saved. If the file name is invalid, the workflow starts without the (probably for the approval important) attachment. It doesn’t really help customer satisfaction, I shouldn’t say.

How could we validate attachment file names and prohibit attachments having invalid names? We can use the same method as described in my former post. We should update our onAttachOKbuttonClicked method with the file name validations. The new version, including the duplicate check as well as file name length and special character validation:

  1. $(document).ready(attachEventHandlers);
  2.  
  3. function attachEventHandlers() {
  4.     // override the default event handler with our custom method
  5.     $('#attachOKbutton').attr("onclick", "onAttachOKbuttonClicked()");
  6. }
  7.  
  8. function onAttachOKbuttonClicked() {
  9.     var newFilePath = $('#attachmentsOnClient').find('input').last().val();
  10.     // get the file name from the file path as described at
  11.     // http://stackoverflow.com/questions/423376/how-to-get-the-file-name-from-a-full-path-using-javascript
  12.     // TrimWhiteSpaces is a js method of SharePoint to filter out special characters from the file name
  13.     var newFileName = TrimWhiteSpaces(newFilePath).replace(/^.*[\\\/]/, '');
  14.  
  15.     var maxFileNameLength = 128;
  16.  
  17.     // Information about the characters that you cannot use in site names, folder names, and file names in SharePoint
  18.     // http://support.microsoft.com/kb/905231
  19.     // http://www.sysadminsblog.com/microsoft/file-name-length-and-character-restrictions-for-sharepoint/
  20.     // ivalid characters in SP:
  21.     // ~#%&*{}\:<>?/+|"
  22.     // ivalid characters in file system:
  23.     // \/:*?"<>|
  24.     // we have to check only:
  25.     // ~#%&{}+|
  26.     /*
  27.     Cant be longer than 128 characters
  28.     Cant use: ~ # % & * { } \ : < > ? / + | "; RegExp: [~#%\&{}+\|] – do not include characters that are not allowed in the file system
  29.     Cant use the period character consecutively in the middle of a file name (blahblah.docx); RegExp: \\.\\.
  30.     Cant use the period character at the end of a file name; RegExp:  ^\\.
  31.     Cant use the period character at the start of a file name; RegExp:  \\.$    
  32.     */
  33.  
  34.     var match = (new RegExp('[~#%\&{}+\|]|\\.\\.|^\\.|\\.$')).test(newFileName);
  35.     if (match) {
  36.         alert("Ivalid file name. The name of the attached file contains invalid characters.");
  37.     }
  38.     else if (newFileName.length > maxFileNameLength) {
  39.         alert("Ivalid file name. The name of the attached file is too long.");
  40.     }
  41.     else {
  42.         // it is the same duplicate check code from former post (http://pholpar.wordpress.com/2014/03/12/how-to-check-for-duplicated-attachments-on-sharepoint-forms/)
  43.         var foundDuplication = false;
  44.  
  45.         $('#idAttachmentsTable').find('tbody').find('tr').each(function () {
  46.             var existingFileName = $(this).find('.ms-vb').find('a').text();
  47.             // if the existingFileName is empty then the attachment was uploaded in this session
  48.             // that is, it is not saved yet
  49.             if (existingFileName == '') {
  50.                 var existingFilePath = $(this).find('.ms-vb').find('span').text();
  51.                 existingFileName = existingFilePath.replace(/^.*[\\\/]/, '');
  52.             }
  53.  
  54.             if (newFileName == existingFileName) {
  55.                 foundDuplication = true;
  56.                 return false;
  57.             }
  58.         });
  59.  
  60.         if (foundDuplication) {
  61.             alert("A file with name '" + newFileName + "' is already attached to this item.");
  62.         }
  63.         else {
  64.             // call the OkAttach js method of SharePoint
  65.             // this is the method that is originally called by uploading attachments
  66.             OkAttach();
  67.         }
  68.  
  69.     }
  70. }

Note: I check only special characters that are supported in the NTFS file system, but not supported in SharePoint file names. I don’t check if the file name contains officially forbidden strings (.files, –Dateien, etc.). Reason is that up to now I had no issues with such files.

Including this method on the form helps us to prevent submitting the form with invalid attachments, and let the user to fix potential issues around attachment file names before saving the item.

June 25, 2014

debugging issues and SSL Failure caused by a DNS-Configuration Mistake

Filed under: SP 2010 — Tags: — Peter Holpar @ 22:55

Recently we had two issues in one of our development environments, that were rather weird at first sight.

In the farm there are three web applications (AppA, AppB and AppC), all of them accessible only via HTTPS. AppA and AppB share the same (non-wildcard) SSL certificate, AppC has its own one. All of the applications has its own IP address (IP_A, IP_B, IP_C) as we cannot use host headers with HTTPS in this case, and its own application pool (AppPoolA, AppPoolB and AppPoolC) assigned in IIS.

Symptoms:

1. We want to debug our custom application pages used in AppA. After an IISRESET we open the page using the URL of AppA in the browser, however, instead of the process of AppPoolA, the process of AppPoolB is started (we can see it in Task Manager), although the content of the pages was rendered in the browser using the context of AppA. If we try to deploy the solution from Visual Studio, and would like to automatically attach the debugger to the corresponding web application (AppA), for example using CKSDev, the breakpoints remain inactive. If we attach the debugger manually to the process of AppPoolB, we can debug the pages, and SPContext.Current reflects the correct values in context of AppA. This behavior is not always reproducible, about in 10-20% of the cases we can debug the pages “normally”, via the process of AppPoolA.

2. When accessing AppA in the browser, about in 60-70% of the cases we receive a security warning, that the certificate was issued to an other URL. When checking the certificate, we see, that the server tries to build up the HTTPS connection using the certificate assigned to AppC. If we ignored the warning, content of AppA was displayed.

Reason:

Analyzing the IIS and SharePoint Server settings and SSL certificate / IP bindings I found no errors. However, when PINGing the Fully Qualified Domain Name (FQDN) of AppA, I saw, that the host name of the front end server is displayed, and not always the IP address of AppA (IP_A) are resolved, but quite frequently the IP of AppB and AppC (IP_B, IP_C) as well. Using NSLOOKUP I realized that all of these IPs are assigned to the FQDN of AppA. The host name was resolved to IP addresses randomly in a round-robin fashion by DNS. The AppB FQDN -  IP_B, AppC FQDN – IP_C assignments were correctly configured.

Since the request was sent to the incorrect IP address, it was routed to an other virtual server at the IIS level, this is why the other application pool was started and the other SSL certificate was returned. However, as all of the sites were bound to SharePoint web applications, at the SharePoint level the correct target web application was recognized based on the URL of the request, so the content was rendered as expected, and not based on the content / in the context of the other web applications.

You should be able to reproduce this behavior if you have more SharePoint web applications bound to the same port number but different IPs without host headers, if you set the incorrect IP address in the hosts file locally, and do not let the browser to use a proxy server for accessing the sites.

Solution:

Discussing the issue with the network group it turned out, that FQDNs of the web applications were configured as CNAME records in DNS, referring to the host name of the front end server. Deleting the faulty CNAME assignments in DNS has solved both of the above described symptoms.

How to restrict the properties of a single object in the results of Client Object Model requests sent from PowerShell on the server side

Filed under: Managed Client OM, PowerShell, Reflection, SP 2013 — Tags: , , , — Peter Holpar @ 21:49

In my recent posts I illustrated how can we restrict (either using expression trees or via dynamically compiled assemblies) on the server side the properties of entity collections returned by the client object model requests when using PowerShell. Although it has less effect on the network traffic, it may be interesting to see, how can we achieve a similar result when working with single objects (like a single list instance) instead of entity collections (for example, fields of a list).

A query without property restrictions might look like this in C#:

ClientContext ctx = new ClientContext("http://sp2013&quot;);
var list = ctx.Web.Lists.GetByTitle("Images");
ctx.Load(list);
ctx.ExecuteQuery();

The request contains no property restriction (see SelectAllProperties=true below),

image

and the response contains indeed dozens of properties.

image

In C# it is quite simple to specify using lambda expressions, which properties of the list we need:

ClientContext ctx = new ClientContext("http://sp2013&quot;);
var list = ctx.Web.Lists.GetByTitle("Images");
ctx.Load(list, l => l.Title, l => l.ItemCount);
ctx.ExecuteQuery();

The request will include the limitation,

image

and only the requested properties are returned in the response:

image

But how to achieve the same result from PowerShell?

Again, we can easily refactor the code with the lambda expressions into a static helper class:

public static class QueryHelper
{
    public static void LoadListWithLimtedFields(ClientContext ctx, List list)
    {
        ctx.Load(list, l => l.Title, l => l.ItemCount);
    }
}

In this case, the original method contains only the code below that is easy to translate to PowerShell:

ClientContext ctx = new ClientContext("http://sp2013&quot;);
var list = ctx.Web.Lists.GetByTitle("Images");
QueryHelper.LoadListWithLimtedFields(ctx, list);
ctx.ExecuteQuery();

The same functionality transformed to PowerShell is shown below:

  1. $url = "http://sp2013&quot;
  2.  
  3. $referencedAssemblies = (
  4.     "Microsoft.SharePoint.Client, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  5.     "Microsoft.SharePoint.Client.Runtime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  6.     "System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
  7. $sourceCode = @"
  8. using Microsoft.SharePoint.Client;
  9. using System.Collections.Generic;
  10. using System.Linq;
  11.  
  12. public static class QueryHelper
  13. {
  14.     public static void LoadListWithLimtedFields(ClientContext ctx, List list)
  15.     {
  16.         ctx.Load(list, l => l.Title, l => l.ItemCount);
  17.     }
  18. }
  19. "@
  20.  
  21. Add-Type -ReferencedAssemblies $referencedAssemblies -TypeDefinition $sourceCode -Language CSharp;
  22. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  23. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  24.  
  25. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  26. $list = $ctx.Web.Lists.GetByTitle("Images")
  27. [QueryHelper]::LoadListWithLimtedFields($ctx, $list)
  28. $ctx.ExecuteQuery()
  29.  
  30. Write-Host $list.Title, $list.ItemCount

Although more complex as the former solution, and recommended only for the hard-core developers, we can achieve the same result using expression trees built using Reflection. Let’s see how Visual Studio compiles the lambda expressions in our C# code into plain .NET objects and method calls. We have to build the code and open the assembly with a decompiler tool.

The original code as decompiled by JetBrains dotPeek:

ClientContext clientContext = new ClientContext("http://sp2013&quot;);
List byTitle = clientContext.Web.Lists.GetByTitle("Images");
clientContext.Load<List>(byTitle, new Expression<Func<List, object>>[2]
{
  (Expression<Func<List, object>>) (l => l.Title),
  (Expression<Func<List, object>>) (l => (object) l.ItemCount)
});
clientContext.ExecuteQuery();

The original code as decompiled by Reflector:

ParameterExpression CS$0$0001;
ClientContext ctx = new ClientContext("http://sp2013&quot;);
List list = ctx.Web.Lists.GetByTitle("Images");
ctx.Load<List>(list, new Expression<Func<List, object>>[] { Expression.Lambda<Func<List, object>>(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(List), "l"), (MethodInfo) methodof(List.get_Title)), new ParameterExpression[] { CS$0$0001 }), Expression.Lambda<Func<List, object>>(Expression.Convert(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(List), "l"), (MethodInfo) methodof(List.get_ItemCount)), typeof(object)), new ParameterExpression[] { CS$0$0001 }) });
ctx.ExecuteQuery();

The decompiled code helps us to create the same expression tree from PowerShell using Reflection:

  1. $url = "http://sp2013&quot;
  2.  
  3. # load the required client object model assemblies
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  5. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6.  
  7. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  8. $list = $ctx.Web.Lists.GetByTitle("Images")
  9.  
  10. $expressionType = [System.Linq.Expressions.Expression]
  11. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  12.  
  13. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  14. $lambdaMethodGeneric = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.List,System.Object]])
  15.  
  16. $listType = [Microsoft.SharePoint.Client.List]
  17.  
  18. # query the Title propery of type System.String
  19. $param1 = [System.Linq.Expressions.Expression]::Parameter($listType, "l")
  20. $name1 = [System.Linq.Expressions.Expression]::Property($param1, "Title")
  21. $expression1 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($name1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
  22.  
  23. # query the ItemCount of type System.Int32
  24. $param2 = [System.Linq.Expressions.Expression]::Parameter($listType, "l")
  25. $name2 = [System.Linq.Expressions.Expression]::Property($param2, "ItemCount")
  26.  
  27. # convert the Int32 type to object
  28. $body2 = [System.Linq.Expressions.Expression]::Convert($name2, [System.Object])
  29.  
  30. $expression2 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($body2, [System.Linq.Expressions.ParameterExpression[]] @($param2)))
  31.  
  32. $loadMethod = [Microsoft.SharePoint.Client.ClientRuntimeContext].GetMethod("Load")
  33. $loadMethodGeneric = $loadMethod.MakeGenericMethod([Microsoft.SharePoint.Client.List])
  34.  
  35. # call Load with both of the expressions
  36. $loadMethodGeneric.Invoke($ctx, [System.Object[]] @($list, [System.Linq.Expressions.Expression`1[System.Func“2[Microsoft.SharePoint.Client.List,System.Object]][]] @($expression1, $expression2)))
  37.  
  38. $ctx.ExecuteQuery()
  39.  
  40. Write-Host $list.Title, $list.ItemCount

Using the methods described in this and in the former posts we can achieve the same network traffic limiting results from PowerShell as we got used to in the case of the C# code.

June 17, 2014

How to restrict the properties and Filter the results of Client Object Model requests sent from PowerShell – The alternative solution without expression trees

Filed under: Managed Client OM, PowerShell, SP 2013 — Tags: , , — Peter Holpar @ 23:24

Recently I blogged about how to implement the Where and Include functionality of the Client Object Model requests sent from PowerShell. That is great, however I don’t really like the idea to create Expression trees from scratch manually and call a lot of generic methods using Reflection to achieve this goal.

Isn’t there a simpler solution out there? Yes, there is.

In the solution illustrated below I split the functionality into two separate components. I create a static helper class that includes all of the linguistic features that are problematic to achieve form the standard PowerShell toolset.

Note: The sample below includes the Take function beyond the Where and Include methods, so only the first matching child element will be returned.

  1. public static class QueryHelper
  2. {
  3.     public static IQueryable<Field> FilterFields(this FieldCollection fields)
  4.     {
  5.         return fields.Where(f => f.TypeAsString == "Guid").Take(1).Include(f => f.Id, f => f.InternalName);
  6.     }
  7. }

The second component calls the helper method introduced above. This code contains only simple client object model features, all of the available via simple PowerShell method calls.

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3. var fieldsQuery = list.Fields;
  4. var fields = ctx.LoadQuery(QueryHelper.FilterFields(fieldsQuery));
  5. ctx.ExecuteQuery();

In the next step we translate the second component to PowerShell, however we leave the C# code for the first part. From the C# code we build a temporary assembly in runtime, and call the helper method from PowerShell.

I’ve created two alternative solutions, in the first one, the code is compiled via the native Add-Type PowerShell cmdlet:

  1. $url = "http://sp2013&quot;
  2.  
  3. $referencedAssemblies = (
  4.     "Microsoft.SharePoint.Client, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  5.     "Microsoft.SharePoint.Client.Runtime, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c",
  6.     "System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089")
  7. $sourceCode = @"
  8. using Microsoft.SharePoint.Client;
  9. using System.Collections.Generic;
  10. using System.Linq;
  11. public static class QueryHelper
  12. {
  13.   public static IQueryable<Field> FilterFields(this FieldCollection fields)
  14.   {
  15.     return fields.Where(f => f.TypeAsString == "Guid").Take(1).Include(f => f.Id, f => f.InternalName);
  16.   }
  17. }
  18. "@
  19.  
  20. Add-Type -ReferencedAssemblies $referencedAssemblies -TypeDefinition $sourceCode -Language CSharp;
  21. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  22. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  23.  
  24. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  25. $listFields = $ctx.Web.Lists.GetByTitle("Images").Fields
  26. $fieldQuery = [QueryHelper]::FilterFields($listFields)
  27. $fields = $ctx.LoadQuery($fieldQuery)
  28. $ctx.ExecuteQuery()
  29.  
  30. $fields | % { Write-Host $_.InternalName: $_.Id }

In the second one, the code is compiled directly via the CompileAssemblyFromSource method of the CSharpCodeProvider class:

  1. $url = "http://sp2013&quot;
  2.  
  3. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  5.  
  6. $sourceCode = @"
  7. using Microsoft.SharePoint.Client;
  8. using System.Collections.Generic;
  9. using System.Linq;
  10. public static class QueryHelper
  11. {
  12.   public static IQueryable<Field> FilterFields(this FieldCollection fields)
  13.   {
  14.     return fields.Where(f => f.TypeAsString == "Guid").Take(1).Include(f => f.Id, f => f.InternalName);
  15.   }
  16. }
  17. "@
  18.  
  19. $parameters = New-Object System.CodeDom.Compiler.CompilerParameters
  20. $runtimeCompiler = New-Object Microsoft.CSharp.CSharpCodeProvider
  21. # supress command output by casting the command to void
  22. [void]$parameters.ReferencedAssemblies.Add("System.Core.dll");
  23. [void]$parameters.ReferencedAssemblies.Add("C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll");
  24. [void]$parameters.ReferencedAssemblies.Add("C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll");
  25. $parameters.GenerateExecutable = $False
  26. $parameters.GenerateInMemory = $True
  27. $parameters.IncludeDebugInformation = $False
  28. $errCount = $compRes.Errors.Count
  29. $compRes = $runtimeCompiler.CompileAssemblyFromSource($parameters, $sourceCode)
  30. Write-Host Build error count: $errCount
  31.  
  32. If ($errCount -eq 0)
  33. {
  34.   $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  35.   $listFields = $ctx.Web.Lists.GetByTitle("Images").Fields
  36.   $fieldQuery = [QueryHelper]::FilterFields($listFields)
  37.   $fields = $ctx.LoadQuery($fieldQuery)
  38.   $ctx.ExecuteQuery()
  39.  
  40. $fields | % { $_.InternalName }
  41. }

The request (as checked using Fiddler) now includes the necessary filters,

image

and the response includes only a single entry, and the two public properties (Id and InternalName) we requested.

image

The output on the console:

image

Note: Based on my experience, the temporary assemblies are cached in the PowerShell process. That means if you alter the C# code, you should restart the PowerShell console to let the changes get reflected.

Conclusion: I prefer this approach to the former ones, because we can write the lambda expressions as we got used to in C#. There is no need to write overcomplicated Expression method calls.

June 15, 2014

How to restrict the properties in the results of Client Object Model requests sent from PowerShell on the server side, implementing the Include method

Filed under: Managed Client OM, PowerShell, Reflection, SP 2013 — Tags: , , , — Peter Holpar @ 22:51

In my recent post I illustrated how one can limit the entities returned by the server when responding a Client Object Model request sent from PowerShell. Although that method restricts the entities to the ones we really need, it still returns all of the properties for those items.

See the SelectAllProperties = true attribute of the ChildItemQuery in the request captured by Fiddler:

image

And the response includes really all of the properties…

image

However, we need typically only a few properties, the others only increase the size of the response package sent by the server unnecessarily.

In the C# syntax we can limit the fields using the Include method (see Retrieving only specified properties of lists):

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3. var fieldsQuery = list.Fields.Include(f => f.InternalName, f => f.Id);
  4. // var fieldsQuery = list.Fields;
  5. var fields = ctx.LoadQuery(fieldsQuery);
  6. ctx.ExecuteQuery();

Remark: The JavaScript version supports a kind of Include as well (see Retrieving Only Specified Properties of Lists Using JavaScript).

Following the same strategy as in the case of the Where method, we compile our code to an assembly, and decompile it using JetBrains dotPeek:

  1. ClientContext clientContext = new ClientContext("http://sp2013&quot;);
  2. IQueryable<Field> clientObjects = ClientObjectQueryableExtension.Include<Field>((IQueryable<Field>)clientContext.Web.Lists.GetByTitle("Images").Fields, new Expression<Func<Field, object>>[2]
  3.   {
  4.     (Expression<Func<Field, object>>) (f => f.InternalName),
  5.     (Expression<Func<Field, object>>) (f => (object) f.Id)
  6.   });
  7. clientContext.LoadQuery<Field>(clientObjects);
  8. clientContext.ExecuteQuery();

Decompiling the code with Reflector reveals the internals of the lambda expression query:

IQueryable<Field> fieldsQuery = ctx.Web.Lists.GetByTitle("Images").Fields.Include<Field>(new Expression<Func<Field, object>>[] { Expression.Lambda<Func<Field, object>>(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(Field), "f"), (MethodInfo) methodof(Field.get_InternalName)), new ParameterExpression[] { CS$0$0001 }), Expression.Lambda<Func<Field, object>>(Expression.Convert(Expression.Property(CS$0$0001 = Expression.Parameter(typeof(Field), "f"), (MethodInfo) methodof(Field.get_Id)), typeof(object)), new ParameterExpression[] { CS$0$0001 }) });

Transferring this quite complicate expression to a bit more readable format our method looks like this:

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3.  
  4. // query the InternalName propery of type System.String
  5. var param1 = Expression.Parameter(typeof(Field), "f");
  6. var name1 = Expression.Property(param1, "InternalName");
  7.  
  8. var expression1 = Expression.Lambda<Func<Field, object>>(name1, param1);
  9.  
  10. // query the Id of type System.Guid
  11. var param2 = Expression.Parameter(typeof(Field), "f");
  12. var name2 = Expression.Property(param2, "Id");
  13. // convert the Guid type to object
  14. var body2 = Expression.Convert(name2, typeof(object));
  15.  
  16. var expression2 = Expression.Lambda<Func<Field, object>>(body2, param2);
  17.  
  18. // call Include with both of the expressions
  19. var fieldsQuery = ClientObjectQueryableExtension.Include<Field>(list.Fields, expression1, expression2);
  20.  
  21. var fields = ctx.LoadQuery(fieldsQuery);
  22. ctx.ExecuteQuery();

The PowerShell version of the same functionality:

  1. $url = "http://sp2013&quot;
  2.  
  3. # load the required client object model assemblies
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  5. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6.  
  7. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  8. $list = $ctx.Web.Lists.GetByTitle("Images")
  9.  
  10. $expressionType = [System.Linq.Expressions.Expression]
  11. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  12.  
  13. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  14. $lambdaMethodGeneric = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]])
  15.  
  16. $fieldType = [Microsoft.SharePoint.Client.Field]
  17.  
  18. # query the InternalName propery of type System.String
  19. $param1 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  20. $name1 = [System.Linq.Expressions.Expression]::Property($param1, "InternalName")
  21. $expression1 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($name1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
  22.  
  23. # query the Id of type System.Guid
  24. $param2 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  25. $name2 = [System.Linq.Expressions.Expression]::Property($param2, "Id")
  26.  
  27. # convert the Guid type to object
  28. $body2 = [System.Linq.Expressions.Expression]::Convert($name2, [System.Object])
  29.  
  30. $expression2 = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($body2, [System.Linq.Expressions.ParameterExpression[]] @($param2)))
  31.  
  32. $includeMethod = [Microsoft.SharePoint.Client.ClientObjectQueryableExtension].GetMethod("Include")
  33. $includeMethodGeneric = $includeMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  34.  
  35. # call Include with both of the expressions
  36. $fieldsQuery = $includeMethodGeneric.Invoke($Null, [System.Object[]] @($list.Fields, [System.Linq.Expressions.Expression`1[System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]][]] @($expression1, $expression2)))
  37.  
  38. $fields = $ctx.LoadQuery($fieldsQuery)
  39. $ctx.ExecuteQuery()
  40.  
  41. $fields | % { Write-Host $_.InternalName: $_.Id }

Testing the script we found in Fiddler, that the request is limited to the properties we need:

image

and the response contains only the requested properties as well:

image

The output of the script should be similar to this one:

image

Note: In the case above, the child items are not restricted via a QueryableExpression, however you can combine the Where method described in the former post and the Include method to restrict both the child entities and their properties returned by the server:

fields.Where(f => f.TypeAsString == "Guid").Include(f => f.Id, f => f.InternalName)

In the PowerShell equivalent we chain our expression to achieve the same result:

  1. $url = "http://sp2013&quot;
  2.  
  3. # load the required client object model assemblies
  4. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
  5. Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
  6.  
  7. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  8. $list = $ctx.Web.Lists.GetByTitle("Images")
  9.  
  10. $expressionType = [System.Linq.Expressions.Expression]
  11. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  12.  
  13. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  14.  
  15. $fieldType = [Microsoft.SharePoint.Client.Field]
  16.  
  17. # call the Where method first
  18. $lambdaMethodGenericBool = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Boolean]])
  19.  
  20. $param = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  21. $name = [System.Linq.Expressions.Expression]::PropertyOrField($param, "TypeAsString")
  22. $body = [System.Linq.Expressions.Expression]::Equal($name, [System.Linq.Expressions.Expression]::Constant("Guid"))
  23. $expression = $lambdaMethodGenericBool.Invoke($Null, [System.Object[]] @($body, [System.Linq.Expressions.ParameterExpression[]] @($param)))
  24.  
  25. $whereMethod = [System.Linq.Queryable].GetMethods() | ? { $_.Name -eq "Where" -and $_.ToString() -like "*TSource,System.Boolean*" }
  26. $whereMethodGeneric = $whereMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  27.  
  28. $fieldsQuery = $whereMethodGeneric.Invoke($Null, [System.Object[]] @($list.Fields, $expression))
  29.  
  30. # call the Include method next on the result of the Where method
  31. $lambdaMethodGenericObj = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]])
  32.  
  33. # query the InternalName propery of type System.String
  34. $param1 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  35. $name1 = [System.Linq.Expressions.Expression]::Property($param1, "InternalName")
  36. $expression1 = $lambdaMethodGenericObj.Invoke($Null, [System.Object[]] @($name1, [System.Linq.Expressions.ParameterExpression[]] @($param1)))
  37.  
  38. # query the Id of type System.Guid
  39. $param2 = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  40. $name2 = [System.Linq.Expressions.Expression]::Property($param2, "Id")
  41.  
  42. # convert the Guid type to object
  43. $body2 = [System.Linq.Expressions.Expression]::Convert($name2, [System.Object])
  44.  
  45. $expression2 = $lambdaMethodGenericObj.Invoke($Null, [System.Object[]] @($body2, [System.Linq.Expressions.ParameterExpression[]] @($param2)))
  46.  
  47. $includeMethod = [Microsoft.SharePoint.Client.ClientObjectQueryableExtension].GetMethod("Include")
  48. $includeMethodGeneric = $includeMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  49.  
  50. # call Include with both of the expressions
  51. $fieldsQuery2 = $includeMethodGeneric.Invoke($Null, [System.Object[]] @($fieldsQuery, [System.Linq.Expressions.Expression`1[System.Func“2[Microsoft.SharePoint.Client.Field,System.Object]][]] @($expression1, $expression2)))
  52.  
  53. $fields = $ctx.LoadQuery($fieldsQuery2)
  54. $ctx.ExecuteQuery()
  55.  
  56. $fields | % { Write-Host $_.InternalName: $_.Id }

When checking the network traffic using Fiddler we found that both the request

image

and the response fulfills our expectations.

The output of the script contains only the two returned fields due to the filtering on the server side:

image

How to filter the results of Client Object Model requests sent from PowerShell on the server side, implementing the Where method

Filed under: Managed Client OM, PowerShell, Reflection, SP 2013 — Tags: , , , — Peter Holpar @ 00:31

There are cases when we have to use the Managed Client Object Model from PowerShell. One of the most well-known cases is when we would like to manage our Office 365 environment (or access data stored in it) from our PowerShell scripts.

The Managed Client Object Model itself supports the server side filtering of data requested from the client using the Where method (see Applying filters to list retrieval), in contrary to the ECMAScript Client Object Model, where it is possible only via an unsupported workaround.

What it makes easy to use from advanced .NET programming languages like C# are the concepts of static methods, LINQ and lambda expression, all of them is quite far away from the standard PowerShell samples. No surprise, that the examples we can find on the web do not apply this filtering technique at all. If you have a look at this example, you can see, that it first downloads the whole list of site columns over the wire, then filters them on the client side just to get the two columns we need. If we have hundreds of columns, it can be pretty an overkill.

Note: Yes, I know that REST makes it possible (via the $filter query option) to filter the items returned, however it has its own limitation, like the lack of the batch requests – e.g. aggregating requests on the client side and sending them in batches via executeQuery – that is (at least, IMHO) one of the best features of the Client Object Model. Furthermore, I feel REST does not fit so good to the concept of PowerShell as the usage of the Managed Client Object Model.

Since we are able to use the same .NET libraries from PowerShell as from C#, it should be possible to achieve the same filtering result as well. But how to start?

First, let’s see a simple C# sample of a Managed Client Object Model call. In this sample we get the list of those fields of the Images list, that are of type Guid.

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3. var fieldsQuery = list.Fields.Where(f => f.TypeAsString == "Guid");
  4. var fields = ctx.LoadQuery(fieldsQuery);
  5. ctx.ExecuteQuery();

How could we translate this one to a version that (although maybe less readable for us, humans) better reflects the object model and method calls behind the syntax sugar of C# (like static methods, and so on)? If we compile our code into an assembly, and open that assembly with a decompiler tool, like JetBrains dotPeek, the result may help us a step further:

  1. ClientContext clientContext = new ClientContext("http://sp2013&quot;);
  2. IQueryable<Field> clientObjects = Queryable.Where<Field>((IQueryable<Field>)clientContext.Web.Lists.GetByTitle("Images").Fields, (Expression<Func<Field, bool>>)(f => f.TypeAsString == "Guid"));
  3. clientContext.LoadQuery<Field>(clientObjects);
  4. clientContext.ExecuteQuery();

That seems already far better, but what can we do with the lambda expression?

Opening the assembly with another decompiler tool, Reflector reveals further details about the internal working of this functionality:

IQueryable<Field> fieldsQuery = Queryable.Where<Field>(ctx.Web.Lists.GetByTitle("Images").Fields, Expression.Lambda<Func<Field, bool>>(Expression.Equal(Expression.Property(CS$0$0000 = Expression.Parameter(typeof(Field), "f"), (MethodInfo) methodof(Field.get_TypeAsString)), Expression.Constant("Guid", typeof(string)), false, (MethodInfo) methodof(string.op_Equality)), new ParameterExpression[] { CS$0$0000 }));

As this decompiled code suggest, and as learned from this forum answer, we can substitute the lambda expression with an equivalent expression tree built dynamically.

The version we receive after this transformation:

  1. ClientContext ctx = new ClientContext("http://sp2013&quot;);
  2. var list = ctx.Web.Lists.GetByTitle("Images");
  3.  
  4. var param = Expression.Parameter(typeof(Field), "f");
  5. var name = Expression.PropertyOrField(param, "TypeAsString");
  6. var body = Expression.Equal(name, Expression.Constant("Guid"));
  7.  
  8. var expression = Expression.Lambda<Func<Field, bool>>(body, param);
  9. var fieldsQuery = Queryable.Where<Field>(list.Fields, expression);
  10.  
  11. var fields = ctx.LoadQuery(fieldsQuery);
  12. ctx.ExecuteQuery();

All we now have yet to do is to translate the C# code to PowerShell. The translation is not extremely complicated, it is mainly a mechanical process, although includes a lot of Type objects and Reflection calls, and we need a few tricks as well to achieve our goal.

For example, in the code above we use the static generic Lambda method of the Expression class with parameter types Expression and ParameterExpression[]. If we liked to get this method by name and parameter types directly using the GetMethod method of the Type class, we would receive an “Ambiguous match found” exception. The cause is that the Expression class has two methods with the same name: one generic one, and a non-generic version, and the GetMethod method does not support to restrict the filter to only one of these. Probably we can get the right one using the FindMembers method as well, but I found it easier to use the GetMethods method to get all of the methods of the Expression class and filter the results in my PowerShell code. In the code below we filter the methods, first by name, next we restrict the results to generic methods, and take the override that has two parameters, and the second parameter is of type ParameterExpression[]:

$lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }

I had similar problems with the Where method of the Query class. Although this method has only two overrides, and these ones have different parameter signatures, the parameters are rather complicated generic parameters, like Expression<Func<TSource, int, bool>>, so I decided to filter the methods again from my code, taking the methods called Where, and selecting the one that has a textural representation with the right parameter types:

$whereMethod = [System.Linq.Queryable].GetMethods() | ? { $_.Name -eq "Where" -and $_.ToString() -like "*TSource,System.Boolean*" }

The result of the translation is:

  1. $url = "http://sp2013&quot;
  2.  
  3. $ctx = New-Object Microsoft.SharePoint.Client.ClientContext($url)
  4. $list = $ctx.Web.Lists.GetByTitle("Images")
  5.  
  6. $expressionType = [System.Linq.Expressions.Expression]
  7. $parameterExpressionArrayType = [System.Linq.Expressions.ParameterExpression].MakeArrayType()
  8.  
  9. $lambdaMethod = $expressionType.GetMethods() | ? { $_.Name -eq "Lambda" -and $_.IsGenericMethod -and $_.GetParameters().Length -eq 2  -and $_.GetParameters()[1].ParameterType -eq $parameterExpressionArrayType }
  10. $lambdaMethodGeneric = $lambdaMethod.MakeGenericMethod([System.Func“2[Microsoft.SharePoint.Client.Field,System.Boolean]])
  11.  
  12. $fieldType = [Microsoft.SharePoint.Client.Field]
  13. $param = [System.Linq.Expressions.Expression]::Parameter($fieldType, "f")
  14. $name = [System.Linq.Expressions.Expression]::PropertyOrField($param, "TypeAsString")
  15. $body = [System.Linq.Expressions.Expression]::Equal($name, [System.Linq.Expressions.Expression]::Constant("Guid"))
  16. $expression = $lambdaMethodGeneric.Invoke($Null, [System.Object[]] @($body, [System.Linq.Expressions.ParameterExpression[]] @($param)))
  17.  
  18. $whereMethod = [System.Linq.Queryable].GetMethods() | ? { $_.Name -eq "Where" -and $_.ToString() -like "*TSource,System.Boolean*" }
  19. $whereMethodGeneric = $whereMethod.MakeGenericMethod([Microsoft.SharePoint.Client.Field])
  20.  
  21. $fieldsQuery = $whereMethodGeneric.Invoke($Null, [System.Object[]] @($list.Fields, $expression))
  22.  
  23. #$whereMethod = [System.Linq.Queryable].GetMethod("Where", [System.Type[]] @([System.Linq.IQueryable[TSource]], [System.Linq.Expressions.Expression[System.Func[TSource,bool]]]))
  24.  
  25. $fields = $ctx.LoadQuery($fieldsQuery)
  26. $ctx.ExecuteQuery()

Note: Before using this code, you should load the necessary client object model assemblies via:

Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"

When validating by Fiddler, we can see that the request contains the filter we need:

image

And when checking the result, only the matching fields are returned, and all without filtering the results on the client side.

image

Conclusion: I don’t want to suggest at all that you should always use this kind of filtering when requesting data from the server. It may be unnecessary or even inefficient if you have only a few or a few dozens of entities to return from the server. However, if it is really a large number of items, it may be useful to know how to get only the entities you really need.

Note: I’ve tested my solution with SharePoint 2013 on-premise, but it should work with SharePoint 2010 and Office 365 as well. You can however apply this technique not only to the SharePoint object model as well. For example, I used it first with the Project Server 2013 Client Object Model.

June 11, 2014

How to filter the results of JSCOM requests on the server side

Filed under: ECMAScript Client Object Model, SP 2013 — Tags: , — Peter Holpar @ 22:32

In the past I already discussed the internals of the Managed Client Object Model and the ECMAScript Object Model (a.k.a. JSCOM) several times. In this post I illustrate an unofficial way of extending the JSCOM.

WARNING: As I wrote above, the method I describe here is totally unofficial and unsupported. It’s only an experiment that depends on unpublished features of the JSCOM, like internal functions that can be changed or even removed without prior notice. Please, take my words seriously, and do not apply this method in a productive environment.

Assuming you are working with the Managed Client Object Model, you can submit queries, that are evaluated on the server side, and return only results that fulfill the conditions. For example, the request below returns only the filterable, non-hidden fields of a list (sample taken from MSDN):

clientContext.Load(oList,
    list => list.Fields.Where(
    field => field.Hidden == false
        && field.Filterable == true));
clientContext.ExecuteQuery();

The most important benefit of this syntax is that you can reduce the network traffic caused by returning irrelevant data (in this case fields) that we would have to filter on the client side without this feature.

Unfortunately, this feature seems to be missing in the ECMAScript Object Model, the Include keyword supports only limiting the members of the object to be returned (the query below returns only the Title and Id properties of each lists), but does not help to filter the results at all (e.g. you can not filter lists based on their properties).

clientContext.load(collList, ‘Include(Title, Id)’);

Since I’m working recently quite a lot with JSCOM (mainly in context of Project Server 2013), I was curious what happens when we use this feature from the Managed Client Object Model, and how we could inject the functionality into JSCOM.

Note: Yes, I know that REST makes it possible (via the $filter query option) to filter the items returned, however it has its own limitation, like the lack of the batch requests – e.g. aggregating requests on the client side and sending them in batches via executeQueryAsync – that is (at least, IMHO) one of the best features of JSCOM.

So I’ve created a simple .NET console application that submits the following query (first without, next with filtering) to the server:

ClientContext ctx = new ClientContext(“http://sp2013”);
// without filtering
// var listQuery = ctx.Web.Lists;
// with filtering
var listQuery = ctx.Web.Lists.Where(l => l.Title == "Images");
var list = ctx.LoadQuery(listQuery);
ctx.ExecuteQuery();

and captured the network traffic using Fiddler in both cases. The screenshot below highlights the difference between the two queries:

image

The equivalent version of the no-filter query in JavaScript:

  1. var context = SP.ClientContext.get_current();
  2. var lists = context.get_web().get_lists();
  3.  
  4. $(document).ready(function () {
  5.     getList();
  6. });
  7.  
  8. function getList() {
  9.     context.load(lists);
  10.     context.executeQueryAsync(onGetListSuccess, onGetListFail);
  11. }

As in the case of the .NET version, this query submits the request without the QueryableExpression. If we want to enable filtering in case of JSCOM, we should first find where the Query part of the request is assembled. Using a simple search in the .js files shows that this task is performed in the SP.ClientQueryInternal.prototype.$2s_1 method in the SP.Runtime.js.

The default implementation of this method is illustrated below (code taken from SP.Runtime.debug.js):

  1. $2s_1: function SP_ClientQueryInternal$$2s_1($p0, $p1) {
  2.     $p0.writeStartElement('Query');
  3.     this.$2r_1($p0, $p1);
  4.     $p0.writeEndElement();
  5.     if (this.$n_1) {
  6.         $p0.writeStartElement('ChildItemQuery');
  7.         this.$n_1.$2r_1($p0, $p1);
  8.         $p0.writeEndElement();
  9.     }
  10. }

We should create our custom override of the $2s_1 method that injects the QueryableExpression part of the query into the ChildItemQuery block (see a similar implementation in the $2w_0 method of the SP.ClientObjectPropertyConditionalScope.prototype in SP.Runtime.debug.js). For example (to filter child items with Title Images”):

  1. SP.ClientQueryInternal.prototype.$2s_1 = function SP_ClientQueryInternal$$2s_1($p0, $p1) {
  2.     $p0.writeStartElement('Query');
  3.     this.$2r_1($p0, $p1);
  4.     $p0.writeEndElement();
  5.     if (this.$n_1) {
  6.         $p0.writeStartElement('ChildItemQuery');
  7.         this.$n_1.$2r_1($p0, $p1);
  8.  
  9.         // custom code
  10.         
  11.         $p0.writeStartElement(SP.ClientConstants.QueryableExpression);
  12.         $p0.writeStartElement(SP.ClientConstants.where);
  13.         $p0.writeStartElement(SP.ClientConstants.Test);
  14.         $p0.writeStartElement(SP.ClientConstants.Parameters);
  15.         $p0.writeStartElement(SP.ClientConstants.Parameter);
  16.         $p0.writeAttributeString(SP.ClientConstants.Name, "l");
  17.         $p0.writeEndElement();  // Parameters
  18.         $p0.writeEndElement();  // Parameter
  19.         $p0.writeStartElement(SP.ClientConstants.Body);
  20.         $p0.writeStartElement(SP.ClientConstants.equal);
  21.         $p0.writeStartElement(SP.ClientConstants.expressionProperty);
  22.         $p0.writeAttributeString(SP.ClientConstants.Name, "Title");
  23.         $p0.writeStartElement(SP.ClientConstants.expressionParameter);
  24.         $p0.writeAttributeString(SP.ClientConstants.Name, "l");
  25.         $p0.writeEndElement();  // ExpressionParameter
  26.         $p0.writeEndElement();  // ExpressionProperty
  27.         $p0.writeStartElement(SP.ClientConstants.expressionConstant);
  28.         SP.DataConvert.writeValueToXmlElement($p0, "Images");
  29.         $p0.writeEndElement();  // EQ
  30.         $p0.writeEndElement();  // Body
  31.         $p0.writeEndElement();  // ExpressionConstant
  32.         $p0.writeEndElement();  // Test
  33.         $p0.writeStartElement(SP.ClientConstants.Object);
  34.         $p0.writeStartElement(SP.ClientConstants.queryableObject);
  35.         $p0.writeEndElement();  // QueryableObject
  36.         $p0.writeEndElement();  // Object
  37.         $p0.writeEndElement();  // Where
  38.         $p0.writeEndElement();  // QueryableExpression
  39.  
  40.         // end custom code
  41.  
  42.  
  43.         $p0.writeEndElement();
  44.     }
  45. }

If we define this method in our page as illustrated above, all request sent to the server will filter the child items using the condition defined in the override (Title EQ Images”). That is probably not what we would like to achieve. It would be better to limit the filtering for example to the context the request was sent from.

I’ve found, that the calling SP.ClientContext object is available as this.$0_1.

First, I‘ve created a filterBase function that writes the filtering part of the request stream based on the condition, filter name and filter value parameters:

  1. function filterBase($p0, condition, filterName, filterValue) {
  2.     $p0.writeStartElement(SP.ClientConstants.QueryableExpression);
  3.     $p0.writeStartElement(SP.ClientConstants.where);
  4.     $p0.writeStartElement(SP.ClientConstants.Test);
  5.     $p0.writeStartElement(SP.ClientConstants.Parameters);
  6.     $p0.writeStartElement(SP.ClientConstants.Parameter);
  7.     $p0.writeAttributeString(SP.ClientConstants.Name, "l");
  8.     $p0.writeEndElement();  // Parameters
  9.     $p0.writeEndElement();  // Parameter
  10.     $p0.writeStartElement(SP.ClientConstants.Body);
  11.     $p0.writeStartElement(condition);
  12.     $p0.writeStartElement(SP.ClientConstants.expressionProperty);
  13.     $p0.writeAttributeString(SP.ClientConstants.Name, filterName);
  14.     $p0.writeStartElement(SP.ClientConstants.expressionParameter);
  15.     $p0.writeAttributeString(SP.ClientConstants.Name, "l");
  16.     $p0.writeEndElement();  // ExpressionParameter
  17.     $p0.writeEndElement();  // ExpressionProperty
  18.     $p0.writeStartElement(SP.ClientConstants.expressionConstant);
  19.     SP.DataConvert.writeValueToXmlElement($p0, filterValue);
  20.     $p0.writeEndElement();  // EQ
  21.     $p0.writeEndElement();  // Body
  22.     $p0.writeEndElement();  // ExpressionConstant
  23.     $p0.writeEndElement();  // Test
  24.     $p0.writeStartElement(SP.ClientConstants.Object);
  25.     $p0.writeStartElement(SP.ClientConstants.queryableObject);
  26.     $p0.writeEndElement();  // QueryableObject
  27.     $p0.writeEndElement();  // Object
  28.     $p0.writeEndElement();  // Where
  29.     $p0.writeEndElement();  // QueryableExpression
  30. }

To keep our former example condition, we can call filterBase from a new function filterLists:

  1. function filterLists($p0) {
  2.     filterBase($p0, SP.ClientConstants.equal, "Title", "Images");
  3. }

We assign the filtering function in our getLists method to the context:

  1. function getList() {
  2.     context.load(lists);
  3.     context.filter = filterLists;
  4.     context.executeQueryAsync(onGetListSuccess, onGetListFail);
  5. }

In the new version of the of the $2s_1 method override we check if the current context has a filtering method assigned to, and if one is found, it is called:

  1. SP.ClientQueryInternal.prototype.$2s_1 = function SP_ClientQueryInternal$$2s_1($p0, $p1) {
  2.     $p0.writeStartElement('Query');
  3.     this.$2r_1($p0, $p1);
  4.     $p0.writeEndElement();
  5.     if (this.$n_1) {
  6.         $p0.writeStartElement('ChildItemQuery');
  7.         this.$n_1.$2r_1($p0, $p1);
  8.  
  9.         // custom code
  10.  
  11.         var context = this.$0_1;
  12.         if (context.filter != undefined) {
  13.             context.filter($p0);
  14.         }
  15.  
  16.         // end custom code
  17.  
  18.  
  19.         $p0.writeEndElement();
  20.     }
  21. }

OK, we are one step further now, but wouldn’t it be nice, if we could query more objects in the same context (and the same batch) and have the option to set different filters for each one?

I’ve found that the object path property of the client object to be queried is available in the $2s_1 method via this.$G_0.

In this case we should override the $2s_1 method like this:

  1. SP.ClientQueryInternal.prototype.$2s_1 = function SP_ClientQueryInternal$$2s_1($p0, $p1) {
  2.     $p0.writeStartElement('Query');
  3.     this.$2r_1($p0, $p1);
  4.     $p0.writeEndElement();
  5.     if (this.$n_1) {
  6.         $p0.writeStartElement('ChildItemQuery');
  7.         this.$n_1.$2r_1($p0, $p1);
  8.  
  9.         // custom code
  10.  
  11.         var objectPathProp = this.$G_0;
  12.         if (objectPathProp.filter != undefined) {
  13.             objectPathProp.filter($p0);
  14.         }
  15.  
  16.         // end custom code
  17.  
  18.  
  19.         $p0.writeEndElement();
  20.     }
  21. }

The object path property is available as myObject.$5_0.$e_0 property of the client object (for example, lists.$5_0.$e_0), so we can rewrite our methods as illustrated below. In this case we extend our example with a further object (groups) that should be filtered by the filterGroups method (OwnerTitle property of the group EQ "DevSite Owners"). The getList function was also renamed to getObjects.

  1. var context = SP.ClientContext.get_current();
  2. var lists = context.get_web().get_lists();
  3. var groups = context.get_web().get_siteGroups();
  4.  
  5. $(document).ready(function () {
  6.     getObjects();
  7. });
  8.  
  9. function filterLists($p0) {
  10.     filterBase($p0, SP.ClientConstants.equal, "Title", "Images");
  11. }
  12.  
  13. function filterGroups($p0) {
  14.     filterBase($p0, SP.ClientConstants.equal, "OwnerTitle", "DevSite Owners");
  15. }
  16.  
  17. function getObjects() {
  18.     context.load(lists);
  19.     context.load(groups);
  20.  
  21.     lists.$5_0.$e_0.filter = filterLists;
  22.     groups.$5_0.$e_0.filter = filterGroups;
  23.     context.executeQueryAsync(onGetListSuccess, onGetListFail);
  24. }

The following Fiddler screenshot shows the resulting query:

image

This kind of overrides provides already a quite flexible “framework” for filtering objects on the server side using the JSCOM, making this important feature available for JavaScript developers as well. It would be nice if Microsoft would provide a similar, but official solution to this issue in a forthcoming service pack.

We can see from this experiment as well, that there is much more power available in the XML / JSON communication protocol behind the client object models, than it is made public by this APIs, so there is yet place for improvements. You can find valuable information related to the SharePoint Client Query Protocol here.

Older Posts »

Theme: Shocking Blue Green. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 54 other followers