Pagination on DevExtreme dxDataGrid with Skip / Take loadOptions

Question

We have a table with a large amount of data and I do not want to load it at once for my dxDataGrid.

I want to implement paging with Skip / Take which is supplied from the dxDataGrid's DataSourceLoadOptions.

This is my controller:

[HttpGet]
    public async Task<Object> GetSalesOrdersWithTotals(DataSourceLoadOptions loadOptions)
    {
        try
        {
            var results =  await SalesOrderService.GetSalesOrdersWithTotals(loadOptions.Skip, loadOptions.Take, 40);
            loadOptions.Skip = 0;
            loadOptions.Take = 0;
            return DataSourceLoader.Load(results, loadOptions);
        }
        catch (Exception ex)
        {
            return Json(new { code = "422", success = false, message = "Unable to fetch sales orders with totals - " + ex.ToString() });
        }
    }

This is the service that returns the data:

public async Task<IEnumerable<SalesOrderWithTotals>> GetSalesOrdersWithTotals(int skip, int take, int defaultPageSize)
    {
        if (take == 0)
        {
            //Fix for passing a 0 take
            take = defaultPageSize;
        }
        var salesOrderWithTotals =
        from o in _context.SalesOrder
        select new SalesOrderWithTotals
        {
            SalesOrderId = o.SalesOrderId,
            Net = _context.SalesOrderItem.Where(it => it.SalesOrderId == o.SalesOrderId).Select(it => it.Qty == null ? 0 : it.Qty.Value * it.UnitPrice == null ? 0 : it.UnitPrice.Value).Sum(),
            Tax = _context.SalesOrderItem.Where(it => it.SalesOrderId == o.SalesOrderId).Select(it => it.Qty == null ? 0 : it.Qty.Value * it.UnitPrice == null ? 0 : it.UnitPrice.Value).Sum() * (o.Tax.Percentage /100),
            Gross = _context.SalesOrderItem.Where(it => it.SalesOrderId == o.SalesOrderId).Select(it => it.Qty == null ? 0 : it.Qty.Value * it.UnitPrice == null ? 0 : it.UnitPrice.Value).Sum() + _context.SalesOrderItem.Where(it => it.SalesOrderId == o.SalesOrderId).Select(it => it.Qty == null ? 0 : it.Qty.Value * it.UnitPrice == null ? 0 : it.UnitPrice.Value).Sum() * (o.Tax.Percentage / 100),
            Name = o.Customer.Name,
            CustomerOrderNumber = o.CustomerOrderNumber,
            Contact = o.Contact,
            OrderDate = o.OrderDate
        };
        return await salesOrderWithTotals.Skip(skip).Take(take).ToListAsync();
    }

Looking at SQL profiler, this takes the first 40 records but of course the dxDataGrid is not aware of the total count of records so pagination is not available.

What would be the best method to achieve what I want in this case?

Many thanks

Answer 1

You must do an extra query to get the count of your SalesOrder and keep it in for example salesOrderCount. Then keep the Load method return data as bellow.

LoadResult result = DataSourceLoader.Load(results, loadOptions);

LoadResult has a parameter called totalCount so set it with the real count of your data:

result.totalCount = salesOrderCount;

and then

return result;

Now the dxDataGrid is aware of the total count of records.