DevOps Integration: Retrieving work items from multiple projects

As I work in many DevOps projects at the same time, I would like all my assigned work in a unified task list so that I can prioritize my work in the morning. At the moment, I have to log in using separate accounts to three different DevOps organizations. Sure, I could use my Office 365 account in all of these but they are not all hosted by me and the client Azure AD does not accept guest accounts.

Instead, I would like one view that shows something like this:

  • Project Name (with link to sprint board)
    • Task name (with link to task item)

I want this simple list repeated for all active tasks and bugs in the current iteration. To do this, we need to write some C# logic and create personal access tokens (PATs).

To get a personal access token, sign in to the DevOps project, click your account settings link in the top right corner, select Security, then Personal Access Tokens and click Create. Choose 90 days and full control, then copy the PAT. Yes, you need to recreate these every 90 days.

To get this to work, you need to add some NuGet packages:

You could either hard code this, or create an interface for users to store and edit their PATs. Here is an example where I have stored them in a SQL database using Entity Framework, loop through them and create a list of projects with the associated tasks.

var profileId = "GET_PROFILE_ID_HERE_FROM_AUTH_PROVIDER";
var projects = new List<Project>();
using (var db = new DataModel())
{
    var settings = db.DevOpsSettings.Where(dos => dos.UserId == profileId);
    foreach (var setting in settings)
    {
        projects.Add(GetProject(
            setting.CollectionUri,
            setting.PersonalAccessToken,
            setting.ProjectName,
            setting.ProjectFriendlyTitle));
    }
}

The project class looks like this:

using System.Collections.Generic;
using System.Linq;


/// <summary>
/// Represents a project within DevOps with associated work item tasks.
/// </summary>
public class Project
{
    /// <summary>
    /// Gets the active tasks within the current iteration. Current iteration is based on finding the task with the lowest iteration path.
    /// </summary>
    /// <value>
    /// The active tasks.
    /// </value>
    public List<WorkItem> Active
    {
        get
        {
            if (WorkItems.Count == 0)
            {
                return WorkItems;
            }

            var items = WorkItems.Where(w => 
                (new[] {"Active", "New"}).Contains(w.State) & 
                (new[] { "Bug", "Task" }).Contains(w.Type)).ToList();
            var iteration = items.OrderBy(w => w.Iteration).First().Iteration;
            return items.Where(w => w.Iteration == iteration).ToList();
        }
    }

    /// <summary>
    /// Gets or sets the link.
    /// </summary>
    /// <value>
    /// The link.
    /// </value>
    public string Link { get; set; }

    /// <summary>
    /// Gets or sets the title.
    /// </summary>
    /// <value>
    /// The title.
    /// </value>
    public string Title { get; set; }


    /// <summary>
    /// Gets or sets the work items assigned to the user.
    /// </summary>
    /// <value>
    /// The work items.
    /// </value>
    public List<WorkItem> WorkItems { get; set; }
}

The main logic is within the GetProject method and looks like this:

using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.TeamFoundation.WorkItemTracking.WebApi;
using Microsoft.TeamFoundation.WorkItemTracking.WebApi.Models;
using Microsoft.VisualStudio.Services.Common;
using Microsoft.VisualStudio.Services.WebApi;

/// <summary>
/// Used to integrate with Azure DevOps work items.
/// </summary>
public class DevOpsModel
{
    /// <summary>
    /// Gets the project.
    /// </summary>
    /// <param name="collectionUri">The collection URI.</param>
    /// <param name="pat">The personal access token.</param>
    /// <param name="teamProjectName">Name of the team project.</param>
    /// <param name="projectTitle">The project friendly title.</param>
    /// <returns>A project containing work items.</returns>
    public Project GetProject(string collectionUri, string pat, string teamProjectName, string projectTitle)
    {
        // Create return object
        var project = new Project
        {
            Title = projectTitle,
            Link = $"{collectionUri}{teamProjectName}",
            WorkItems = new List<WorkItem>()
        };

        // Create instance of VssConnection using Personal Access Token
        var connection = new VssConnection(new Uri(collectionUri), new VssBasicCredential(string.Empty, pat));

        // Create instance of WorkItemTrackingHttpClient using VssConnection
        var witClient = connection.GetClient<WorkItemTrackingHttpClient>();

        // Get 2 levels of query hierarchy items
        var queryHierarchyItems = witClient.GetQueriesAsync(teamProjectName, depth: 2).Result;

        // Search for 'My Queries' folder
        var myQueriesFolder = queryHierarchyItems.FirstOrDefault(qhi => qhi.Name.Equals("My Queries"));
        if (myQueriesFolder == null)
        {
            return project;
        }

        // See if our query already exists under 'My Queries' folder.
        var queryName = "Assigned to Me";
        QueryHierarchyItem workQuery = null;
        if (myQueriesFolder.Children != null)
        {
            workQuery = myQueriesFolder.Children.FirstOrDefault(qhi => qhi.Name.Equals(queryName));
        }

        if (workQuery == null)
        {
            // if the query does not exist, create it.
            workQuery = new QueryHierarchyItem
            {
                Name = queryName,
                Wiql =
                    "SELECT [System.Id],[System.WorkItemType],[System.Title],[System.AssignedTo],[System.State],[System.IterationPath] FROM WorkItems WHERE [System.TeamProject] = @project AND [System.AssignedTo] = @me AND [System.State] <> 'Closed'",
                IsFolder = false
            };
            workQuery = witClient.CreateQueryAsync(workQuery, teamProjectName, myQueriesFolder.Name).Result;
        }

        // run the query
        var result = witClient.QueryByIdAsync(workQuery.Id).Result;
        if (!result.WorkItems.Any())
        {
            return project;
        }

        var skip = 0;
        const int batchSize = 100;
        List<WorkItemReference> workItemRefs;
        do
        {
            workItemRefs = result.WorkItems.Skip(skip).Take(batchSize).ToList();
            if (workItemRefs.Any())
            {
                // get details for each work item in the batch
                var workItems = witClient.GetWorkItemsAsync(workItemRefs.Select(wir => wir.Id)).Result;
                foreach (var workItem in workItems)
                {
                    project.WorkItems.Add(new WorkItem
                    {
                        Title = (workItem.Fields["System.Title"] ?? string.Empty).ToString(),
                        Iteration = (workItem.Fields["System.IterationPath"] ?? string.Empty).ToString().Split('\\').Last(),
                        Id = workItem.Id ?? 0,
                        Type = (workItem.Fields["System.WorkItemType"] ?? string.Empty).ToString(),
                        State = (workItem.Fields["System.State"] ?? string.Empty).ToString()
                    });
                }
            }

            skip += batchSize;
        } while (workItemRefs.Count() == batchSize);

        return project;
    }
}

I need to add some error handling for expired PATs and handle that in the interface, but that's something you can add for yourself with a try/catch around the GetProject call.

Hope this helps anyone else, sure saves me time in the morning!