Unable to Implement OData in Azure Function App for Excel Integration

Question

I have working odata app service which I am currently using to get data to excel and power BI, but I want to implement the odata in azure function app v4 but I could not get the response in function app like in server( no @odata.context just getting the array of data as response also excel and power BI says the URL does not point to a OData service or a feed)

Is there a way to make OData work in function app

I want a proper odata response from function app

Answer 1

Is there a way to make OData work in function app

I have created a HTTP Trigger function with runtime stack .NET 8.0 and also configured ODATA within the function.

The following function code worked successfully.

Function code:

public class Function1
{
    private readonly ILogger<Function1> _logger;

    public Function1(ILogger<Function1> logger)
    {
        _logger = logger;
    }

    [Function("GetProducts")]
    public IActionResult Run(
        [HttpTrigger(AuthorizationLevel.Function, "get", Route = "products")] HttpRequest req)
    {
        // Log information for debugging purposes
        _logger.LogInformation("C# HTTP trigger function processed a request.");

        // Sample list of products (can be replaced with data from a DB like Cosmos DB or SQL)
        var data = new List<Product>
    {
        new Product() { Title = "Mountain Bike SERIOUS ROCKVILLE", Category = "Mountain Bicycle" },
        new Product() { Title = "Mountain Bike eléctrica HAIBIKE SDURO HARD SEVEN", Category = "Mountain Bicycle" },
        new Product() { Title = "Sillín BROOKS CAMBIUM C15 CARVED ALL WEATHER", Category = "Sillin" },
        new Product() { Title = "Poncho VAUDE COVERO II Amarillo", Category = "Chaquetas" },
    };

        // Apply OData query options (like $filter, $orderby, etc.)
        var queryOptions = req.Query;
        var filteredData = ApplyODataQueryOptions(data.AsQueryable(), queryOptions);

        // Prepare the OData response (with metadata)
        var response = new ODataResponse
        {
            Context = "https://yourfunctionapp.azurewebsites.net/odata/$metadata#products", // Adjust this URL
            Count = filteredData.Count(),
            Value = filteredData.ToList()
        };

        // Return the OData response as JSON
        return new JsonResult(response)
        {
            StatusCode = 200
        };
    }

    // Helper function to apply OData query options like $filter, $orderby, etc.
    private IQueryable<Product> ApplyODataQueryOptions(IQueryable<Product> data, IQueryCollection queryOptions)
    {
        if (queryOptions.ContainsKey("$filter"))
        {
            // Apply filtering logic (basic for illustration; you can expand this)
            var filterValue = queryOptions["$filter"].ToString(); // Convert StringValues to a string
            if (filterValue.Contains("Category eq"))
            {
                // Extract the category value directly (without using Split)
                var categoryIndex = filterValue.IndexOf("Category eq") + "Category eq".Length;
                var category = filterValue.Substring(categoryIndex).Trim(); // Extract the category part

                // Clean the value by removing quotes
                if (category.StartsWith("'") && category.EndsWith("'"))
                {
                    category = category.Substring(1, category.Length - 2); // Remove single quotes
                }

                // Apply filter based on the category
                data = data.Where(p => p.Category == category);
            }
        }

        if (queryOptions.ContainsKey("$orderby"))
        {
            var orderbyValue = queryOptions["$orderby"].ToString(); // Convert StringValues to a string
            if (orderbyValue.Contains("Title"))
            {
                data = data.OrderBy(p => p.Title);
            }
        }

        return data;
    }

    // OData response format
    public class ODataResponse
    {
        public string? Context { get; set; }
        public int Count { get; set; } // Total count of results
        public IEnumerable<Product>? Value { get; set; } // Actual data
    }

    // Define the Product class directly here (or can be in a separate file if preferred)
    public class Product
    {
        public string? Title { get; set; }
        public string? Category { get; set; }
    }
}

The status of the function check below:

Output: