Query for Bugs, Tasks, and Other Work Items

You can query for bugs, tasks, other types of work items, and links between work items by using one of the WorkItemStore.Query methods or a Query object. These queries use the work item query language (wiql), which resembles Transact-SQL.

TfsTeamProjectCollection tpc = new TfsTeamProjectCollection(
      new Uri("https://server:8080/tfs/DefaultCollection"));
WorkItemStore workItemStore = (WorkItemStore)tpc.GetService(typeof(WorkItemStore)); 
WorkItemCollection queryResults = workItemStore.Query("
   Select [State], [Title] 
   From WorkItems
   Where [Work Item Type] = 'User Story'
   Order By [State] Asc, [Changed Date] Desc");

Dim collectionUri As Uri
collectionUri = New Uri("https://Server:8080/tfs/DefaultCollection")
Dim tpc As New TfsTeamProjectCollection(collectionUri)
Dim workItemStore As WorkItemStore
workItemStore = tpc.GetService(Of WorkItemStore)()
Dim queryResults As WorkItemCollection
queryResults = workItemStore.Query(“
  Select [State], [Title]
  From WorkItems
  Where [Work Item Type] = ‘User Story’
  Order By [State] Asc, [Changed Date] Desc”)

In this topic

• Query Language

• Syntax of the Where Clause

  • Comparison Operators

  • Variables

  • Literal Values

• Query Methods and the Query Object

• Asynchronous Queries

• Paging of Field Values

Required Permissions

A query will return only those work items for which you have the View work items or View work items in this node permission. Typically, these permissions are granted to members of the Readers and Contributors groups for each team project. For more information, see Team Foundation Server Permissions.

Tip

To explore the details of the work item query language, create queries by using Team Explorer, and then save them as .wiql files. Rename the files to use the .xml extension, and open them in Visual Studio. Look for the wiql element to see how each query is expressed in the work item query language.

Query Language

The work item query language has five parts.

Select [State], [Title] 
From WorkItems
Where [Work Item Type] = 'User Story'
Order By [State] Asc, [Changed Date] Desc
AsOf '6/15/2010'

Select [State], [Title]	Identifies each Field whose value will be set in every WorkItem that is returned by the query. You can specify either the display name or the reference name of the field. You must use square brackets ([]) if the name contains blanks or periods. Caution You can use a WorkItem that was returned by a query to get the value of a Field, even if the query did not return the value. If you do this, another round trip to the server will occur. For more information, see Performance Considerations.
From WorkItems	Indicates whether you want the query to find work items or links between work items. From WorkItems Use for work items. From WorkItemLinks Use for links between work items. For more information, see Queries for Links between Work Items.
Where [Work Item Type] = 'User Story'	Specifies the filter criteria for the query. For more information, see Syntax of the Where Clause later in this topic.
Order By [State] Asc, [Changed Date] Desc	(Optional) Specifies how to sort the WorkItemCollection that the query returns.
AsOf '6/15/2010'	(Optional) Specifies a historical query by indicating a date or point in time at which the filter is to be applied. For example, this query returns all user stories that existed on June 15, 2010. Note You can’t create AsOf queries in the query builder in Visual Studio. If you create a query file (.wiq) that includes an AsOf clause, and then load that in Visual Studio, the AsOf clause will be ignored.

Syntax of the Where Clause

The Where clause specifies the filter criteria of a query for work items. The query returns only work items that satisfy these conditions. The following example query returns user stories that are active and that are assigned to you.

Where [Work Item Type] = 'User Story'
AND [State] = ‘Active’
AND [Assigned to] = @Me

You can control the order in which logical operators are evaluated if you use parentheses to group search criteria. For example, to return work items that are either assigned to you or that you closed, change the query filter to match the following example:

Where [Work Item Type] = 'User Story'
AND [State] = ‘Active’
AND ( [Assigned to] = @Me
OR [Closed by] = @Me )

The following table describes the syntax of the Where clause:

	Syntax	Example
Where clause	Where FilterCondition [Group|{LogicalOperator FilterCondition}]
Group	(FilterCondition LogicalOperator FilterCondition [LogicalOperator Filter Condition]…)	([Assigned to] = @Me OR [Created by = @Me]) The logical grouping operators are AND and OR.
FilterCondition	Field ComparisonOperator Value	[Work Item Type] = ‘Help Topic’ You can specify either the reference name or the display name of a field. If the name contains spaces or periods, you must enclose it in square brackets ([]). The comparison operators are described in Comparison Operators later in this topic. For the value, you can specify either a literal value ('User Story') or a macro (@Me).
Value	LiteralValue|Variable|Field	'User Story' Literal value The actual value to compare to the value of the field. For more information, see Literal Values later in this topic. Variable An expression that indicates a certain value. For example, @Me indicates the person who is running the query. For more information, see Variables later in this topic. Field The name of another field. For example, you can use [Assigned to] = [Changed by]to find work items that are assigned to the person who changed the work item most recently.

Comparison Operators

You can use the operators in the following table to specify how a field must relate to the corresponding value:

Query operator	Description	Applicable types of fields
=	Matches the value.	Number, Text, Date, Tree
<>	Does not match the value.	Number, Text, Date, Tree
>	Is larger than the value.	Number, Text, Date
<	Is less than the value.	Number, Text, Date
>=	Is larger than or equal to the value.	Number, Text, Date
<=	Is less than or equal to the value.	Number, Text, Date
Contains	Contains the string.	Text
Does Not Contain	Does not contain the string.	Text
In	Matches any value in a comma-delimited set. For example, [System.Id] In (100, 101, 102) finds work items whose IDs are 100, 101, and 102.	Number, Text, Date, Tree
In Group	Is a member of the group. The group can be a Team Foundation group ([Assigned to] In Group [Project]\Contributors) or a work item category when you use it with the Work Item Type field ([Work Item Type] In Group Requirements). For information about category groups, see Grouping Work Item Types into Categories.	Text
Not in Group	Is not a member of the group. For more information, see the entry for In Group.	Text
Was Ever	Matches if the field ever matched the value, even if it has changed to a different value.	Text, Date
Under	For areas and iterations, matches if the work item is in that node or any one of its child nodes. For information about areas and iterations, see Create and Modify Areas and Iterations.	Tree
Not Under	For areas and iterations, matches if the work item is not in that node or any one of its child nodes.	Tree

Variables

You can use variables in your queries to pass user input or calculated values. To create a query that includes variables, create placeholders in the query string by using @variable. Then pass the name and the value of each variable to the query method by using an implementation of IDictionary, as the following example shows

// Define a query that uses a variable for the type of work item. 
string queryString = "Select [State], [Title] From WorkItems Where [Work Item Type] = @Type";

// Set up a dictionary to pass "User Story" as the value of the type variable.
Dictionary<string, string> variables = new Dictionary<string, string>();
variables.Add("Type", "User Story");

// Create and run the query.
Query query = new Query(workItemStore, queryString, variables); 
WorkItemCollection results = query.RunQuery();

// Define a query that uses a variable for the type of work item.
Dim queryString As New StringBuilder("SELECT [State], [Title] FROM WorkItems WHERE [WorkItemtype] = @Type")

// Set up a dictionary to pass "User Story" as the value of a type variable.
Dim variables = New Dictionary(Of String, String)
variables.Add("Type", "User Story")

// Create and run the query.
Dim query As New Query(workItemStore, queryString, variables)                   
WorkItemCollection results = query.RunQuery()

You can also use @Me or @Today in your query without providing values for those variables. When these variables appear in the query but have no associated value passed in the IDictionary implementation, the variables are evaluated as the following table describes:

Variable	Usage
@Me	Indicates the current user in a field that contains user names. For example, you can find work items that you activated if you specify [Activated by] = @Me.
@Today	Indicates the current date. You can also modify the @Today variable by adding or subtracting days. For example, you can find all items that were activated in the last week if you specify [Activated Date] > @Today - 7.

Literal Values

When you specify a value for each field, the value must match the data type of that field. All fields in Team Foundation have one of the data types that are listed in the following table:

Data type	Data stored
DateTime	A datetime value as defined by SQL Server. By default, DateTime values in queries for work items are treated as having date precision. For example, a work item that is created at any time of day on June 1, 2010, would match the filter criteria [Created Date] = 6/1/2010. For more information, see Query Methods and the Query Object later in this topic and the following page on the Microsoft Web site: datetime (Transact-SQL).
Double	A real number, such as 0.2 or 3.5.
GUID	A character string that represents a GUID.
HTML	Text strings that contain HTML.
Integer	A 32-bit integer that is signed, such as 0, 1, 2, or 34.
PlainText	An unformatted text string that may be longer than 255 characters.
String	A text string that contains up to 255 characters.
TreePath	A branching tree structure, such as an Area or Iteration.

Queries for Links Between Work Items

You can also use queries to find links between work items. A condition in the Where clause may apply to the links or to any work item that is the source or the target of a link. The following table summarizes the differences between these types of queries and queries only for work items:

	Work items	Links between work items
From clause	From WorkItems	From WorkItemLinks
Where clause	[FieldName] = Value	One of the following: [Source].[FieldName] = Value [Target].[FieldName] = Value [System.Links.LinkType] = 'LinkName'
Mode		One of the following: mode(MustContain) (Default) Returns only WorkItemLinkInfo records where the source, target, and link criteria are all satisfied. mode(MayContain) Returns WorkItemLinkInfo records for all work items that satisfy the source and link criteria, even if no linked work item satisfies the target criteria. mode(DoesNotContain) Returns WorkItemLinkInfo records for all work items that satisfy the source, only if no linked work item satisfies the link and target criteria.
Returns	WorkItemCollection	An array of WorkItemLinkInfo.

The following query returns the links between user stories and their active child nodes.

SELECT [System.Id]
FROM WorkItemLinks
WHERE ([Source].[System.WorkItemType] = 'User Story')
  And ([System.Links.LinkType] = 'Child')
  And ([Target].[System.State] = 'Active')
mode(MustContain)

Query Methods and the Query Object

You can query for work items by using the WorkItemStore.Query method. You can also identify the number of work items that satisfy a query without returning all work items by using the WorkItemStore.QueryCount method.

You can create a Query object to define and run queries.

string queryString = "Select [Title] From WorkItems Where [Work Item Type] = 'User Story'"
Query query = new Query(workItemStore, queryString); 
int numWorkItems = query.RunCountQuery();
Console.WriteLine("The project collection has " + numWorkItems.ToString() + " user stories.");

Dim queryString As New StringBuilder("Select [Title] FROM WorkItems WHERE [WorkItemType] = 'User Story'"
Dim query As New Query(workItemStore, queryString)
Dim numWorkItems As Int
numWorkItems = query.RunCountQuery()
Console.Writeline("The project collection has " + numWorkItems.ToString() + " user stories.")

Asynchronous Queries

You can run queries asynchronously by using the Query.BeginQuery method. The following sample runs a query asynchronously and cancels the query after a very short time-out period.

// Run the query asynchronously, and time out after 0.05 seconds. 
ICancelableAsyncResult callback = query.BeginQuery();
callback.AsyncWaitHandle.WaitOne(50, false); 
if (!callback.IsCompleted) 
{
   callback.Cancel();
   Console.WriteLine("The query timed out");
}
else
{
   WorkItemCollection nextResults = query.EndQuery(callback); 
   Console.WriteLine("The project collection has " + nextResults.Count.ToString() + " work items."); 
}

Dim callback as ICancelableAsyncResult
callback = query.RunQuery()
callback.AsyncAWaitHandle.WaitOne(50, False)
If Not (callback.IsCompleted)
Then
callback.Cancel()
Console.Writeline("The query timed out")
Else
Dim nextResults As WorkItemCollection = query.EndQuery(callback)
Console.Writeline("The project collection has " + nextResults.Count.ToString() + " work items.") 
End If

Paging of Field Values

The WorkItemCollection that a query returns contains values for the following fields:

• ID

• Rev (Revision)

• AreaID

• IterationID

• WorkItemType

The values of fields that you specify in the SELECT clause are returned in pages.

Note

By default, each page contains the selected fields for 50 work items. You can adjust the size of a page by using WorkItemCollection.PageSize.

You can minimize round trips to the server by selecting all fields that your code will use. The following code makes one round trip for the query and one round trip to return a page of titles every time that a new page is accessed.

WorkItemCollection results = WorkItemStore.Query(
   "SELECT Title FROM Workitems WHERE (ID < 1000)"); 
foreach (WorkItem item in results) 
{
   Console.WriteLine(item.Fields["Title"].Value);
}

If your code accesses a field that you did not specify in the SELECT clause, that field is added to the set of paged fields. Another round trip is performed to refresh that page to include the values of that field.

Query Syntax (EBNF)

Extended Backus-Naur Form (EBNF) is meta-language that describes the grammar of languages in a compact and unambiguous way. This code block uses EBNF to describe the grammar of work item query language (WIQL).

If you are not familiar with EBNF, see an alternative description of WIQL in Syntax for the Work Item Query Language.

<select> ::= <flat-select> | <one-hop-select> | <recursive-select>

<flat-select> ::= select <field list>
from workitems
[ where <expression> ]
[ order by <order by field list> ]
[ asof <datetime> ]

<one-hop-select> ::= select <field list>
from workitemlinks
[ where <one-hop-link-expression>  <source-expression>  <target-expression> ]
[ order by <source-target order by field list> ]
[ asof <datetime> ]
mode( mustcontain | maycontain | doesnotcontain )

<recursive-select> ::= select <field list>
from workitemlinks
where <recursive-link-expression> [ and <source-expression> <target-expression> ]
mode ( recursive | returnmatchingchildren )

<expression> ::= <expression4>

<expression4> ::= <expression3> [ or <expression4> ]
<expression3> ::= <expression2> [ and <expression3> ]

<expression2> ::=   {[ not | ever ] <expression2> }
                          | <expression1>

<expression1> ::= <conditional expression>

<conditional expression> ::=   { '(' <expression> ')' }  | <field reference name> <conditional operator> <value>  | <field reference name> [not] in '(' <value list> ')'

<value> ::= <number>
| <string>
| <datetime>

<value list> ::= <value> [ ',' <value list> ]

<conditional operator> ::=   { '=' | '<>' | '<' | '<=' | '>' | '>=' } 
                             | { [ever] [not] { like | under }} 
<link operator> ::= '=' | '<>'

<field list> ::= <field name> [ ',' <field list> ]
<order by field list> ::= <order by field> [ ',' <order by field list> ]
<source-target order by field list> ::= [ <source> |<target> ] <order by field> [ ',' <source-target order by field list> ]
<order by field> ::= <field name> [ 'asc' | 'desc' ]

<number> ::= [ '-' ] <digit>* [ '.' [ <digit>* ]] [ { e | E } [ '-' ] <digit>* ]

<string> ::=   { ''' { <anychar except '''> | '''' }* ''' } 
                 | { '"' { <anychar except '"'> | '""' }* '"' }

<datetime> ::= <string>

<source> ::= '[source].'
<target> ::= '[target].'

<one-hop-link-expression> ::= <one-hop-link-expression4> | ''

<one-hop-link-expression4> ::= <one-hop-link-expression3> [ or <one-hop-link-expression4> ]
<one-hop-link-expression3> ::= <one-hop-link-expression2> [ and <one-hop-link-expression3> ]

<one-hop-link-expression2> ::= {[ not | ever ] <one-hop-link-expression2>}
                         | <one-hop-link-expression1>

<one-hop-link-expression1> ::= <conditional-link-expression>

<conditional-link-expression> ::= { '(' <one-hop-link-expression> ')' } | <linktype-field> <link operator> <linktype-name><linktype-direction> | <linktype-field> [not] 'in  (' <linktype list> ')'

<recursive-link-expression> ::= <linktype-field> '=' <linktype-name>'-forward'

<linktype list> ::= <linktype-name><linktype-direction> [, <linktype-name><linktype-direction>]

<linktype-direction> ::= '-forward' | '-reverse'

<source-expression> ::= <source-expression4> | ''

<source-expression4> ::= <source-expression3> [ or <source-expression4> ]
<source-expression3> ::= <source-expression2> [ and <source-expression3> ]

<source-expression2> ::=   {[ not | ever ] <source-expression2> }
                          | <source-expression1>

<source-expression1> ::= <conditional-source-expression>

<conditional-source-expression> ::=   { '(' <source-expression> ')' }  | <source><field reference name> <conditional operator> <value>  | <source><field reference name> [not] in '(' <value list> ')'

<target-expression> ::= <target-expression4> | ''

<target-expression4> ::= <target-expression3> [ or <target-expression4> ]
<target-expression3> ::= <target-expression2> [ and <target-expression3> ]

<target-expression2> ::=   {[ not | ever ] <target-expression2> }
                          | <target-expression1>

<target-expression1> ::= <conditional-target-expression>

<conditional-target-expression> ::=   { '(' <target-expression> ')' }  | <target><field reference name> <conditional operator> <value>  | <target><field reference name> [not] in '(' <value list> ')'

<linktype-field> ::= '[System.Links.LinkType] = '
<select> ::= select <field list>
from workitems
[ where <expression> ]
[ order by <order by field list> ]
[ asof <datetime> ]

<expression> ::= <expression4>

<expression4> ::= <expression3> [ or <expression4> ]
<expression3> ::= <expression2> [ and <expression3> ]

<expression2> ::=   {[ not | ever ] <expression2> }
                          | <expression1>

<expression1> ::= <conditional expression>

<conditional expression> ::=   { '(' <expression> ')' }  | <field reference name> <conditional operator> <value>  | <field reference name> [not] in '(' <value list> ')'

<value> ::= <number>
| <string>
| <datetime>

<value list> ::= <value> [ ',' <value list> ]

<conditional operator> ::=   { '=' | '<>' | '<' | '<=' | '>' | '>=' } 
                             | { [ever] [not] { like | under }} 

<field list> ::= <field name> [ ',' <field list> ]
<order by field list> ::= <field name> [ asc | desc ] [ ',' <order by field list> ]

<number> ::= [ '-' ] <digit>* [ '.' [ <digit>* ]] [ { e | E } [ '-' ] <digit>* ]

<string> ::=   { ''' { <anychar except '''> | '''' }* ''' } 
                 | { '"' { <anychar except '"'> | '""' }* '"' }

<datetime> ::= <string> Insert section body here.

See Also

Concepts

Extending Work Item Tracking by Using the Client Object Model for Team Foundation