Getting Row Count in ADO.Net Data Services

The recent CTP release of ADO.Net Data Services (Astoria) V1.5 contains a nice feature that allows result set counting on the server side. In this blog post, I’ll go over the various ways you can benefit from this feature in your data service.

Type of Row Count

Before going into details on how to use the feature, we should first take a look at what the server will count for you. There are two scenarios here: counting the total result set, and counting what the server actually put on the wire.

Counting the total result set is helpful when you need to know exactly how many entities exist in a set, regardless of any paging constrains you put on the query. Take the classic Northwind database for example. There are 91 Customers in the database, if you ask the server “How many customers are there in total”, you are asking for the total count on the set “Customers”, and the result will always be 91, even if you instruct the server to only give you the first 10 customers back (via the $top=10 query). This type of counting is very useful when you want to do client-driven paging. Since you must know the total number of entities before you can calculate how many page links you need to render on the control. Astoria supports counting the total result set via the $inlinecount=allpages option. As the name suggests, the count value will be returned inline with the actual result set.

The other type of counting is when you want to know before hand how many entities the server will (eventually) put on the wire when you execute the query. The reason I put “(eventually)” in that sentence will be explained later. This type of counting happens when you ask the server “How many orders will you give back if I want the first 100 orders made by customers ’ALFKI’”. The server will answer “6” in this case because there are only 6 orders under Customer ‘ALFKI’, but if you change the question to “how many orders will you give back if I want the first 5 orders made by ‘ALFKI’”, then the server will tell you “5”. This type of counting is supported in Astoria by the $count segment, and since you are only interested in the count value, the result is just a plaintext number.

In other words, $inlinecount=allpages is a query option that gives you the count neglect any paging effects ($skip, $top etc.), while $count segment will take explicitly stated paging effect into account.

Inline Counting

When you specify $inlinecount=allpages in the query option string, the server will respond will the normal syndication of result set (or a JSON block) plus the count value embedded in the results. In ATOM serialization, the count is returned in a feed level tag called “count”, under the “Metadata V2” namespace. For example, the query

"/Customers?$inlinecount=allpages&$top=5”

will give you the following tag plus 5 customers:

<m2:count xmlns:m2=”https://schemas.microsoft.com/ado/2008/11/ dataservices/metadata”>91</m2:count>

Inline Counting on the Client

On our client, if the resulting feed contains the count tag, you can extract the value by accessing the “TotalCount” property on the QueryOperationResponse object. Of course, if the count tag is not present, getting the property value will cause an InvalidOperationException to be thrown.

There are many ways for the client to generate a request to cause the server to respond with the count tag. We have provided a new API for the ALinq users called IncludeTotalCount(). The method exists on the DataServiceQuery class, and returns a new instance of DataServiceQuery that will cause the $inlinecount query option to be generated. For example, you can write:

var q = (from c in ctx.CreateQuery<Customers>(“/Customers”).IncludeTotalCount().Take(5) select c) as DataServiceQuery<Customers>;

var results = q.Execute() as QueryOperationResponse<Customers>;

long countValue = results.TotalCount;

An other way to achieve this is to use the AddQueryOption API and manually add “inlinecount” option with value “allpages”. Of course, if you just specify an URI with the correct counting option and directly execute it from the context, you will also be able to use the TotalCount property to access the count tag in the result.

It is also possible to batch requests generated by IncludeTotalCount. The individual count value can be accessed in each of the QueryOperationResponse object in the batch response.

Value Counting

The $count segment can be added to any entity sets on the server, the result is a plaintext response that represents the count of entities in that set. Different from inline counting , query options can be added to this segment to modify the number of entity to be counted. For example, given that there are 91 customers in total, the following table illustrates the behavior of $count

Since the response is in PlainText format, the Accept header should be compatible with “text/plain”. For example, “*/*” will work, but “application/json” will cause an exception to the thrown.

Value Counting on the Client

In V1, calling Count and LongCount on a DataServiceQuery<> will throw NotSupportedException. These two APIs are now implemented and mapped to the $count segment on the corresponding entity set. Note that these APIs will cause the query to execute immediately and returns the count value, hence it’s a synchronized operation and thus not available on the Silverlight Client. Here’s an example of how you can use these APIs:

var q = (DataServiceQuery<Customers>)(from c in ctx.CreateQuery<Customers>("Customers") select c);

long countValue = q.LongCount();

You can call Count or LongCount on any DataServiceQuery<> that doesn’t already have a counting option (i.e., one that’s generated by IncludeTotalCount).

Counting on Links Endpoint

The query option "inlinecount” and the “$count” segment can also be applied to $links end points. However there is no equivalent APIs on the client side. Here are some examples illustrating how:

/Customers(‘ALFKI’)/$links/Orders?$inlinecount=allpages

returns a PlainXML format with the <m2:count> tag embedded.

/Customers(‘ALFKI’)/$links/Orders/$count

returns a PlainText response “6” (there are 6 links to customer ALFKI’s orders)

Counting with Expansion

When you specify inline counting together with expansions (via $expand), the count value represents the number of entities that exists in the outermost set. Each entity will be expanded as normal, but there won’t be a count tag for each of the expanded set.

When you specify value counting ($count segment), expansion is entirely ignored.

Counting with Server Driven Paging

Server driven paging is a new feature in V1.5 that allows server side enforced paging. This means on SDP enabled services, you may be given back a partial set for any request you make, together with a link on where to get the next part of the set. Ideally, SDP will not affect counting, hence in the beginning of the article I wrote how many entities the server will "eventually" give back. However this is not the case right now. When you enable SDP, you will find that both $inlinecount and $count will be affected by the partial set effect. This however is a known issue and we are tracking it right now.

Comments

• Anonymous
  March 17, 2009
  PingBack from http://blog.a-foton.ru/index.php/2009/03/18/getting-row-count-in-adonet-data-services/

• Anonymous
  March 17, 2009
  Peter Qian, the newest member of the ADO.NET Data Services, now has his own blog . In his initial post

• Anonymous
  March 18, 2009
  Peter Qian, a developer on the data services team, has posted a nice write up describing the row count

• Anonymous
  December 05, 2011
  Does the service have access to the number of records and the size of the request of a given query?  I need to keep statistics of the service usage and would need to know how many rows were returned per entity and how large the result set for that entity was.

• Anonymous
  December 06, 2011
  Server has access to the original request through the HttpContext. However, you do not have the final query object after it's fully composed unless you have your own query provider. So in order to do the logging, you need to at least wrap the query provider: blogs.msdn.com/.../tip-55-how-to-extend-an-iqueryable-by-wrapping-it.aspx