如何用TFS WIQL（C#）查询项目中所有用户及其迭代信息

Got it, let's break this down for you. You need to pull unique users and their linked iterations from your TFS project 'Precient' using C# and WIQL, and the standard team/user queries aren't getting you what you need. Here's a solid approach to make this work:

Core Approach #

Users are associated with iterations through the work items they're assigned to. So we'll query all work items in your project, extract the Assigned To user and Iteration Path for each, then deduplicate the user-iteration pairs to get your unique set.

Targeted WIQL Query #

This query will fetch distinct user-iteration combinations, filtering out any empty values and targeting your specific project:

SELECT DISTINCT
    [System.AssignedTo],
    [System.IterationPath]
FROM WorkItems
WHERE
    [System.TeamProject] = 'Precient'
    AND [System.AssignedTo] IS NOT NULL
    AND [System.IterationPath] IS NOT NULL
ORDER BY [System.AssignedTo], [System.IterationPath]

• DISTINCT ensures each user-iteration pair only appears once
• We filter out null users/iterations to avoid clutter
• The query is scoped strictly to your 'Precient' project

C# Implementation #

We'll use the official TFS/Azure DevOps .NET SDK to execute the query and process results. First, install the required NuGet package:

# Using Package Manager Console
Install-Package Microsoft.TeamFoundationServer.Client

# Or .NET CLI
dotnet add package Microsoft.TeamFoundationServer.Client

Here's the complete code to run the query and format the output:

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

class TfsUserIterationQuery
{
    static void Main(string[] args)
    {
        // Replace with your TFS server URL and collection name
        string tfsCollectionUrl = "http://your-tfs-server:8080/tfs/YourCollectionName";
        string targetProject = "Precient";

        // Use Windows credentials (adjust for PAT if using Azure DevOps Services)
        VssCredentials credentials = new VssCredentials();

        try
        {
            using var witClient = new WorkItemTrackingHttpClient(new Uri(tfsCollectionUrl), credentials);

            // Execute the WIQL query
            var wiqlQuery = @$"SELECT DISTINCT
                [System.AssignedTo],
                [System.IterationPath]
            FROM WorkItems
            WHERE
                [System.TeamProject] = '{targetProject}'
                AND [System.AssignedTo] IS NOT NULL
                AND [System.IterationPath] IS NOT NULL
            ORDER BY [System.AssignedTo], [System.IterationPath]";

            var queryResult = witClient.QueryByWiqlAsync(new Wiql { Query = wiqlQuery }).Result;

            if (queryResult.WorkItems == null || !queryResult.WorkItems.Any())
            {
                Console.WriteLine("No user-iteration associations found in the project.");
                return;
            }

            // Fetch full work item details to get field values (WIQL only returns IDs initially)
            var workItemIds = queryResult.WorkItems.Select(item => item.Id).ToArray();
            var workItems = witClient.GetWorkItemsAsync(workItemIds, new[] { "System.AssignedTo", "System.IterationPath" }).Result;

            // Group iterations by user, ensuring unique iterations per user
            var userIterationMap = new Dictionary<string, HashSet<string>>();
            foreach (var workItem in workItems)
            {
                string userName = workItem.Fields["System.AssignedTo"].ToString();
                string iterationPath = workItem.Fields["System.IterationPath"].ToString();

                if (!userIterationMap.ContainsKey(userName))
                {
                    userIterationMap[userName] = new HashSet<string>();
                }
                userIterationMap[userName].Add(iterationPath);
            }

            // Print formatted results
            Console.WriteLine($"=== Unique Users & Their Iterations for Project: {targetProject} ===");
            foreach (var userEntry in userIterationMap.OrderBy(entry => entry.Key))
            {
                Console.WriteLine($"\nUser: {userEntry.Key}");
                Console.WriteLine("Associated Iterations:");
                foreach (var iteration in userEntry.Value.OrderBy(i => i))
                {
                    Console.WriteLine($"  - {iteration}");
                }
            }
        }
        catch (Exception ex)
        {
            Console.WriteLine($"An error occurred: {ex.Message}");
        }
    }
}

Key Notes #

1. Authentication: If you're using Azure DevOps Services instead of on-prem TFS, replace VssCredentials() with a PAT-based credential:

   string pat = "your-personal-access-token";
   var credentials = new VssPersonalAccessTokenCredential(pat);
   

2. Pagination: If your project has thousands of work items, add TOP and SKIP to your WIQL query to paginate results and avoid timeouts.
3. Iteration Path Format: The IterationPath returns the full hierarchical path (e.g., Precient\Release 1\Sprint 3). If you only want the iteration name, you can split the string on \ and take the last segment.
4. Permissions: Ensure the running user has read access to all work items in the project to get complete results.

内容的提问来源于stack exchange，提问作者user9461718