We use cookies to give you the best experience on our website. If you continue to browse, then you agree to our privacy policy and cookie policy. Image for the cookie policy date
close icon

Filter query to database

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?


1 Reply

RS Rajapandiyan Settu Syncfusion Team February 2, 2023 07:21 AM UTC

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


Loader.
Live Chat Icon For mobile
Up arrow icon