How to retrieve list of child tasks from a Product Backlog Item in TFS API?

Question

Given a certain product backlog id, I want to programmatically retrieve a list of tasks that are child to the PBI.

I am aware that there's not one field in the task that says "Parent PBI Id". I have a version of code that is working, but that's really really slow, since I have do perform part of my filtering int the client.

See how I'm currently doing:

string wiqlQuery =
    string.Format(
        "Select ID, [Remaining Work], State " +
        "from WorkItems " +
        "where (([Work Item Type] = 'Task')" +
        " AND ([Iteration Path] = '{0}' )" +
        " AND (State <> 'Removed')" +
        " AND (State <> 'Done')) ",
        sprint, storyId);

// execute the query and retrieve a collection of workitems
WorkItemCollection workItems = wiStore.Query(wiqlQuery);

if (workItems.Count <= 0)
    return null;

var result = new List<TaskViewModel>();
foreach (WorkItem workItem in workItems)
{
    var relatedLink = workItem.Links[0] as RelatedLink;
    if (relatedLink == null) continue;
    if (relatedLink.RelatedWorkItemId != storyId) continue;

    Field remWorkField = (from field in workItem.Fields.Cast<Field>()
                          where field.Name == "Remaining Work"
                          select field).FirstOrDefault();
    if (remWorkField == null) continue;
    if (remWorkField.Value == null) continue;

    var task = new TaskViewModel
    {
        Id = workItem.Id,
        ParentPbi = relatedLink.RelatedWorkItemId,
        RemainingWork = (double) remWorkField.Value,
        State = workItem.State
    };

    result.Add(task);
}

return result;

Have you tried to construct the query in the query builder gui? If you can do that and then save the query as a file, the file will contain a good wiql statement. If you can do the query in wiql, it will transform into a single sql query on the backend instead of you doing it on the client api. — bryanmac, Oct 05 '11 at 03:31
You can use `workItem.Fields.Cast().FirstOrDefault(f => field.Name == "Remaining Work")` as far as the method accepts a predicate. Currently you use `Where(..).FirstOrDefault()` which is overwhelming. — abatishchev, Oct 05 '11 at 08:47

pantelif · Answer 1 · 2011-10-12T10:37:53.997

As a standard, team project in MSF Agile comes with a set of queries. Take a look at 'Work Items' -> 'Iteration 1' -> 'Iteration Backlog'.

Saving this query as WIQL file in your disk is absolutely possible.
Using it as a modified wiqlQuery should relieve you from a lot of the filtering you do.

EDIT (in response to comment: "Ok, I did that, but the query doesn't mention the relationship between parent and linked (child) items"):

I opened the WIQL of a default "Iteration Backlog":

<?xml version="1.0" encoding="utf-8"?>
<WorkItemQuery Version="1">
  <TeamFoundationServer>
  http://****>
  <TeamProject>****</TeamProject>
  <Wiql>SELECT [System.Id], [System.WorkItemType], [System.Title],
  [System.State], [System.AssignedTo],
  [Microsoft.VSTS.Scheduling.RemainingWork],
  [Microsoft.VSTS.Scheduling.CompletedWork],
  [Microsoft.VSTS.Scheduling.StoryPoints],
  [Microsoft.VSTS.Common.StackRank],
  [Microsoft.VSTS.Common.Priority],
  [Microsoft.VSTS.Common.Activity], [System.IterationPath],
  [System.AreaPath] FROM WorkItemLinks WHERE
  (Source.[System.TeamProject] = @project and
  Source.[System.AreaPath] under @project and
  Source.[System.IterationPath] under '****\Iteration 1' and
  (Source.[System.WorkItemType] = 'User Story' or
  Source.[System.WorkItemType] = 'Task')) and
  [System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'
  and Target.[System.WorkItemType] = 'Task' ORDER BY
  [Microsoft.VSTS.Common.StackRank],
  [Microsoft.VSTS.Common.Priority] mode(Recursive)</Wiql>
</WorkItemQuery>

The part of the query that retrieves the related items should be this

[System.Links.LinkType] = 'System.LinkTypes.Hierarchy-Forward'

I'm using the Scrum template, but I think I know what you're talking about. In my case I have a "Sprint Backlog" query that does what I want, but I don't see any way to save it to disk or how to see the string query that is being used. It is a query of type "Tree of Work Items" — Padu Merloti, Oct 05 '11 at 16:16
I myself wasn't aware of this as well, and caught it in this thread http://stackoverflow.com/questions/6534060/how-to-export-bug-list-or-any-custom-query-from-tfs-to-excel-from-a-command-lin/6541934#6541934. Once you have saved the file you can open it in any editor and copy the WIQL query in your C# — pantelif, Oct 05 '11 at 18:36
Ok, I did that, but the query doesn't mention the relationship between parent and linked (child) items — Padu Merloti, Oct 12 '11 at 06:04

How to retrieve list of child tasks from a Product Backlog Item in TFS API?

1 Answers1