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

Question

I am wondering if anyone can help me, for some time now I have been trying to figure out how to implement custom paging in an 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 appear 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 Stored Procedure :

CREATE PROCEDURE [dbo].[GetOrders] @CompanyID int,

                                   @Skip INT,

                                   @Take INT

AS

BEGIN 

SET NOCOUNT ON;

SELECT *

FROM Orders 

WHERE CompanyID = @CompanyID

ORDER BY t.OrderID

OFFSET @Skip ROWS FETCH NEXT @Take  ROWS ONLY

END

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;

} 

The paging by the framework is done after this point return response;

Answer 1

• We need to Interrupt the framework by extending EnableQueryAttribute (Assuming you turn off filtering etc. and set max page size). In the paging query you need to go one above your page to fire an internal mechanism, this solution is just a workaround. The key is to set the Take to 0 before you "ApplyTo".

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

• PagingAttribute

public sealed class PagingAttribute : EnableQueryAttribute

{

    public override IQueryable ApplyQuery(IQueryable queryable, ODataQueryOptions queryOptions)

    {

        var result = default(IQueryable);

        var originalRequest = queryOptions.Request;

        var skip = queryOptions.Skip == null ? 0 : queryOptions.Skip.Value;

        var take = queryOptions.Top == null ? PageSize : queryOptions.Top.Value;

        queryOptions = ODataQueryOptionsUtilities.Transform(queryOptions, new ODataQueryOptionsUtilitiesTransformSettings { Skip = (map, option) => default(int?) });

        if (queryOptions.Request.ODataProperties().TotalCount != null)

            originalRequest.ODataProperties().TotalCount = originalRequest.GetInlineCount();

        result = queryOptions.ApplyTo(queryable);

        if (skip + take <= originalRequest.ODataProperties().TotalCount)

            originalRequest.ODataProperties().NextLink = NextPageLink.GetNextNewPageLink(originalRequest, (skip + take));

        return result;

    }

}

• In the controller, set these things as shown below.

originalRequest.ODataProperties().TotalCount = Query.Item1; // Total size of all records to be returned originalRequest.SetInlineCount(Query.Item1);