Return total count of query plus paging with Data Services

…And in the same reply.  I racked my brain for a few days trying to figure out a way to return the count of a query in the same reply with the results for my client paging control.  I started with making two queries, one for the count and one for the records.  There is a couple of issues with that. First, your sending two queries. Second, before you get the results of the second query, your count has probably changed.  On a CRUD table, count is a point in time anyway, so you can’t "count" on it being accurate.  The other issue was I had to maintain two queries, one for the count and one for the results; which was difficult to maintain, so using a Batch was not really helping. 

It finally occured to me that I might be able to stuff the count in the HTTP reply header.  As it turned out, it worked, and worked pretty well.  I was able to send one query and get results and the count in same reply.

Service WebGet method

/// <summary>
/// Returns the entries using the predicate.
/// The total count of records matching the predicate is returned in
/// Http header "EntryCount".
/// </summary>
/// <param name="predicate"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
[WebGet]
public IEnumerable<Entries> GetEntries(string predicate, string orderKeys, int skip, int take)
{
    // TODO: Security check.
    if (orderKeys == null || orderKeys.Length == 0)
        throw new DataServiceException("orderKeys required.");
    // Get the records using skip and take.
    IEnumerable<Entries> q = null;
    int count = 0;
    if (predicate == null || predicate.Length == 0)
    {
        q = this.CurrentDataSource.Entries.OrderBy(orderKeys).Skip(skip).Take(take);
        count = this.CurrentDataSource.Entries.Count();
    }
    else
    {
        q = this.CurrentDataSource.Entries.Where(predicate).OrderBy(orderKeys).Skip(skip).Take(take);
        count = this.CurrentDataSource.Entries.Where(predicate).Count();
    }

    // Get the count all records matching predicate and add to HTTP header.
    HttpContext.Current.Response.AppendHeader("EntryCount", count.ToString());
    return q;
}

 

Client side Proxy method

/// <summary>
/// Get entries using a string predicate and read the total count in the reply header.
/// </summary>
/// <param name="predicate"></param>
/// <param name="skip"></param>
/// <param name="take"></param>
/// <returns></returns>
public IEnumerable<Entries> GetEntries(string predicate, string orderBy, int skip, int take, out int count)
{ 
    // Why this work around? We have to pass an eSql string for the predicate instead of composing over IQueryable,
    // because at the server side we need to "know" the full predicate in order to get the count.
    // This also allows us to use the same query for the count and the select.
    if (predicate == null) predicate = "";      // Empty predicate on server side equals all rows.
    if (orderBy == null) throw new ArgumentNullException("orderKeys"); // order clause required.

    predicate = predicate.Replace("'", "''");   // Must add another single quote as these will be inside a single quote pair.
    string request = string.Format("GetEntries?predicate='{0}'&orderKeys='{1}'&skip={2}&take={3}", predicate, orderBy, skip, take);
    request = Uri.EscapeUriString(request);     // Escape spaces and other.
    var q = this.Execute<Entries>(request.ToRelativeUri()); // Have to Execute (not CreateQuery) so we can inspect the header here.

    // Get full record count in header.
    var qReply = (QueryOperationResponse)q;     // Downcast to get the Headers in the reply.
    Debug.WriteLine("Request URI: " + qReply.Query.RequestUri);
    string cs = qReply.Headers["EntryCount"];
    count = int.Parse(cs);
    return q;
}

 

Test

One down side here is that we have to send our query as an entity Sql string, so we don’t get the help of linq expressions or designer support.  If there was a way to get the user’s query inside a service operation, we could use that query and append .Count() to it.  TMK, there is not a way to do that with Astoria.  If anyone has ideas on that, please let me know.

// Get all entries > 1.  Take 10 and skip 0. The count of all entries > 1 will be returned so you can set your paging buttons (i.e. Next, Prev) as needed.

int count;
string pred = "it.EntryID>1";
var q = db.GetEntries(pred, "it.IsSticky desc, it.Created desc", 0, 10, out count);
Console.WriteLine("Count: {0}", count);

Advertisements
This entry was posted in C#. Bookmark the permalink.

3 Responses to Return total count of query plus paging with Data Services

  1. James says:

    Thanks so much for this. I spent a day trying to sort out the mind-boggling lack of a count facility in Data Services (MS: oh you can implement just next and previous page so your application users can just go through 5000 records a page at a time to find the one they want) and this is the best workaround I found.

  2. Unknown says:

    qswd024 brogame wow gold wow gold wow power leveling oofay.us wowmine 隔膜泵 隔膜泵 隔膜泵 磁力泵 磁力泵 磁力泵 油泵 油泵 油泵 上海展览公司 上海展览公司 上海展览公司 阀门 阀门 阀门 空调 空调 空调 胶体磨 胶体磨 胶体磨 Cosmetic surgery Cosmetic surgery 菲律宾留学 菲律宾留学 菲律宾留学 东方医院 东方医院 东方医院 隔膜泵 油泵 上海展览公司 阀门 磁力泵 qswd00

Comments are closed.