DocumentDb: Get the first document

Question

Is there an effective way of getting the first document from a query? Typically this would be using FirstOrDefault() with LINQ collects or a TOP in SQL.

Say the collection looks something like this:

{
    name: "John",
    position: 3
},
{
    name: "Mary",
    position: 1
},
{
    name: "Peter",
    position: 2
}

I need to retrieve only the single document with the highest value position. I.e. the "Mary" document in this case.

Using the C# SDK, I would have to perform something like this:

Client.CreateDocumentQuery<T>(Collection.DocumentsLink)
    .Where(somethingOtherCriteria);
    .OrderByDescending(x => x.Position)
    .AsEnumerable()
    .FirstOrDefault());

This will retrieve ALL the documents in the collection (after applying the relevant filter) and then fetching the first from the list. This could still bring in thousands of irrelevant documents over the wire.

Is it not possible to execute FirstOrDefault() on the server?

Answer 1

One option is to set the max response size to 1, and grab only the first page from the query iterator:

var query = client.CreateDocumentQuery<Family>(collectionLink, "SELECT * FROM myCollection", new FeedOptions { MaxItemCount = 1 }).AsDocumentQuery();
var feedResponse = await query.ExecuteNextAsync<Family>();

Answer 2

You're close. Skip the AsEnumerable() and do this:

var query = Client.CreateDocumentQuery<T>(Collection.DocumentsLink)
    .Where(somethingOtherCriteria);
    .OrderByDescending(x => x.Position)
    .AsDocumentQuery();

while (query.HasMoreResults)
    foreach (var document in await q.ExecuteNextAsync<T>(cancellationToken))
        return document;

If you do it this way, you won't actually return any results but the one you want. The catch is, you do have to run ExecuteNextAsync() against every page of results until you get the page with your desired result -- but the other results won't come over the wire and won't get deserialized into a C# reference type.

Answer 3

According to https://feedback.azure.com/forums/263030-azure-cosmos-db/suggestions/6771773-add-support-for-single-entity-retrieval-instead-of

Client.CreateDocumentQuery<T>(Collection.DocumentsLink)
    .Where(somethingOtherCriteria);
    .OrderByDescending(x => x.Position)
    .Take(1)
    .AsEnumerable()
    .FirstOrDefault());

Answer 4

You're looking for Take(), that's the Linq function that maps to TOP in the query. So you want something like

        var query = client.CreateDocumentQuery<MyType>(collectionAddress)
            .Where(x => x.Value == myValue)
            .Take(1);