Filter query to database

[Denis Schneider]

Is there a way to filter the datasource when accessing the database? I have a fairly large table, it's not very efficient to always get the entire table.

Is it also possible to somehow get only the desired column in the Excel filter (e.g. when i press Category filter, load from back only Categories. not entirely MoneyHistory model ) ? Maybe there is a way to use DTO in filter?

[Rajapandiyan Settu]

Hi Denis,

Thanks for contacting Syncfusion support.

From version 20.1.52, we have provided the support to get the distinct value directly from the server side. You can achieve this by using the following code example in the actionBegin event (requestType as filterchoicerequest or filtersearchbegin).

actionBegin: https://ej2.syncfusion.com/javascript/documentation/api/grid/#actionbegin

 

[index.cshtml]

<script>

    function onActionBegin(args) {

        if (args.requestType === “filterchoicerequest” || args.requestType === “filtersearchbegin”) {

            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>

 

 

[HomeController.cs]

        public IActionResult UrlDataSource([FromBody] TestDm dm)

        {

            IQueryable<BigData> DataSource = BigData.GetAllRecords().AsQueryable();

            QueryableOperation operation = new QueryableOperation();

            IQueryable Data = null;

 

            if (dm.Search != null && dm.Search.Count > 0)

            {

                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search

            }

            if (dm.Sorted != null && dm.Sorted.Count > 0) //Sorting

            {

                DataSource = operation.PerformSorting(DataSource, dm.Sorted);

            }

            if (dm.Where != null && dm.Where.Count > 0) //Filtering

            {

                DataSource = operation.PerformFiltering(DataSource, dm.Where, dm.Where[0].Operator);

            }

            if (dm.Select != null)

            {

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

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

            }

            int count = Data != null ? Data.Cast<dynamic>().Count() : DataSource.Cast<BigData>().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 = Data != null ? Data : DataSource, count = count }) : Json(DataSource);

        }

Sample: https://www.syncfusion.com/downloads/support/directtrac/general/ze/core_grid_distinct_select_excel_filter-992359859.zip

By default, the Excel/ Checkbox filter Dialog in the Grid will display the distinct data from the first 1000 records in grid dataSource to optimize the performance. You can also increase the Excel filter choice count by modifying the filterChoiceCount argument value in the actionBegin event when the requestType is ‘filterchoicerequest’ as demonstrated in the below code snippet,

 

function actionBegin(args){

  if(args.requestType == 'filterchoicerequest' || args.requestType === "filtersearchbegin"){

 

 args.filterChoiceCount = 5000;   // change the filterchoicecount value as you need (the filter check list shown based on this value )

 }

}

 

Find the below documentation for your reference.

https://www.syncfusion.com/kb/13372/how-to-change-the-filter-choice-value-count-for-grid-excel-filter

Regards,

Rajapandiyan S