Time travelling with Work Item Queries In TFS 2010

by Ewald Hofman 20. April 2010 05:28

Someone asked me if I could help him because he made a mess of the work items. He published old data from Excel to TFS, so all the updates of his team mates where gone with one click.

There is a nice and very under-appreciated feature in the work item queries to get the values of the work items at a moment in history as if you created a snapshot at that time. It is pretty easy to accomplish this with the ASOF feature. The following steps show you how you can use the ASOF statement.

  1. Create a new query with the fields and filters you are interested in via the default “Add query” command
  2. Save the query (File –> Save New Query 1 [Query])
  3. You get now the ability to store the query on the file system 

  4. Select an appropriate location and click Save
  5. Open the file you just saved in Notepad. You will now see the Work Item Query Language (WIQL) that is used to define the query. For more information on the syntax, please refer to http://msdn.microsoft.com/en-us/library/bb130198.aspx
    This WIQL could look like:

    <?xml version="1.0" encoding="utf-8"?><WorkItemQuery Version="1"><TeamFoundationServer>http://myserver:8080/tfs/defaultcollection</TeamFoundationServer><TeamProject>Agile</TeamProject><Wiql>SELECT [System.Id], [System.Title] FROM WorkItems WHERE [System.AssignedTo] = 'Ewald Hofman' ORDER BY [System.Id]</Wiql></WorkItemQuery>
  6. You can only execute a modified WIQL with a .NET app. So lets create a new C# ConsoleApplication project
  7. Add the references to
    1. Microsoft.TeamFoundation.Client
    2. Microsoft.TeamFoundation.Common
    3. Microsoft.TeamFoundation.WorkItemTracking.Client
    4. System.Windows.Forms

      you can find the TeamFoundation assemblies in %Program Files%\Microsoft Visual Studio 10.0\Common7\IDE\ReferenceAssemblies\v2.0
  8. Add the following using statements to your code

    using Microsoft.TeamFoundation.Client;
    using Microsoft.TeamFoundation.WorkItemTracking.Client;
    using System.Net;
    using System.Windows.Forms;
  9. Now add the following code to the class Program. You will have to modify the tfsServer and queryText variables to your own values. Notice at the end of the queryText the ASOF syntax. With this statement you are time travelling.
    static void Main(string[] args)
        StringBuilder clipboardText = new StringBuilder();
        bool titlesPrinted = false;
        string tfsServer = @"http://myserver:8080/tfs/defaultcollection";
        string queryText = @"SELECT [System.Id], [System.Title], [System.State] FROM WorkItems 
                                WHERE [System.AssignedTo] = 'Ewald Hofman' ORDER BY [System.Id] ASOF '4/10/2010'";
        // Open the connection to TFS
        using (var tfs = new TfsTeamProjectCollection(new Uri(tfsServer), CredentialCache.DefaultCredentials))
            // Get the work item service
            var store = (WorkItemStore)tfs.GetService(typeof(WorkItemStore));
            // Execute the query
            var wiCollection = store.Query(queryText);
            // Iterate through all work items
            foreach (WorkItem wi in wiCollection)
                // Add the column headers
                if (!titlesPrinted)
                    titlesPrinted = true;
                    foreach (FieldDefinition field in wiCollection.DisplayFields)
                // Add the work item values
                foreach (FieldDefinition field in wiCollection.DisplayFields)
        // Put the complete text to the clipboard, so it can be copied to Excel
        if (clipboardText.Length > 0)
    /// <summary>
    /// Try a few times to put it on the clipboard: known issue...
    /// </summary>
    public static void SetClipboard(object data)
        for (int i = 0; i < 10; i++)
            catch { }
  10. You can now run the code, open Excel and start paste command.


TFS SDK | VSTS 2010 | Work items

Comments are closed

Powered by BlogEngine.NET
Theme by Mads Kristensen


Widget Statistics not found.

There is an error in XML document (0, 0).X

Recent comments

Comment RSS