This is a migrated thread and some comments may be shown as answers.

Paging: Only load data of the current page

15 Answers 1164 Views
Grid
This is a migrated thread and some comments may be shown as answers.
gsharp
Top achievements
Rank 1
gsharp asked on 02 Jun 2015, 11:19 AM

Hi

 Is it possible to implement paging in a way that the Datasource contains only the Data to display?

 Lets assume i have 1 Million records, but for performance reasons i just want to load 10 at a time from sql server.

 How can I tell the Grid that he's on Page 2 of X and have a total record of 1 Million (in AJAX mode)?

 

DECLARE @PageNumber AS INT, @RowspPage AS INT
 SET @PageNumber = 2
 SET @RowspPage = 10 
SELECT ID_EXAMPLE, NM_EXAMPLE, DT_CREATE
 FROM TB_EXAMPLE
 ORDER BY ID_EXAMPLE
 OFFSET ((@PageNumber - 1) * @RowspPage) ROWS
 FETCH NEXT @RowspPage ROWS ONLY;

 

Thanks and best Regards

Giuseppe

 

15 Answers, 1 is accepted

Sort by
0
Boyan Dimitrov
Telerik team
answered on 04 Jun 2015, 07:18 AM

Hello gsharp,

By default the Kendo UI for ASP.NET MVC will enable server operations ( sorting, filtering, grouping and paging). This means that the data source will leave the data item paging implementation to the remote service. Setting .Pageable() for the Grid and  .PageSize(10) for the data source will achieve the desired functionality.

@(Html.Kendo().Grid<KendoGridAjaxBinding.Models.Product>()
       .Name("client")
           ....
            .Pageable()
            .DataSource(dataSource => dataSource
                .Ajax()
                .PageSize(10)
                .Model(model => model.Id(p => p.ProductID))
                .Read(read => read.Action("Products_Read", "Home"))
                 .Update("Editing_Update", "Home")
            )
)

It will load only 10 items at a time and show the total amount of records. 

 

Regards,
Boyan Dimitrov
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
gsharp
Top achievements
Rank 1
answered on 04 Jun 2015, 11:06 AM

Hi Boyan

 Tahnks for your answer. But that's actually not what I want. your solution still require that i load all 1 Million records from the Database to show Page 1 of X and total records of 1 Million.

What I want is to have only 10 records in the DataSource itself and "tell" the grid, hey look I give you 10 records, but tell the grid that actually there are a total of 1 Million records.

Hope my question is a bit clearer.

Regards

Giuseppe

 

 

0
Boyan Dimitrov
Telerik team
answered on 08 Jun 2015, 08:46 AM

Hello gsharp,

Please find attached a sample example of Kendo UI for ASP.NET MVC with server operations. If run in debug mode the following line of code returns only 10 items from the data base. 

DataSourceResult result = products.ToDataSourceResult(request);

Also I checked the Sql query:

SELECT TOP (10)
[Extent1].[ProductID] AS [ProductID],
[Extent1].[ProductName] AS [ProductName],
.....

Regards,
Boyan Dimitrov
Telerik
Do you want to have your say when we set our development plans? Do you want to know when a feature you care about is added or when a bug fixed? Explore the Telerik Feedback Portal and vote to affect the priority of the items
0
Mike
Top achievements
Rank 1
answered on 17 Aug 2017, 08:31 PM
Not working for me.  I followed the logic exactly except you were using entity framework.  Does this not work with a DAO/ADO?  I'm returning results from a stored procedure using the procedure of this post and I get  Exception of type 'System.OutOfMemoryException' was thrown.
0
Mike
Top achievements
Rank 1
answered on 17 Aug 2017, 08:42 PM
And it was only 31,884 rows of data
0
Boyan Dimitrov
Telerik team
answered on 21 Aug 2017, 01:44 PM

Hello Mike,

