Azure Functions with Cosmos DB Binding - ORDER BY not working when passed as route parameter

Question

I'm trying to fetch a list of items from a container ordered by a specific field.

Here's my CosmosDB Binding with SQL Query:

[FunctionName("FilterEvents")]
public static IActionResult FilterEvents(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "events/{PartitionKey}/{Order}/{SearchTerm}")] HttpRequest req,
    [CosmosDB(
            databaseName: Constants.DatabaseName,
            collectionName: Constants.ContainerName,
            ConnectionStringSetting = "CosmosDBConnectionString",
            SqlQuery = "SELECT * " +
                "FROM c " +
                "WHERE c.email = {PartitionKey} AND CONTAINS(c.title, {SearchTerm})" +
                "ORDER BY c.participantsCount {Order}"
        )] IEnumerable<Event> events,
    string PartitionKey,
    string Order,
    string SearchTerm,
        ILogger log)
{
   ...
   Console.WriteLine(PartitionKey);
   Console.WriteLine(Order);
   Console.WriteLine(SearchTerm);
}

When I invoke this API with this parameters:

https://../api/events/someone@gmail.com/ASC/event

I get the following error: :(

System.Private.CoreLib: Exception while executing function: FilterEvents. 
Microsoft.Azure.WebJobs.Host: Exception binding parameter 'events'.
Microsoft.Azure.DocumentDB.Core: Message: 
{
 "errors":[
 { 
  "severity":"Error",
  "location": {
    "start":101,
    "end":107},
    "code":"SC1001",
    "message":"Syntax error, incorrect syntax near '@Order'."
  }
 ]
}
[2022-07-07T13:49:53.666Z] ActivityId: 2a1a4919-f6e9-4b10-81b3-2ff2aa9d0159, Microsoft.Azure.Documents.Common/2.14.0, Windows/10.0.22621 documentdb-netcore-sdk/2.13.1.

=> When I simply remove that ORDER BY clause from my SQL Query,

i.e, SELECT * FROM c WHERE c.email = {PartitionKey} AND CONTAINS(c.title, {SearchTerm})

and invoke with the same URL Parameters, https://../api/events/someone@gmail.com/ASC/event

I could even see the values getting printed in the console:

   Console.WriteLine(PartitionKey); // someone@gmail.com
   Console.WriteLine(Order);        // ASC
   Console.WriteLine(SearchTerm);   // event

=> Also, when I hardcode the value 'ASC' or 'DESC' in place of {Order}, things just work as expected.

I couldn't find any information on why this is not working. Any suggestion would be much helpful here.

Thanks in advance.

Answer 1

It doesn't looks like it is supported. If you look at the implementation here, it is converting the query parameters into SQL parameters:

foreach (var parameter in attribute.SqlQueryParameters)
{
    queryDefinition.WithParameter(parameter.Item1, parameter.Item2);
}

This won't work with ORDER BY clause.

Alternatively, you could use the DocumentClient binding but then you'll have to write the query yourself:

using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using System.Collections.Generic;
using Microsoft.Azure.Documents.Client;
using Microsoft.Azure.Documents.Linq;
using Microsoft.Azure.Documents;
...
[FunctionName("FilterEvents")]
public static async Task<IActionResult> FilterEventsAsync(
    [HttpTrigger(AuthorizationLevel.Anonymous, "get", Route = "events/{PartitionKey}/{Order}/{SearchTerm}")] HttpRequest req,
    [CosmosDB(ConnectionStringSetting = "CosmosDBConnectionString")] DocumentClient client,
    string PartitionKey,
    string Order,
    string SearchTerm,
    ILogger log)
{
    var querySpec = new SqlQuerySpec(
        $"SELECT * FROM c WHERE c.email = @PartitionKey AND CONTAINS(c.title, @SearchTerm) ORDER BY c.participantsCount {Order}"
        , new SqlParameterCollection(new[] { 
            new SqlParameter("@PartitionKey", PartitionKey)
            , new SqlParameter("@SearchTerm", SearchTerm)
        })
    );

    var collectionUri = UriFactory.CreateDocumentCollectionUri(Constants.DatabaseName, Constants.ContainerName);
    IDocumentQuery<Event> query = client.CreateDocumentQuery<Event>(collectionUri, querySpec)
        .AsDocumentQuery();

    var events = new List<Event>();
    while (query.HasMoreResults)
    {
        foreach (Event result in await query.ExecuteNextAsync())
        {
            events.Add(result);
        }
    }

    return new OkObjectResult(events);
}

To be honest, not sure if it worse it. If you know the query won't returned too many items, you could have a default value for the order by clause and revert it programmatically:

events.Reverse();
return new OkObjectResult(events);

It will allow you to keep your existing implementation.