Integrating Cosmos DB with OData using Azure Http trigger function app

Question

Objective: implement oData Azure Http trigger function to decrease latency.

I have 3.5 lacks records in cosmosdb and querying the data using azure Http Trigger function app.

I am querying CosmosDB data in via azure DocumentDB client in azure Http Trigger Function.

        public static async Task<IActionResult> MachinesByRegion_Get(
        [HttpTrigger(AuthorizationLevel.Function, "get", Route = "Region/Machines")]HttpRequest req,
        ILogger log)
    {

        log.LogInformation("GetMachinesByRegion function Started to process a request.");

        // check parameters
        if (string.IsNullOrEmpty(req.Query["PageSize"]) || string.IsNullOrEmpty(req.Query["PageNumber"]))
            return new BadRequestObjectResult(new PostActionResponse(400, "Please provide valid PageSize and PageNumber"));

        // Number of documents per page
        int pageSize = int.Parse(req.Query["PageSize"]);
        Dictionary<string,string> field = new Dictionary<string, string>();
        string columns = "*";
        if (!string.IsNullOrEmpty(req.Query["Fields"]))
        {
            string columnstring = req.Query["Fields"].ToString();
            field.Add("Fields", columnstring);
            string[] columnarr = columnstring.Split(',');
            int arraylength = columnarr.Length;

            // Build Selection columns
            if (arraylength != 0)
            {
                StringBuilder strinbuilder = new StringBuilder("c.id, ");
                int i = 1;
                foreach (string value in columnarr)
                {
                    strinbuilder.Append($"c.{value.Replace(" ", String.Empty)}");
                    if (arraylength > i)
                        strinbuilder.Append(", ");
                    i++;
                }
                columns = strinbuilder.ToString();
            }
        }

        // Parse Query Parameter
        int pagenumber = int.Parse(req.Query["PageNumber"]);

        int offset = pagenumber == 1 ? 0 : (pagenumber - 1) * pageSize;

        // Bulid query string based on request
        string sqlstr = string.Format($"SELECT {columns} FROM c ORDER BY c.id ASC OFFSET {offset} LIMIT {pageSize}");


        Helper.TraceLogWriter(log, "Summary:");
        Helper.TraceLogWriter(log, "--------------------------------------------------------------------- ");
        Helper.TraceLogWriter(log, $"Collection : {DBConstants.DataBaseName.Telemetry}.{DBConstants.ContainerName.KomatsuMachine}");
        Helper.TraceLogWriter(log, $"The Query String : {sqlstr}");
        Helper.TraceLogWriter(log, "--------------------------------------------------------------------- ");
        Helper.TraceLogWriter(log, "");

        try
        {
            // Feed Options 
            var options = new FeedOptions
            {
                MaxItemCount = pageSize,
                MaxDegreeOfParallelism = -1,
                EnableCrossPartitionQuery = true,
            };

            // Create CosmosDB Connection URI
            Uri collectionUri = UriFactory.CreateDocumentCollectionUri(databaseId: DBConstants.DataBaseName.Telemetry, collectionId: DBConstants.ContainerName.KomatsuMachine);

            ConnectionPolicy connPolicy = new ConnectionPolicy
            {
                ConnectionMode = ConnectionMode.Direct,
                ConnectionProtocol = Protocol.Tcp
            };

            using var client = new DocumentClient(new Uri(BaseRepository.EndpointUri), BaseRepository.PrimaryKey, connPolicy);
            // Get the List of Items against the Query string
            var results = client.CreateDocumentQuery(collectionUri, sqlstr, options).ToList();
            int records = results.Count;
            Helper.TraceLogWriter(log, $"Retrieved Documents: {records}");

            Helper.TraceLogWriter(log, $"Sample Document: {JsonConvert.DeserializeObject(JsonConvert.SerializeObject(results[0]))}");

            await Task.Yield();

            // Build base url                    
            var pages = new PageLinkBuilder($"{BaseRepository.BaseUrl}/api/Region/Machines", pagenumber, pageSize, records, field);

            return (ActionResult)new OkObjectResult(new PagedResult
            {
                PageNumber = pagenumber,
                PageSize = records,
                Pages = pages,
                Results = results
            });
        }
        catch (Exception ex)
        {
            Helper.TraceErrorWriter(log, $"Exception caught in GetMachinesByProduct: {ex.Message}");
            return new UnprocessableEntityObjectResult(new PostActionResponse(422, "Exception in CosmosDB, Please check logs"));
        }
    }

I just want to enable oData to optimistic performance in case of select, filter and order etc.

in cosmosDB, document have many fields if enable, it will reduce the size of the data.

i had a walk-through on this.
Please Click Here

This is for Web API. but i need for azure Http trigger function app

Answer 1

the link you provide is only for the "Where" predicate it will not reduce the selection at all because it's an IEnumerable and not an IQueryable :

public async Task<IEnumerable<T>> GetItemsAsync(Expression<Func<T, bool>> predicate)
    {
        IDocumentQuery<T> query = client.CreateDocumentQuery<T>(
            UriFactory.CreateDocumentCollectionUri(DatabaseId, CollectionId),
            new FeedOptions { MaxItemCount = -1 })
            .Where(predicate)
            .AsDocumentQuery();

        List<T> results = new List<T>();
        while (query.HasMoreResults)
        {
            results.AddRange(await query.ExecuteNextAsync<T>());
        }

        return results;
    }

The only way i could imagine you can do that is by using EF Core For Cosmos DB

then you could use Azure Functions OData Extension with an IQueryable.

With that you should have EF Core providing specific select for you directly in CosmosDB.

But this will only work if you use EF Core from the start with Cosmos DB because EF Core have a specific formating for the PK, Id and discriminator in Cosmos DB.