As far as I understand the exception is thrown when the stored procedure is executed without calling the ToDataSourceResult extension method in the Kendo,MVC.dll. If so I would suggest to take a look at the  ASP.NET System.OutOfMemoryException forum thread. 

 

Regards,
Boyan Dimitrov
Progress Telerik
Try our brand new, jQuery-free Angular 2 components built from ground-up which deliver the business app essential building blocks - a grid component, data visualization (charts) and form elements.
0
freeTheCode
Top achievements
Rank 1
answered on 02 Dec 2018, 04:46 PM

I'm in agreement with gsharp. The example you posted is a bit deceptive in not addressing the question of whether this is supported or can even be done.

Every example that has custom paging always fails to address the question. Every example has the controller receiving the DbContext for the ToDataSourceResult method to then do the paging which is not what is wanted.

So I'm going to ask this question because I need a concrete answer.

My Controller does not have access to the DbSet. My Controller calls a data service which returns ViewModel objects. My data service does the filtering and returns only the paged 10 items which are converted to ViewModel objects and passed to the controller. The controller then wraps the 10 ViewModel objects via ToDataSourceResult

Just like gSharp said, unless my dataservice returns all 1million records to the controller, paging will not work. it will only load the first page and stop.

It is clear that ToDataSourceResult method is looking for all 1 million records for it to then do the paging.

I don't want that. I want to return 10 records and the total count.

How can that be done?

 

 

 

 

 

0
Boyan Dimitrov
Telerik team
answered on 04 Dec 2018, 12:11 PM
Hello,

To achieve such goal the DataSourceRequest object should be sent from your Controller to the DAL service. The potential problem you might face is related to issue with serialization/deserialization of the DataSourceRequest object when sent from one project to another one. 

In order to avoid any issues with serialization/deserialization of the DataSourceRequest object ( if it is sent to the data access layer) I would recommend to parse manually the DataSourceRequest object to custom class that holds the state parameters as int and string types and use that object in the data access layer:

public class DataSourceState
{
    public int PageSize { get; set; }
    public int Page { get; set; }
    public string Filter { get; set; }
    public string Sort { get; set; }
    public string Group { get; set; }
    public string Aggregate { get; set; }
        
    public DataSourceRequest DataSourceRequest
    {
        get
        {
            var request = new DataSourceRequest()
            {
                Page = Page,
                PageSize = PageSize
            };
        
            if (!string.IsNullOrEmpty(Filter))
            {
                request.Filters = FilterDescriptorFactory.Create(Filter);
            }
            if (!string.IsNullOrEmpty(Sort))
            {
                request.Sorts = GridDescriptorSerializer.Deserialize<SortDescriptor>(Sort);
            }
        
            if (!string.IsNullOrEmpty(Group))
            {
                request.Groups = GridDescriptorSerializer.Deserialize<GroupDescriptor>(Group);
            }
        
            if (!string.IsNullOrEmpty(Aggregate))
            {
                request.Aggregates = GridDescriptorSerializer.Deserialize<AggregateDescriptor>(Aggregate);
            }
            return request;
        }
    }
}


Regards,
Boyan Dimitrov
Progress Telerik
Get quickly onboarded and successful with your Telerik and/or Kendo UI products with the Virtual Classroom free technical training, available to all active customers. Learn More.
0
freeTheCode
Top achievements
Rank 1
answered on 04 Dec 2018, 04:29 PM

Well after trying and failing repeatedly, I simply bit the bullet once it became clear that this would not work as I wanted without more issues and questions.

So I reverted back to the way they described but now noted one caveat that was a contributory/likely issue.

https://docs.telerik.com/aspnet-mvc/helpers/grid/binding/ajax-binding

"Important
The ToDataSourceResult() method will page, sort, filter, and group the collection that is passed to it. If this collection is already paged, the method returns an empty result.
"

 

What I had to do was rewrite the Middle Service to not enumerate the objects or do the paging or even create the viewmodels.

