7

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

knowwebapp.com
  • 95
  • 4
  • 16

2 Answers2

5

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.

granth
  • 8,919
  • 1
  • 43
  • 60
  • 1
    Oh, that's stupid. I'm using VSO REST API. I can get all work items at once (in one request), since it doesn't return more than 20,000 items (response contains an error) and IDs start from ~180,000 and end around ~310,000. Also there can be holes in IDs, depending on query conditions. – David Ferenczy Rogožan Aug 27 '15 at 15:06
  • If I would use an API client library with paging instead of REST API, how does paging work there? Does it page results locally (i.e. requests everything from VSO at once) or does it request only `PageSize` number of results? – David Ferenczy Rogožan Aug 27 '15 at 15:10
  • 1
    It's annoying that there is no LIMIT functionality, and yet VSO limits a query of results to 20K as an error. – Jmoney38 Oct 04 '16 at 17:23
  • 2
    @DavidFerenczyRogožan A bit late to the party, but you can limit the response on the REST API using `$top=200` for example. If you order your query on `System.Id` and build a dynamic query where you store the last ID returned from the previous call you're all set. – riezebosch Apr 28 '20 at 19:28
1

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.

Mario
  • 6,572
  • 3
  • 42
  • 74