Using CSOM in C# to Dynamically Load Project Server Columns From Selected Rows

Question

I use CSOM .NET to load task objects from Project Server 2013, and I need to

• filter tasks so that only a subset of them is returned, and
• load only a subset of task columns, specified at runtime by the user.

I found this post that shows how to load a dynamic set of columns, and it works nicely for my second requirement. However, I cannot figure out a workable LINQ syntax to combine both column selection and row filtering.

In the example below, I need to load only those "rows" for summary tasks (where t.IsSummary is true), and I want to load only the Name, Start, and Finish columns.

The following code from the referenced post loads just the three columns that I need:

foreach (string fieldName in new List<string>(){"Name","Start","Finish"});
{
  ctx.Load(ctx.Tasks,c => c.Include(t => t[fieldName]));
}
ctx.ExecuteQuery();  

But when I try to combine where() and include() in the only syntax that makes sense to me, I get InvalidQueryExpressionException on second iteration through the foreach loop: "The query expression is not supported."

foreach (string fieldName in new List<string>(){"Name","Start","Finish"});
{
    ctx.Load(ctx.Tasks,
    c => c.Where(t => t.IsSummary),
    c => c.Include(t => t[fieldName])
    );
}

ctx.ExecuteQuery();

I get the same error if I reverse the order of where and include clauses. If I pull the where clause outside of the loop over field names and make it a separate Load call, the summary-task row filtering works, but I lose the dynamic selection of tasks fields. There must be a syntax in LINQ for CSOM that meets both requirements. What is the correct syntax to do this type of query?

Answer 1

I answered this myself by using expression trees, which let you filter a set of rows and select a set of columns based on parameters that are only known at runtime. In the example below, I simulate finding out at runtime that I need to filter the tasks on the IsSummary column and that I should retrieve only the five columns Id, Name, Start, IsSubProject, and Finish. Here's the code:

        using System.Linq.Expressions;

        // Input parms discovered at runtime
        string filterColumnName = "IsSummary";
        List<string> columnNames = new List<string>(
          new[] { "Id", "Name", "Start", "IsSubProject", "Finish" });

        //  Get the client object for the Published Project matching projGuid
        ctx.Load(ctx.Projects, c => c.Where(p => p.Id == projGuid));
        ctx.ExecuteQuery();
        PublishedProject proj = ctx.Projects.Single();

        // Compute the expression tree for filtering the task rows
        var taskParm = Expression.Parameter(typeof(PublishedTask), "t");
        var predicate = Expression.PropertyOrField(taskParm, filterColumnName);
        var filterExpression = Expression.Lambda<
                Func<PublishedTask, bool>>(predicate, taskParm);

        //  Dynamically generate expression tree for each column to be included
        var colSelectionList = new List<Expression<
                Func<PublishedTask, object>>>();

        foreach (var colName in columnNames)
        {
            var fldExpr = Expression.PropertyOrField(taskParm, colName);
            var fldAsObjExpr = Expression.Convert(fldExpr, typeof(object));
            var colSelectorExpr = Expression.Lambda<
                 Func<PublishedTask, object>>(fldAsObjExpr, taskParm);
            colSelectionList.Add(colSelectorExpr);
        }

        //  Create query using LoadQuery (Load does not work here)                  
        var taskList = ctx.LoadQuery(proj.Tasks
                        .Where(filterExpression)
                        .Include(colSelectionList.ToArray())
                        );

        //  Execute the query
        ctx.ExecuteQuery();
        // taskList now contains just the filtered rows and selected columns

I hope this helps someone else who is stuck on using CSOM to do this for Project Server. I found these two references helpful: At MSDN and at Second Life of a Hungarian SharePoint Geek

..Jim

Answer 2

The following example demonstrates how to apply select and filter operators in SharePoint CSOM API:

var list = ctx.Web.Lists.GetByTitle(listTitle);
var items = list.GetItems(CamlQuery.CreateAllItemsQuery());

var result = ctx.LoadQuery(items.Where(i => (bool)i["IsSummary"]).Include(i => i["Name"], i => i["Start"], i => i["Finish"]));
ctx.ExecuteQuery();

foreach (var item in result)
{
    Console.WriteLine(item["Name"]);    
}

So, i believe the following expression is supported in Project Server CSOM API:

var result = ctx.LoadQuery(ctx.Tasks.Where(t => (bool)t["IsSummary"]).Include(t => i["Name"], t => t["Start"], t => t["Finish"]));
ctx.ExecuteQuery();