How to query work items with too many changesets

Question

These work items (and the linked changesets) are often error prone. So, good candidates for team code review, and should pay more attention.

I have checked Work Item Query Language and Client Object Model and WIQL syntax for Link Query, but still cannot get these work items (with count of changesets) effectively.

Is there any way to query like this (should be WIQL, SQL syntax here only for demonstration)?

SELECT [Source].[System.Id], COUNT(1) AS Changesets FROM WorkItemLinks
WHERE [Source].[System.WorkItemType] = 'Bug'
    AND [System.Links.LinkType] = 'Changeset'
GROUP BY [Source].[System.Id]
HAVING Changesets > 5

Or is there an efficient way to use the APIs, that don't need to read each work item to get the count?

Or could only be achieved by execute SQL queries on database directly (like this)?

Or can we have something like CurrentRelatedLinkCount/ExternalLinkCount/HyperLinkCount/RelatedLinkCount, which are supported by default (from VS IDE or API), as the query field?

Answer 1

You can use ExternalLinkCount for Changeset links.