Azure用Cosmos DB绑定功能 - 订单在以路线参数为路线时不工作

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/ [email＆nbsp; procearted] /asc/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.

我

收到 ，从c中选择 * c.email = {partitionKey}并包含（c.Title，{searchTerm}）

并使用相同的URL参数调用， https：//.. //../api/events/ [email＆nbsp; procearted] /asc/asc/event

我什至可以看到在console中打印的值

   Console.WriteLine(PartitionKey); // [email protected]
   Console.WriteLine(Order);        // ASC
   Console.WriteLine(SearchTerm);   // event

：或“ desc”代替 {order} ，事情只是工作预计，

我找不到任何建议

。

Answer 1

看起来不受支持。如果您查看实现在这里，它正在转换查询参数到SQL参数：

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

这与子句的顺序无法使用。

或者，您可以使用 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);
}

老实说，不确定是否会更糟。如果您知道查询不会返回太多项目，则可以通过子句对该订单的默认值进行编程恢复：

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

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