Retrieving TFS Results from a Tree Query

I recently needed to retrieve the results from a TFS tree query using .NET code, via calls to the TFS API. It was easy to find examples for retrieving a set of work items from a flat query, but I couldn’t find any information about how to retrieve a tree. I did find a few people talking about some ways to do it, but they said it was too slow.

In this blog post I’ll show you a very fast way to retrieve a tree of results. Let’s start with the query I’m using:

 SELECT [System.Id], 
       [System.Title], 
       [Microsoft.VSTS.Common.BacklogPriority], 
       [System.AssignedTo], 
       [System.State, 
       [Microsoft.VSTS.Scheduling.RemainingWork], 
       [Microsoft.VSTS.CMMI.Blocked], 
       [System.WorkItemType] 
  FROM WorkItemLinks 
 WHERE (
        Source.[System.TeamProject] = @project 
    AND Source.[System.WorkItemType] IN ('Product Backlog Item', 'Bug')
    AND (
        Source.[System.State] <> 'Removed' 
        AND Source.[System.State] <> @closedState
      )
    )
    AND [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
    AND Target.[System.WorkItemType] <> ''
ORDER BY [Microsoft.VSTS.Common.StackRank], [Microsoft.VSTS.Common.Priority], [System.Id] mode(Recursive)

I’ve highlighted the FROM location, which in this case is WorkItemLinks, which you need to use in order to retrieve a hierarchy. Flat querys pull from WorkItems.

Retrieving a tree of work items actually takes two queries. The first query returns a list of work item IDs that contains SourceId and TargetId values (parent and child IDs when the link type is Hierarchy-Forward as here). However, the query does not return any of the details of the work item. For that you’ll need a second query (described later).

The tree query above also contains two parameters (@project and @closedState) that will need values. Here is a code snippet that shows supplying values and retrieving the list of IDs:

 Dictionary<string, object> parameters = new Dictionary<string, object>();
parameters.Add("project", _tfsProjectName);
parameters.Add("closedState", "Done");

var treeQuery = new Query(_workItemStore, wiql, parameters);
WorkItemLinkInfo[] links = treeQuery.RunLinkQuery();

The first part of the code creates a dictionary with the parameter values to use when running the query. You must use the RunLinkQuery method to run the query, instead of the RunQuery method, when working with WorkItemLinks. Calling RunQuery when you reference WorkItemLinks will throw an exception.

The links array returned from this query might look something like this:

These results represent a tree. In our case the tree has just one level of children. As an example, work item 23268 is the parent of two other work items: 23339 and 23464. However, more importantly, the results of running this query do not return any of the fields from the query. How do you get those fields? You have to run another query that returns the results. The second query needs to use the set of columns from the original query, but pull from the WorkItems “table” instead of WorkItemLinks.

 //
// Build the list of work items for which we want to retrieve more information
//
int[] ids = (from WorkItemLinkInfo info in links
             select info.TargetId).ToArray();

//
// Next we want to create a new query that will retrieve all the column values from the original query, for
// each of the work item IDs returned by the original query.
//
var detailsWiql = new StringBuilder();
detailsWiql.AppendLine("SELECT");
bool first = true;

foreach (FieldDefinition field in treeQuery.DisplayFieldList)
{
    detailsWiql.Append("    ");
    if (!first)
        detailsWiql.Append(",");
    detailsWiql.AppendLine("[" + field.ReferenceName + "]");
    first = false;
}
detailsWiql.AppendLine("FROM WorkItems");

//
// Get the work item details
//
var flatQuery = new Query(_workItemStore, detailsWiql.ToString(), ids);
WorkItemCollection details = flatQuery.RunQuery();

Here is what this code does:

• Create an array of work item ids in the same order as returned by the tree query
• Build a new query that has this form: “SELECT {field list} FROM WorkItems”. The Query instance used to retrieve the tree of work item IDs has a property called DisplayFieldList that contains the the list of fields contained in the query. The code uses this list to build a new flat query that returns all the fields from the original tree query.
• Call the RunQuery method on the new flat query

The set of work items returned by this new query will be in exactly the same order as the link query results at the top of this post. You can then “combine” these two results sets to build a tree and use this tree as you need.

Using this approach is very fast because there are just two queries. I don’t have any inside knowledge about how the TFS client using the SDK to retrieve results, but I wouldn’t be surprised if it’s very much like what I’ve outlined here.