How to construct IQueryable query using Linq when I just need count without reading all documents in Document-Db database?

Question

I need the number of documents stored in a collection in my Azure Cosmos Db database. How can I get the count using LINQ query on the IQueryable object?

docDbClient.CreateDocumentQuery<TResult>().Count()

If I do above, I am unable to follow it up with .AsDocumentQuery() method.

`Count` returns a single integer - why would you want to *follow* it by something that wants to work with a "group" of items? — Damien_The_Unbeliever, Jul 19 '17 at 07:23
One more thing....DocumentDB API natively supports `Count` queries. You should try using that instead of doing the count this way. — Gaurav Mantri, Jul 19 '17 at 07:25
So, I have to construct the SQL statement myself in this case, instead of using the LINQ provider? — aman, Jul 19 '17 at 08:30
Your query should already do precisely what you want - get a count without retrieving anything else. — Sergey Kalinichenko, Jul 19 '17 at 10:48
But to use this IOrderedQueryable with Document-DB, I must call the AsDocumentQuery(), right? — aman, Jul 19 '17 at 13:19

score 3 · Accepted Answer · answered Jul 20 '17 at 08:05

This is my async implementation (use Count, for the sync version):

var count = await DocumentClient.
            CreateDocumentQuery<T>(CreateCollectionUri(), CreateFeedOptions()).
            Where(predicate).
            CountAsync();

where predicate is Expression<Func<T, bool>>

score 3 · Answer 2 · edited Aug 28 '21 at 11:03

3

Another way to acheive that is:

using Microsoft.Azure.Cosmos.Linq; // required for CountAsync()
//...
cosmosClient
    .GetContainer(databaseName, containerName)
    .GetItemLinqQueryable<MyRecord>(true)
    .Where(r => r.Name == "John")
    .Where(r => r.Status == MyRecordStatus.Approved)
    .CountAsync()

edited Aug 28 '21 at 11:03

Martin Meeser

2,784
2
28
41

answered Oct 28 '20 at 10:01

J.Wincewicz

849
12
19

is there any specific reason why you are allowing synchronous execution (`.GetItemLinqQueryable(true)`)? – Martin Meeser Aug 28 '21 at 11:05
Yes, I can not recall it now, but I tested that and did not work for some reason. Synchronous execution was used on purpose. – J.Wincewicz Sep 30 '21 at 09:02

score 2 · Answer 3 · edited Jun 20 '20 at 09:12

You can try to use the following code to count the number of documents in your collection.

client = new DocumentClient(new Uri(EndpointUri), PrimaryKey);

IQueryable<int> total = client.CreateDocumentQuery<int>(UriFactory.CreateDocumentCollectionUri("testdb", "testcoll"), "SELECT Value count(1) FROM c", new FeedOptions() { EnableCrossPartitionQuery = true });

Console.WriteLine("total: " + total.AsEnumerable().FirstOrDefault());

Query result:

If you capture the request (using fiddler etc), you will find that the client sdk send the same query {"query":"SELECT VALUE [{\"item\": Count(1)}]\r\nFROM root"} to the server when you execute your code docDbClient.CreateDocumentQuery<TResult>().Count().

How to construct IQueryable query using Linq when I just need count without reading all documents in Document-Db database?

3 Answers3

Linked