How to use LIMIT keyword as using in ms sql by WIQ

Question

I'm working on TFS API, I don't know that TFS API have any things like LIMIT keyword or no.I need it for paging.

Thanks

Answer 1

There is nothing equivalent to the SQL LIMIT keyword in TFS WIQL, you will need to implement the paging yourself.

One approach would be to retrieve all the results on the first access, and cache them and page them yourself.

Another approach would be to dynamically construct the WIQL query each time your user pages. For example:

1. Run a WIQL query to return just the work item ID's that match the query. SELECT [System.Id] FROM WorkItems WHERE <conditions>
2. Cache that list of IDs
3. Break that list of IDs into groups that match your paging size
4. Each time your user pages, explicitly request the work item's by ID. SELECT <fields> FROM WorkItems WHERE [System.Id] IN (10,11,12,13,14,15)

Depending on what you are trying to achieve, you should also know that the TFS Work Item Tracking API implements paging/lazy loading under the covers for field values, to maximize response times. You can see how this works by attaching a network sniffer and scrolling a large Work Item query in Visual Studio.

See Paging of Field Values for more information:

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.

Answer 2

I had the same issue. I applied Take on the sequence to limit the results made available by the query. This, unfortunately, doesn't actually limit the results returned by the database, but since the feature is not supported, it provides a workable option.

Me.mWorkItems.Query(pstrSQL).OfType(Of WorkItem)().Take(5)

Using Skip and Take together could work for paging.