c# WIQL Query to get all different Iteration Paths

Question

I'm trying to get all different Iteration Paths of our Team Project by using a wiql query.

My actual solution is the following:

I use this query

    public static readonly string IterationPathsQuery = @"SELECT [System.IterationPath] FROM workitems
        WHERE[System.WorkItemType] = 'Requirement'
        OR[System.WorkItemType] = 'Feature'";

To get all relevant WorkItems and iterate through them to get all the different Iteration Paths.

private void FillIterationPathComboBox(WorkItemStore wiStore)
{
    WorkItemCollection wiCollection = wiStore.Query(Constants.IterationPathsQuery);
    var paths = new List<string>();

    ...
    foreach (WorkItem wi in wiCollection)
    {
        ...

        if (!String.IsNullOrEmpty(wi.IterationPath) && !paths.Contains(wi.IterationPath))
        {
            paths.Add(wi.IterationPath);
        }
    }

    foreach (string path in paths)
    {
        IterationPathComboBox.Items.Add(path);
    }
}

But this solution is not with good performance. Is there a way to query only for the different Iteration Paths that are used? I already read that "distinct" is not supported, but maybe there's a way that I did not think about yet.

Answer 1

WIQL Query is not able to filter different Iteration Paths. Here are two alternatives:

1. You can export the query to Excel and use Excel RemoveDuplicates Method to filter different Iteration Paths.

2. You can get a list of Iteration Paths, then remove Duplicates and Get Distinct records using LINQ. Check the code snippet on this website.

   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.Linq;
   
   namespace AbundantCode  
   {
       internal class Program
       {
           //How to Remove Duplicates and Get Distinct records from List using LINQ ?
   
           private static void Main(string[] args)
           {
               List<Employee> employees = new List<Employee>()
   {
   
   new Employee { EmpID = 1 , Name ="AC"},
   new Employee { EmpID = 2 , Name ="Peter"},
   new Employee { EmpID = 3 , Name ="Michael"},
   new Employee { EmpID = 3 , Name ="Michael"}
   };
   
    //Gets the Distinct List
    var DistinctItems = employees.GroupBy(x => x.EmpID).Select(y => y.First());
               foreach (var item in DistinctItems)
               Console.WriteLine(item.Name);
               Console.ReadLine();
           }
       }
   
       public class Employee
       {
           public string Name { get; set; }
           public int EmpID { get; set; }
       }
   }