I had the MiddleService just pass the Linq ExpressionTree IQuerable<T> up after apply the selection criteria.

The change to the codebase was very minimal for me but might be really sucky for others.

I simply moved my viewmodel creation codeblock logic right into the controller method. 

Then I had to do the projection to the ViewModel Object in the controller as shown in the example

var product = _myMiddleService.GetProductsBasedOnScreenOptions(screenOptions) // Used all the options to setup the IQueryable of my Repository object without projecting.
DataSourceResult result = products.ToDataSourceResult(request, product => new ProductViewModel
                        {
                        ProductID = product.ProductID,
                        ProductName = product.ProductName,
                        UnitsInStock = product.UnitsInStock
                        });
                return Json(result);

 

This works as I want with the the query to the database that is generated only asking for the 20 records per page. 

Maybe somebody else will come up with something else, but we have a large amount of code to implement and could not dwell on this issue.

 

Thanks

 

0
Brandon
Top achievements
Rank 1
answered on 11 Jun 2020, 08:26 PM

I realize this is an old thread but I came across it today as I almost submitted a ticket for this very issue.  After reading this forum I felt like if I was going to submit a ticket I was going to have to get my ducks in a row.  Fortunately this actually led me to an answer on my own.  I'm going to give you step by step what I did so anyone looking at this can see everything it takes.

First....since this is data is being loaded by the listview from javascript I have my an action in my controller defined like this:

public ActionResult GetSearchResults([DataSourceRequest]DataSourceRequest request, string searchText)

 

In my case I'm calling a search function of a library I have no control over.  I tell it how many items per page and what page I'm on which you can get from the request object passed in to the action.

IEnumerable<string> searchIndexes = new List<string> { "MainSitePageCrawlerIndex" };
SearchParameters searchParameters = SearchParameters.PrepareForPages(searchText, searchIndexes, request.Page, request.PageSize, MembershipContext.AuthenticatedUser);
SearchResult searchResult = SearchHelper.Search(searchParameters);

 

To keep my example flowing I'll include the next line.  I'm simply copying the data from one list to another...but the list remains just the 10 items for the current page...just a different object type.

List<SearchResultEntry> searchResultEntries = searchResult.Items.Select(searchResultItem => new SearchResultEntry
            {
                Title = searchResultItem.Title,
                Url = ((TreeNode)searchResultItem.Data).GetFriendlyUrl(),
                Preview = SearchResultHelper.GetSearchResultPreview(searchResultItem)
            }).ToList();

 

Here is the key to the whole thing.  If you use ToDataSourceResult method there is logic in there that is incomplete. It assumes you are passing everything in...and it is this code that makes it seem like the listview is not working correctly (or whatever control you might be using).  I found, however, there isn't anything real special about this method so I just created the DataSourceResult manually.  Regardless of which way you go you also must set the Total field manually

DataSourceResult dataSourceResult = new DataSourceResult()
            {
                Data = searchResultEntries,
                Total = searchResult.TotalNumberOfResults
            };
 
return Json(dataSourceResult);

 

I hope this helps someone.  Thankfully I figured this out in one day....but it looks like others have struggled with this for sometime.

Thank you,
Brandon

0
Archieval
Top achievements
Rank 1
Veteran
answered on 04 Oct 2020, 12:21 PM
Hey @gsharp did you solve your problem? i have here the same scenario where i want to read rows needed per page, thanks
0
Archieval
Top achievements
Rank 1
Veteran
answered on 05 Oct 2020, 02:22 AM

Hi @freeTheCode's

i am having the same issue right now, i have huge number of records, so what i want is to load 10 records in db per click on each page but will display i have thousands of records, thanks

 

0
Brandon
Top achievements
Rank 1
answered on 05 Oct 2020, 03:03 AM
Did you see my post before your first one?  I had the same issue and fixed it. The key is building the return object yourself instead of calling ToDataSourceResult
0
Archieval
Top achievements
Rank 1
Veteran
answered on 05 Oct 2020, 05:15 AM
Hi @Brandon's, can you include also the view, did you use search textbox? anyways heres my current code for this thanks

