Get AccountName of the Assigned To user from TFS using WIQL

Question

I have such wiql to get work items from TFS:

SELECT [System.Id], [System.Title], [Area Path], [Iteration Path], [Priority], [Test Owner], [Assigned To],[System.State], [System.Reason]
FROM WorkItems
WHERE [System.WorkItemType] = 'Feature' and
      [Iteration Path] != '' and
      [Area Path] Under 'MyProject'

Field [Assigned To] returns Display Name of the user. Is it possible to get AccountName of that user? Thank you!

Answer 1

It is possible to get the AccountName. But it's not straightforward. Fields with username values like the AssignedTo field are stored in the TFS database as a reference to an identity, not simply as the display name, so you would think that there would be some simple way to retrieve any field associated with that identity, but that's just not part of the TFS design - not yet anyway.

Even querying the list of accounts by display name is not supported. Instead, your only option that I have found is to dig into the account list and match the display name to an identity. That identity has your AccountName. Of course, time and resource use for this search will grow as your user list grows. I don't know if it's worth this much trouble to you, but this site shows how to pull an AccountName from only a Display name (in VB): http://www.developerfusion.com/community/blog-entry/8389748/retrieving-an-identity-from-team-foundation-server-using-only-the-display-name/