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.