Adding custom paging to OData(v4) Web API C#

Question

I am wondering would anyone be able to help me. I am trying implement custom paging into a OData feed (oData v4). I am filling the IEnumerable from a stored procedure to which I have added a means of paging by below (Working Perfect)

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

However the issue I am having is that the below code works perfect apart from the paging is not working

[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; }

Basically I am trying to page through the results in batches of 100, taking 100 at time from the database only as needed.

When I first call that controller I get the expected results.

http://localhost:24600/Data/Orders

But when I query this nextLink (below), I get no errors but no results despite the fact there is over 50,000 results in the database and the IEnumerable results has been updated correctly.

http://localhost:24600/Data/Orders?$skip=100

I would be extremely gratefully if someone help me out with this as I am new to using odata.

Update

It looks like, it is counting the size of the first batch and taking that as the total size, even though I am setting the total size. It seems to not be able to go above the initial size.

taken from the docs: For large entity sets, the client might want to limit the number of results. For example, a client might show 10 entries at a time, with “next” links to get the next page of results. To do this, the client uses the $top and $skip options. localhost/Products?$top=10&$skip=20 — Daniel
– Daniel, Commented Nov 3, 2016 at 4:45
When I try to actually query the data (like ?$top=10&$skip=20), even when I use top), something behind the scenes is happening and not showing the IEnumerable results despite the fact they are there. For an example I am putting the next batch into the response, I can see the results when I debug by placing a break point on the return, but it is refusing to show them. The strange thing is there is no errors. — Jimbo Jones
– Jimbo Jones, Commented Nov 3, 2016 at 9:51

SeanT · Accepted Answer · 2019-02-24 09:10:09Z

I think the EnableQuery attribute is the problem. Seems like that tells the framework you want it to handle things. My example below, sans attribute, is using odata on asp.net core (I think the only real difference is using ODataFeature instead of ODataProperties)...

public class ThingController : ODataController { [HttpGet] public PageResult<Thing> GetThings(ODataQueryOptions<Thing> queryOptions) { var thingsToSkip = queryOptions?.Skip?.Value ?? 0; var pageSize = 10; var totalThings = GetThingCount(); var pageOfThings = GetThings(thingsToSkip, pageSize); var showNextLink = thingsToSkip + pageOfItems.Count < totalThings; return new PageResult<Things>(pageOfThings, showNextLink ? Request.GetNextPageLink(pageSize) : null, totalThings); } private int GetThingCount() { return 21; } private List<Things> GetThings(int skip, int take) { return Enumerable.Range(0, TotalThingCount()) .Select(r => new Thing { Id = r.ToString() }) .Skip(skip) .Take(take) .ToList(); }

GWigWam · Accepted Answer · 2016-11-03 10:46:34Z

1

You can just return the complete set (as IQueryable) and let OData handle the paging:

[EnableQuery(PageSize = 100)] public IQueryable<Order> Get() { return _OrderRepo.GetAll(CompanyID); //Assuming this returns an IQueryable }

OData knows how to handle IQueryable and will use .Top() and .Skip() to get entries, this will get converted to SQL by your queryable provider. (Presumably EF?)

edited Nov 3, 2016 at 10:46

answered Nov 3, 2016 at 10:41

GWigWam

2,2024 gold badges31 silver badges38 bronze badges

3 Comments

Jimbo Jones Over a year ago

The issue is the dataset is extremely big get all at once. it's in excess of 50k of records. Which means it's really slow if I use the out of the box stuff.

GWigWam Over a year ago

If you use Entity Framework (Is that an option?) you can use the IQueryable interface to not have to get it all at once. Something like: DbContext.Set<Order>().Where(o => o.CompanyID == cId) will return an IQuerayble, not all the data. If you pass that to OData it will query only the records needed. This means only the required data is gotten from the database.

Jimbo Jones Over a year ago

Entity Framework, not really as I need to deal with complex types, like joins and casting strings to dates and stuff. I was trying to keep my question simple

Collectives™ on Stack Overflow

Adding custom paging to OData(v4) Web API C#

2 Answers 2

Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Related