Filtering on large Data

Question

Hi friends,I looked up on this forum but I didn't find an answer.I have a Grid using Handling On-Demand grid actions for load large amount of data. For example, with your sample each call of grid paging, call to UrlDataSource method and load the entire amount of data, it's slow: IEnumerable DataSource = OrdersDetails.GetAllRecords();I have replaced it with:IReadOnlyList<Customer> DataSource = _customerService.ListAllPaginatedAsync(dm.skip, dm.take);This loads large amount of data verywell.My issue is about filter. It only load the first 1000 values when i'm trying to use it. This is the http head request: {take: 1000}take: 1000Is there another way to load whole filter values?

Vignesh Sivagnanam · Accepted Answer

Hi Jesús Thanks for contacting Syncfusion support. Based on your query you need to load the large amount of data in the Grid. So, we would like to suggest the “IQueryable” on handling the large data on the grid and in the below code example we have added skip and take for server side pagination. Please refer the below code example for your reference Code Example : Index.cshtml

[Homecontroller.cs] public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) { EFDataContext db = new EFDataContext(); IQueryable DataSource = db.customers; QueryableOperation operation = new QueryableOperation(); if (dm.Where != null && dm.Where.Count > 0) //Filtering { DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator); } int count = DataSource.Count(); if (dm.Skip != 0) { DataSource = operation.PerformSkip(DataSource, dm.Skip); //Paging } if (dm.Take != 0) { DataSource = operation.PerformTake(DataSource, dm.Take); } return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); } Sample : https://www.syncfusion.com/downloads/support/forum/159105/ze/EJ2Grid1508950216263006649.zip Please get back to us if you need further assistance with this. Regards, Vignesh Sivagnanam

Jesús Mostajo · Answer

Hi Vignesh,To load data works fine, but on Excel Type filtering, the HTTP GET request is: requiresCounts: true take: 1000. The same behavior occurs in your example as in our project .: requiresCounts: true take: 1000. You can see in attach file your sample: return count: 101000 and JSON result with 1000 records.We need that each column filter filled with whole records, not only a thousand.Could you help me?Thank youAttachment: Archivo_comprimido_4c71428b.zip

Meghana · Answer

Know more about this issue at https://www.idn-kxchange.com/

KHALID OMAR MOHHMED · Answer

Hi friends,I looked up on this forum but I didn't find an answer.I have a Grid using Handling On-Demand grid actions for load large amount of data. For example, with your sample each call of grid paging, call to UrlDataSource method and load the entire amount of data, it's slow: IEnumerable DataSource = OrdersDetails.GetAllRecords();I have replaced it with:IReadOnlyList<Customer> DataSource = _customerService.ListAllPaginatedAsync(dm.skip, dm.take);This loads large amount of data verywell.My issue is about filter. It only load the first 1000 values when i'm trying to use it. This is the http head request: {take: 1000}take: 1000Is there another way to load whole filter values?i have the same problem with excel filter the filter reqiure all data to load the filters values

Thiyagu Subramani · Answer

Hi All,

Thanks for your updates.

By default, in checkbox/Excel type filter the filter choice record count is limited to 1000 (take first 1000 records and then apply distinct value) for better performance. So only the first thousand records is fetched in the filter choices. The remaining records will be returned as result in the search option of the Filter dialog.You can set the number of filter items by using the property filterChoiceCount to overcome this default behavior. When you open the checkbox filter, the actionBegin event will be triggered with requestType as a filterchoicerequest and you can change the filterChoiceCount according to your needs using the event argument, and you can also change the same for requestType filtersearchbegin.

Based on your requirement we have prepared a sample as per your requirement. Please refer to the below code snippet and sample for more reference.

// Grid’s actionBegin event function

function onActionBegin(args) {

if (args.requestType === "filterchoicerequest") {

// Filter choice count is modified

args.filterChoiceCount = 1500;

}

Or if you are using local data, we can also achieve this requirement by using custom dataSource (dataSource with distinct values) for the appropriate filter checkbox column as in args.requestType as filterbeforeOpen in actionBegin Event.

function onActionBegin(args) {

if (args.requestType === "filterbeforeopen") {

customDataSource = DataUtil.distinct(categoryData, ‘Discontinued’, true); // categoryData – grid data, Discontinued – required field

}

If you using remote data, then we suggest you to use the below way to improve the performance. In this code, we have bind actionBegin event and created the selected query to get the filtered column(existingPredicate) and current filtering column(args.filterModel.options.list) based on that field name, then we have generated an array and apply to select query to get the current and previous filtered column records instead of all data.

function begin(args) {

if (args.requestType === "filterchoicerequest") {

var filterfields = [];

var objFilter = Object.keys(args.filterModel.existingPredicate);

for (var i = 0; i < objFilter.length; i++) {

filterfields.push(objFilter[i]);

}

filterfields.push(args.filterModel.options.field);

args.query.distincts = [];

args.query.select(filterfields); // Created the select query

}

</script>

public IActionResult UrlDatasource([FromBody]DataManagerRequest dm)

{

IEnumerable DataSource = order;

DataOperations operation = new DataOperations();

. . . . . . . .

int count = DataSource.Cast<Orders>().Count();

if (dm.Select != null)

{

DataSource = operation.PerformSelect(DataSource, dm.Select); // Selected the columns value based on the filter request

DataSource = DataSource.Cast<dynamic>().Distinct().AsEnumerable(); // Get the distinct values from the selected column

}

. . . . . .

return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource);

}

Let us know if you have any concerns.

Regards,

Thiyagu S

Jesús Mostajo · Answer

Thanks, I have found a solution using your advice.

Vignesh Sivagnanam · Answer

Hi Jesús, 

We are happy to hear that the provided solution works fine at your end. 

Please get back to us if you need any further assistance. 

Regards, 
Vignesh Sivagnanam