Can I enable OData Query pagination with a method that returns DataTable?

Question

User1734332538 posted

Hi All,

I am very new to developing services with WebAPI, and have been tasked with converting our existing ASMX service to RESTful (read it as writing a WebAPI2 layer)... :P

I have the Get method completed and returning the DataTable that is extracted from the pre-existing back-end methods. I am now trying to add pagination to it, and have been trying to enable OData querying on the method. The service is written in WebAPI2 in Asp.Net 4.7.

I have converted my method to return an IQueryable value instead of DataTable. But it still does not work. All online tutorials deal with services leveraging entity objects, but i tried to replicate the steps - with no success.

Any help is REALLY appreciated!!

Cheers!

Answer 1

User36583972 posted

Hi Tim.Fdo,

The following articles described the steps to build Paging With OData And ASP.NET Web API.

We will use keyword $top and $skip to get the pagination data.

Paging With OData And ASP.NET Web API
https://www.c-sharpcorner.com/article/paging-with-odata-and-Asp-Net-web-api/

Paging with ASP.NET Web API OData:
https://blogs.msdn.microsoft.com/youssefm/2013/02/19/paging-with-asp-net-web-api-odata/

Best Regards,

Yong Lu

Answer 2

User1734332538 posted

Hi Yohann,

Thank you for your response!

The issue is, my methods are NOT bound to a model (unlike all the tutorials) - my methods call a different method that returns a DataTable. 

Based on the tutorials, I modified my API method to return an IQueriable as below, but I can't add the method attribute as [EnableQuery], or [Queryable] or anything. So when I call the API from the client (with $top & $skip), it does not paginate but keeps returning the full result instead.

myDataTable.AsEnumerable().AsQueryable()

As I am new to this I guess I am asking a two-part question here:

1. Is it possible to use $top, $skip automatically with a non-model, DataTable-based, WebAPI2 method?
2. If yes, what references / using statements do I need to have in order to enable OData pagination queries on my controller methods (I created it using an empty WebAPI2 controller template in VS2017)?

Of course I could manually capture the query string for $top & $skip and call .Skip() and .Take() on the IEnumerable of my DataTable, but that would be my FINAL option....

Thanks!!

Answer 3

User36583972 posted

Hi Tim.Fdo,

but I can't add the method attribute as [EnableQuery], or [Queryable] or anything. So when I call the API from the client (with $top & $skip), it does not paginate but keeps returning the full result instead.

Is it possible to use $top, $skip automatically with a non-model, DataTable-based, WebAPI2 method?

No, it is impossible.

To enable OData query options globally, call EnableQuerySupport on the HttpConfiguration class at startup:

config.EnableQuerySupport();

And we need to add the [Queryable] attribute to the action method. The EnableQuerySupport method enables query options globally for any controller action that returns an IQueryable type.

For more detailed, please see:

Supporting OData Query Options in ASP.NET Web API 2:
https://docs.microsoft.com/en-us/aspnet/web-api/overview/odata-support-in-aspnet-web-api/supporting-odata-query-options

Best Regards,

Yong Lu