public ActionResult GetRegularCutups([DataSourceRequest] DataSourceRequest request)
        {
            bool isAdmin = false;
            decimal location = Convert.ToDecimal(Location);
            int role = Convert.ToInt32(WebConfigurationManager.AppSettings["AdminId"]);

            if (RoleId == role)
            {
                isAdmin = true;
            }
            else
            {
                isAdmin = false;
            }

            IEnumerable<MeatCutupHdrData> regularCutups = new List<MeatCutupHdrData>();
            regularCutups = _mapper.Map<IEnumerable<MeatCutupHdrDto>, IEnumerable<MeatCutupHdrData>>
                (_meatCutupHdr.GetRegularCutups(isAdmin, location));

            return Json(regularCutups.ToDataSourceResult(request));
        }
0
Brandon
Top achievements
Rank 1
answered on 05 Oct 2020, 05:17 PM

I believe you may have multiple questions you are trying to answer by the post.  I believe I can help you on most if not all parts but I need a clearer understanding of where you are stuck (even if that is multiple places).  Here is some general info:

  • The action for the DataSourceRequest can be passed parameters to help filter your results (separate from paging)
  • The DataSourceRequest object has a property for what Page is being requested and what the PageSize is so that when you pull data you can properly pull back only the data necessary for the current request
  • The key to getting the grid to act right is getting it to know how many rows there are in total (or the max number you want someone to access).  This is where I ran into trouble....turns out the answer is to not use ToDataSourceResult as you have above. Will include sample below
  • To get the grid to request pages in the datasource request you set the PageSize property of the datasource object of the grid or listview in your MVC view

The alternative to using ToDataSourceResult as you did above would be this:

DataSourceResult dataSourceResult = new DataSourceResult()
{
          Data = regularCutups,
          Total = {totalNumberOfResults}
};
 
return Json(dataSourceResult);

 

As far as filling in {totalNumberOfResults} that depends on how you handle pulling the results from your database (or whereever it comes from).  If you pull all the results back you can get a count obviously.  You can then use Skip and Take to get the set of records for the given page.  Depending on where you are pulling the data from you might be able to request the data in pages...if so then you can use what it tells you for the number of records...or do a second call to just return a count.  That part, however, is outside the scope of the Telerik control itself....I'm mainly trying to help you figure out where to put the values once you get them.

 

Jason
Top achievements
Rank 1
commented on 19 May 2021, 08:14 AM

Hey Brandon - Thanks for your replies they have taken me a long way to getting this functionality working as the OP originally requested and I need. I have a slight issue that I'm hoping with be an easy fix. I have altered my SQL query to only return the correct page worth of data and set the total to a fixed number just now. However if you click to view page 2 the grid returns no records - Presumably because it's expecting 2 pages of results but ther is in fact only 1 page (in this case the second page). Hope this makes sense :)
Brandon
Top achievements
Rank 1
commented on 19 May 2021, 01:00 PM

Hi Jason. I think I understand what you say is happening but not exactly why. I have a working site that uses this functionality. In my situation the controller only returns the current page just like you describe. The point of the solution I provide is to correctly provide right number of total records so the control knows how many pages to show in the navigation. My suggestion is to look response to your request for page 2 and see if it looks like what I get back in my example. This is the data for page 5 of my search results:

