Grid with Odata using top and skip to limit data in SQL query while still allowing paging

[Andrew]

Good day,

We have Blazor WASM Hosted project using a Grid using SfDataManager ODataV4 Adaptor that pulls from MSSQL in the controller. The functions work correctly for the paging that limits the number of records returned from the controller to the frontend.

We would like to improve performance further by limiting the number of records return from SQL as well and we've done this by handing the top and skip values the SQL query using OFFSET @skip ROWS FETCH NEXT @top ROWS ONLY.

This however causes the obvious issue of the grid only thinking that the total items are now the limited set so no pages are generated. We tried overriding this in the DataBound event and setting the grids TotalItemCount value manually which generate the pages but on paging does not show the next rows even though the controller is called with the correct skip value and data is returned.

What is the best way to handle this as its a huge performance gain due to the amount of rows SQL still has to process. I've attached a sample proj simluting the issue.

Thanks

Attachment: ODataFetch_1ac1bd03.zip

[Balamurugan Lakshmanan]

Hi Andrew,

Query: “What is the best way to handle this as its a huge performance gain due to the amount of rows SQL still has to process”

We understand that you are using SfDataManager component with ODataV4 adaptor type to fetch and bind data to Grid component. But we would like to inform that for remote data binding (OData, OdataV4, WebAPI) we will generate querystring supported by specific controller and send those querystring to perform certain operation in controller. These actions needs to be handled or will be hanlded by controller sole and returned to grid to display in it.

For ODataV4 controller when Paging is enabled we will send $count query string along with relative url to server so that querying operation. ODataV4 controller itself perform the operation based the query string and return the datasource along with its count. We will process that count value in pager source and render the component. So ODataV4 controller has to return the entire datasource count to perform the paging properly in Grid pager.

When returning the data using the below code.  

        [EnableQuery]

        public IActionResult Get(ODataQueryOptions<Product> options, CancellationToken token)

        {

            var request = Request.Query;

 

            var skip = (request.TryGetValue("$skip", out var value)) ? Convert.ToInt32(value[0]) : 0;

 

            var top = request.TryGetValue("$top", out var take) ? Convert.ToInt32(take[0]) : 0;

 

            return Ok(SimulateSQL(skip, top));

        }

 

        //Simulates using "offset" and "fetch next" from SQL query

        private IEnumerable<Product> SimulateSQL(int skip, int top)

        {

            return products?.Skip(skip).Take(top)!;

        }

You have returned only the current page record using $top and $skip values sent through querystring. So ODataV4 controller has considered only specific number of records as total count and return that value to render the Grid pager.

We dot not have support to update the total grid items when binding the data using the ODataV4 adaptor with SfDataManager. Either we request you to send entire data source to ODataV4 controller so that they can perform querying operation and return the correct value.

Or we request you to bind the data source to Grid component direct using the CustomAdaptor without any ODataV4 service. Refer our UG documentation for your reference.  

Reference: https://blazor.syncfusion.com/documentation/datagrid/custom-binding

Please get back to us if you have further queries.

Regards,

Balamurugan Lakshmanan