Filtering on large Data

[Jesús Mostajo]

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: 1000

Is there another way to load whole filter values?

[Vignesh Sivagnanam]

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 

<ejs-grid id="Grid" height="273" allowPaging="true" allowFiltering="true"> 

    <e-data-manager url="/Home/UrlDataSource" adaptor="UrlAdaptor"></e-data-manager> 

    <e-grid-filterSettings type="Excel"></e-grid-filterSettings> 

    <e-grid-columns> 

      <e-grid-column field="CustomerID" headerText="Customer ID" isPrimaryKey="true" textAlign="Right" width="100"></e-grid-column> 

      <e-grid-column field="ContactName" headerText="Contact Name" type="string" width="120"></e-grid-column> 

      <e-grid-column field="ContactTitle" headerText="ContactTitle" textAlign="Right" width="120"></e-grid-column> 

      <e-grid-column field="City" headerText="Ship City" width="150"></e-grid-column> 

    </e-grid-columns> 

  </ejs-grid> 

[Homecontroller.cs]  

public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) 

        { 

            EFDataContext db = new EFDataContext(); 

            IQueryable<Customers> 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]

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 you

Attachment: Archivo_comprimido_4c71428b.zip

[Meghana]

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

[KHALID OMAR MOHHMED]

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: 1000

Is 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]

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. 

 

 

<script> 

    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]

Thanks, I have found a solution using your advice.

[Vignesh Sivagnanam]

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