使用Cosmos DB绑定的Azure函数- ORDER BY作为路由参数传递时不工作

Question

我正在尝试从特定字段排序的容器中获取项目列表。

这是我的CosmosDB绑定SQL查询:

[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);
}

当我用以下参数调用这个API时:

https://../api/events/[e-mail]/ASC/event

我得到以下错误:

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.

=比;当我简单地从SQL查询中删除ORDER BY子句时，

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

和调用相同的URL参数，https://../api/events/[e-mail]/ASC/event

我甚至可以看到在控制台上打印的值:

Console.WriteLine(PartitionKey); // [e-mail]
Console.WriteLine(Order);        // ASC
Console.WriteLine(SearchTerm);   // event

=比;此外，当我硬编码值'ASC'或'DESC'代替{Order}时，事情就像预期的那样工作。

我找不到任何关于为什么这不起作用的信息。任何建议都会很有帮助的。

提前感谢。

Answer 1

看起来不支持。如果您看一下这里的实现，它将查询参数转换为SQL参数:

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

这对ORDER BY子句不起作用。

或者，你可以使用DocumentClient绑定，但是你必须自己编写查询:

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);
}

老实说，不确定是不是更糟。如果您知道查询不会返回太多项目，您可以为order by子句设置一个默认值，并通过编程方式将其恢复:

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

它将允许您保留现有的实现。