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?

score 0 · Answer 1 · answered May 26 '16 at 11:22

0

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();

answered May 26 '16 at 11:22

Vadim Gremyachev

57,952
20
129
193

Thanks for the answer. Yes, CAML would solve my problem, but there is no way to invoke CAML with Project Server CSOM. It works for SharePoint lists but not for Project Server objects such as projects and tasks. Your second idea will work, but it is not dynamic. Rather it is hard-coded for the three fields "Name", "Start", and "FInish". What I need is a method that will include an arbitrary list of fields; there may be 3 fields or there may be 10 fields. – Jim Black May 27 '16 at 17:11
1

A simpler explanation of what I need: I need a way to specify the included fields at runtime rather than at compile time. So I cannot write code that specifically includes just the three fields of "Name", "Start", and "Finish". Instead I need code that can include an arbitrary list of N fields at runtime. Thanks again! – Jim Black May 27 '16 at 17:20

score 0 · Answer 2 · answered Jun 25 '16 at 23:01

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

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

2 Answers2