Finding Bugs, Tasks, and Other Work Items by Using the Work Item Query Language and the Client Object Model for Team Foundation

You can find 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 T-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");

In this topic

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.

Dica

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 noteCaution
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.

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();

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.

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.");

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."); 
}

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.

Observação

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.

See Also

Concepts

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