azureazure-functionsazure-cosmosdb-sqlapiazure-http-trigger

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


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.


Solution

  • 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.