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/[email protected]/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/[email protected]/ASC/event

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

   Console.WriteLine(PartitionKey); // [email protected]
   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.

CodePudding user response：

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.