Send SqlQuery in Azure Function's DocumentDB Attribute

Question

I have an Azure Function that uses the DocumentDB attribute to connect to Cosmos DB. I'm using the Azure Functions for Visual Studio 2017 tooling. Here is the simple Function

    [FunctionName("DocumentDbGet")]
    public static HttpResponseMessage Run([HttpTrigger(AuthorizationLevel.Function, "get")]HttpRequestMessage req, TraceWriter log,
        [DocumentDB("FunctionJunctionDemo", "Demo")]IEnumerable<DemoModel> items)
    {
        //Can perform operations on the "items" variable, which will be the result of the table/collection you specify
        string name = req.GetQueryNameValuePairs()
            .FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
            .Value;

        var item = items.Where(x => x.FirstName == name);
        return req.CreateResponse(HttpStatusCode.OK);
    }

I want to be able to pass a SqlQuery as one of the parameters of the DocumentDB attribute like so:

    [FunctionName("DocumentDbGet")]
    public static HttpResponseMessage Run([HttpTrigger(AuthorizationLevel.Function, "get")]HttpRequestMessage req, TraceWriter log,
        [DocumentDB("FunctionJunctionDemo", "Demo", SqlQuery = $"select * from c where c.Id = {Id}")]IEnumerable<DemoModel> items)
    {
        //Can perform operations on the "items" variable, which will be the result of the table/collection you specify
        string name = req.GetQueryNameValuePairs()
            .FirstOrDefault(q => string.Compare(q.Key, "name", true) == 0)
            .Value;

        var item = items.Where(x => x.FirstName == name);
        return req.CreateResponse(HttpStatusCode.OK);
    }

I've only seen 1 example do this and reported it was supposedly working. https://github.com/Azure/Azure-Functions/issues/271 The "error" I receive is it doesn't recognize anything named SqlQuery as a possible parameter.

I have looked at the documentation for Azure Function Input bindings https://docs.microsoft.com/en-us/azure/azure-functions/functions-bindings-documentdb#input-sample-with-multiple-documents which shows an output in the function.json file containing a sqlQuery attribute. How did that get in there?

If it isn't possible to pass in a SqlQuery in the DocumentDB attribute, what would be the best practice to filter the results up front to avoid returning an entire collection and then running it through a LINQ query?

Mikhail Shilkov Mikhail Shilkov · Accepted Answer · 2017-08-02T21:59:14

You need to reference 1.1.0-beta version of Microsoft.Azure.WebJobs.Extensions.DocumentDB NuGet package (or later).

In that version SqlQuery is a valid parameter of DocumentDB attribute. You code compiles for me, if I remove $ sign before select string:

[DocumentDB("FunctionJunctionDemo", "Demo", SqlQuery = "select * from c where c.Id = {Id}")]

You don't need $ - it's used for string interpolation in C#, not something you want to do here.

Send SqlQuery in Azure Function's DocumentDB Attribute

1 Answers