locked
Custom Paging using a stored procedure with OData feed C# without Entity Framework RRS feed

  • Question

  • User-1102851594 posted

    I am wondering if anyone can help me, for some time now I have being trying to figure out how to implement custom paging in a OData feed (v4) Web API 2 to feed a power bi feed and having no success.

    The data is derived from a database first, database and is a combination of 5 tables using joins, which makes it not suitable to use with Entity Framework apart from being really slow with Entity Framework (45k of records out of one controller).

    I have tried many different approaches from, setting the total amount of records to trick the framework and padding the paged results with empty members of the list, to the more basic example below. However I still can not the get client (Power BI) take the paged results correctly without returning an extremely large amount of records from the controller. Please see a simplified query and code, any help would be extremely welcome as there appears to be no clear examples of how to do this without using Entity Framework.

    The below code works but I keep having variants of the same problem the framework is doing the paging on the list after it returns, despite whatever I do before that

    T-SQL query:

    SELECT
    *
    FROM
    (SELECT
    ROW_NUMBER() OVER (ORDER BY OrderID) AS RowNum,
    *
    FROM
    dbo.Order
    WHERE
    CompanyID = @CompanyID) AS RowConstrainedResult
    WHERE
    RowNum >= @Start AND RowNum <= @Finish
    ORDER BY
    RowNum

    The controller which points to a repo which calls the above query

    [EnableQuery]
    public async Task<PageResult<Order>> GetOrders(ODataQueryOptions<Order> queryOptions)
    {
    int CompanyID = User.Identity.GetCompanyID().TryParseInt(0);

    ODataQuerySettings settings = new ODataQuerySettings()
    {
    PageSize = 100,
    };

    int OrderCount = _OrderRepo.GetOrderCount(CompanyID);
    int Skip = 0;

    if (queryOptions.Skip != null)
    {
    Skip = queryOptions.Skip.Value;
    }

    IEnumerable<Order> results = await _OrderRepo.GetAll(CompanyID, Skip, 100);

    IQueryable result = queryOptions.ApplyTo(results.AsQueryable(), settings);

    Uri uri = Request.ODataProperties().NextLink;
    Request.ODataProperties().TotalCount = OrderCount;

    PageResult<Order> response = new PageResult<Order>(
    result as IEnumerable<Order>,
    uri, Request.ODataProperties().TotalCount);

    return response;
    }




    Saturday, February 3, 2018 1:26 AM

All replies