Where does the work item iterationId field come from?

Question

in the tfs database Tfs_DefaultCollection

There are nearly no keys. (maybe none, as I haven't exhaustively searched)

I'm able to see all my work items, and current/previous states just fine. However when I join WorkItemAre to Tbl_Iteration none of the IterationIds match at all. How do I look at work items by iteration name/title?

my search code is as follows:

    this.Tbl_TeamConfigurationIterations.Dump();
    this.Tbl_Iterations.Dump();
    var myPersonId= foo; // omitted from sample
    var qIteration = from wia in WorkItemsAres.Where(x=>x.AssignedTo==myPersonId && x.State!="Closed" && x.State!="Resolved")
                    join iLeft in Tbl_Iterations on wia.IterationID equals iLeft.SequenceId into iL
                    from iteration in iL.DefaultIfEmpty()
                    select new {iteration.Iteration,wia};
    qIteration.Dump();//.Select(x=>new{x.AreaID,x.IterationID, x.Title,x.WorkItemType}).Dump();

for those interested in the solutions (both direct db and tfs api dll calls):

Direct Db version

Proper Tfs dll calls

Dylan Smith · Answer 1 · 2014-07-14T19:37:40.293

Microsoft very strongly recommends against using the transactional database directly (in fact it can put you into an unsupportable state). If you want to query TFS Data is recommended to do so using the API and Client Object Model (SDK). There is a very rich API for interacting with TFS that is supported.

See the docs here: http://msdn.microsoft.com/en-us/library/bb130146.aspx

Here is something that came out of a private mailing list where we (ALM MVP's) were trying to better understand Microsofts stance on this:

Reading from the [TFS] databases programmatically, or manually, can cause unexpected locking within Microsoft SQL Server which can adversely affect performance. Any read operations against the [TFS] databases that originate from queries, scripts, .dll files (and so on) that are not provided by the Microsoft [TFS] Development Team or by Microsoft [TFS] Support will be considered unsupported if they are identified as a barrier to the resolution of a Microsoft support engagement.

If unsupported read operations are identified as a barrier to the resolution of support engagement, the database will be considered to be in an unsupported state. To return the database to a supported state, all unsupported read activities must stop.

can it go into an unsupported state if you do not touch the data, only read from it? — Maslow, Jul 14 '14 at 19:17
it's possible, because your queries might not be optimized well and they can cause slow-downs and deadlocks, or other issues. Regardless, it's always better to use the API (or read from the Warehouse DB if you must use SQL). — Dylan Smith, Jul 14 '14 at 19:31

score 2 · Accepted Answer · answered Jul 14 '14 at 19:51

2

@Maslow, you can use xxTree table to get iteration name (join with WorkItemsAre table on IterationID = xxTree.Id) but you should be aware that this is undocumented and unsupported way. I would strongly recommend using TFS object model to do such things.

answered Jul 14 '14 at 19:51

Oleg Mikhaylov

1,124
7
10

updated question with links to usage, and finally converted over to using the api instead of db queries. – Maslow Sep 23 '14 at 18:08

Where does the work item iterationId field come from?

2 Answers2