{"Data":[{"Title":"History - 1993","Url":"/about-us/history?historicaleventid=94","Preview":"Northern Water expands its Irrigation Management Service to include a Turf and Urban Landscape Water Management and Conservation Program component. By"},{"Title":"History - October 1998","Url":"/about-us/history?historicaleventid=107","Preview":"Crews begin constructing a bypass flow structure at the Flatiron Power Plant. The bypass will enable water to bypass Flatiron in the event of a plant"},{"Title":"History - April 2000","Url":"/about-us/history?historicaleventid=113","Preview":"Windy Gap participants, the Subdistrict and Northern Water discuss increasing the Southern Water Supply Project pipeline’s capacity via installation"},{"Title":"History - March 2006","Url":"/about-us/history?historicaleventid=138","Preview":"Northern Water relocates a section of the St. Vrain Supply Canal upstream of the Little Thompson Siphon due to unstable geology. The canal is back in"},{"Title":"History - July 5, 1938","Url":"/about-us/history?historicaleventid=32","Preview":"Northern Water signs a contract with the United States to repay a portion of C-BT Project construction costs."},{"Title":"History - Jan. 1, 1962","Url":"/about-us/history?historicaleventid=65","Preview":"A 40-year repayment period commences for Northern Water\u0027s portion of C-BT Project facilities and construction costs."},{"Title":"History - 1968","Url":"/about-us/history?historicaleventid=67","Preview":"Northern Water purchases 11 acres adjoining the south and west sides of its current property to expand its headquarters. "},{"Title":"History - September 1999","Url":"/about-us/history?historicaleventid=109","Preview":"Crews finish work on the Southern Water Supply Project pipeline’s final 42 miles from Platteville to Fort Morgan."},{"Title":"History - July 14, 2000","Url":"/about-us/history?historicaleventid=116","Preview":"The Northern Water Board approves a contract with RB+B Architects, Inc. to design new headquarters facilities in Berthoud."},{"Title":"History - December 2000","Url":"/about-us/history?historicaleventid=118","Preview":"Northern Water and Reclamation sign C-BT Project Supplemental Contract No. 7 to modernize Horsetooth Reservoir’s four 50-year-old dams."}],"Total":100,"AggregateResults":null,"Errors":null}
Jason
Top achievements
Rank 1
commented on 19 May 2021, 05:00 PM

Hi Brandon - I was struggling with the Datatable I was using not being Enumerable - have now fixed that so the data is enumerated and the functionality is now working as I had hoped. Thanks for your help :).

If anyone else needs to convert a datatable to use in this way here's how it's done - just need to convince the client they don't need the actual number of records as that is the query that is possibly causing all the trouble in the first place :).

var data = dt.AsEnumerable().Select(
row => dt.Columns.Cast<DataColumn>().ToDictionary(
column => column.ColumnName,
column => row[column].ToString()
)).ToList();

result = new DataSourceResult()
{
Data = data,
Total = 3000
};
Brandon
Top achievements
Rank 1
commented on 19 May 2021, 05:07 PM

Hi Jason - Glad you figured it out. Not sure if it will help or you already thought about it but thought I'd through out another tip. I see you hardcoded 3000 for the total number of records. You could also do a query that just returns a count without limiting to a specific page so the grid always has the right number of pages....of course if you know you'll always have a ton of records setting the Total like you did can force it to always show you a set number of pages. Good luck!!!
Jason
Top achievements
Rank 1
commented on 19 May 2021, 06:16 PM | edited

Hi Brandon yeah the number of records is an issue. My implementation is a query that end users can build from a number of large tables. If they go daft it can get extremely messy very quickly. To do a count if need to mirror the the query that is built and then hope it got the same number of records. Need to speak to the pm to see how important the total nbr is. Cheers for your help :).
Jason
Top achievements
Rank 1
commented on 20 May 2021, 10:58 AM

wrapping the select sql with a count(*) from (...) will of course get me what I'm after :)
Tags
Grid
Asked by
gsharp
Top achievements
Rank 1
Answers by
Boyan Dimitrov
Telerik team
gsharp
Top achievements
Rank 1
Mike
Top achievements
Rank 1
freeTheCode
Top achievements
Rank 1
Brandon
Top achievements
Rank 1
Archieval
Top achievements
Rank 1
Veteran
Share this question
or