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

All unique values for excel filter

Hi. This article explains how to return unique values from server end for excel filter checkbox. But this approach does not work for Essential JS 2 for ASP.NET Core, or I use it incorrectly. Please give me an example to get all the unique values ​​of the Excel filter for the Grid (Essential JS 2 for ASP.NET Core).\

Thanks.

8 Replies

PS Pavithra Subramaniyam Syncfusion Team March 12, 2019 12:13 PM UTC

Hi Costa, 
 
Thanks for contacting Syncfusion support. 
 
Query: Hi. This article explains how to return unique values from server end for excel filter checkbox. But this approach does not work for Essential JS 2 for ASP.NET Core, or I use it incorrectly. Please give me an example to get all the unique values ​​of the Excel filter for the Grid (Essential JS 2 for ASP.NET Core). 
 
We have validated your query and created the sample based on your requirement. Here, we have created the sample in EJ2 Grid. We have returned the unique values from the server. Please find the below code example and sample for your reference. 
 
[code example] 
 
<ejs-grid id="Grid" allowPaging="true" allowFiltering="true" actionBegin="actionBegin" toolbar="@(new List<string>() { "Add", "Edit", "Delete", "Update", "Cancel" })"> 
    <e-data-manager url="/home/UrlDatasource" batchUrl="/Home/BatchUpdate" adaptor="UrlAdaptor"></e-data-manager> 
    <e-grid-filterSettings type="Excel"></e-grid-filterSettings> 
    ... 
</ejs-grid> 
 
 
<script> 
    function actionBegin(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(); 
            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); 
            } 
            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 
            } 
            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); 
        } 
 
 
Please get back to us if you need further assistance. 
 
Regards, 
Pavithra S. 
 



CO Costa March 13, 2019 01:47 PM UTC

Thanks, this code works. But in my application, data is stored in SQL Server. Suppose, conditionally, that this is the Order table. I use OrderViewModel to display the data in the Grid. In my case, the tool "SQL Server Extended events" shows that with each filtering (Excel mode) there are 3 queries to the database. The first query loads 100% of the records from the table. In the second query, the filter values ​​for the column are loaded. In the third query, the filtered data is loaded. The presence of the first query does not seem acceptable in terms of performance. Perhaps I have incorrectly formed the function "public IActionResult UrlDatasource ([FromBody] DataManagerRequest dm)" for the case of using DB and ViewModel. What is the recommended way of writing code for the function "public IActionResult UrlDatasource ([FromBody] DataManagerRequest dm)" in order to minimize the amount of data transferred from SQL Server for the case of using DataOperations class with tables in DB and ViewModels?


TS Thavasianand Sankaranarayanan Syncfusion Team March 14, 2019 07:28 AM UTC

Hi Costa, 
 
Query: In my case, the tool "SQL Server Extended events" shows that with each filtering (Excel mode) there are 3 queries to the database.  What is the recommended way of writing code for the function "public IActionResult UrlDatasource ([FromBody] DataManagerRequest dm)" in order to minimize the amount of data transferred from SQL Server for the case of using DataOperations class with tables in DB and ViewModels? 
 
We have validated your query and by default there are three queries are required for this case. First query is for rendering the grid with initial data. 
 
 
 
The second query is to get the filter values for excel filter from server.  
 
 
 
By default, In excel filter we have limited the filter choice record count as 1000 to prevent the performance issue. You can also set the filter choice record count as per your requirement. You can achieve this by using actionBegin event by using filterChoiceCount argument property. 

<script> 
    function actionBegin(args){ 
       if (args.requestType == "filterchoicerequest") { 
            // you can set filter choice count based on your needs , by default count 1000 
            args.filterchoicecount = 1500; 
        } 
    } 
</script> 
 
The third query is to take the corresponding filter records from server. To render the grid with filtered records. 
 
 
 
So there are three requests are required for this case. 
 
Please get back to us if you need further assistance. 

Regards,
 
Thavasianand S. 



CO Costa March 14, 2019 01:45 PM UTC

Hi. I am surprised to receive such an answer from Syncfudiom support service.Formal perfunctory replies are not needed by anyone. I again ask to answer my question on the merits. I would suggest that the employee who answers will read the discussion thread (it's not that big) from the very beginning and take into account that we are talking about ASP.NET Core - EJ 2, Grid, Excel filter, SQL Server, UrlAdaptor, DataOperations, ViewModels and and see the crux of the matter: recommended way of writing code for the function "public IActionResult UrlDatasource ([FromBody] DataManagerRequest dm)" in order to minimize the amount of data transferred from SQL Server for the case of using DataOperations class with tables in DB and ViewModels. Also, it would be nice to attach an example application.
Thanks.


TS Thavasianand Sankaranarayanan Syncfusion Team March 18, 2019 12:25 PM UTC

Hi Costa, 
 
Query: recommended way of writing code for the function "public IActionResult UrlDatasource ([FromBody] DataManagerRequest dm)" in order to minimize the amount of data transferred from SQL Server for the case of using DataOperations class with tables in DB and ViewModels. Also, it would be nice to attach an example application. 
 
We have validated your query and you can achieve your requirement by using below way. If you want minimize the amount of data transferred from server, then you can create and operate the new view model class. Please find the below code example and sample for your reference. 
 
[code example] 
... 
 
namespace EJ2Grid.Controllers 
{ 
    public class HomeController : Controller 
    { 
        public static List<Orders> order = new List<Orders>(); 
 
        ... 
       public IActionResult UrlDatasource([FromBody]DataManagerRequest dm) 
        { 
            IEnumerable DataSource = MappingFunction (order); 
            DataOperations operation = new DataOperations(); 
            if (dm.Search != null && dm.Search.Count > 0) 
            { 
                DataSource = operation.PerformSearching(DataSource, dm.Search);  //Search 
            } 
            ... 
           return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); 
        } 
        private List<OrderViewModel> MappingFunction (List<Orders> lstOrders) 
        { 
            List<OrderViewModel> lstOrderViewModel = new List<OrderViewModel>(); 
            lstOrderViewModel.AddRange(lstOrders.Select(x => new OrderViewModel { OrderID = x.OrderID, CustomerID = x.CustomerID })); 
            return lstOrderViewModel; 
        } 
 
        ... 
 
       public class Orders                         //class with all columns properties 
        { 
            ... 
            public int OrderID { get; set; } 
            public int EmployeeID { get; set; } 
            public string CustomerID { get; set; } 
            public string ShipCity { get; set; } 
            public string ShipName { get; set; } 
       } 
 
        public class OrderViewModel                 //class for only displaying columns property in the grid 
        { 
            public int OrderID { get; set; } 
            public string CustomerID { get; set; } 
        } 
    } 
} 
 
 
Here, the Orders class contains all columns property. We have created a class called OrderViewModel. It will contain the properties only needs to be displayed in grid. So, only the displayed properties are sent from server. 
 
 
Please get back to us if you need further assistance. 
 
Regards, 
Thavasianand S. 



CO Costa March 19, 2019 08:28 PM UTC

     Hi. Thanks for the answer. I think I understood (if I'm wrong, correct me) why each time filtering or paging 100% of records are unloaded from Sql Server. This is because in your examples you suggest using IEnumerable methods of the DataOperations class. Because of this, when casting data to the IEnumerable type, a query is made to the database to upload 100% of the records (these can be thousands, tens of thousands or more records), although only a few records are displayed in the grid (8 records in your example). I hope you finally understand that this is unacceptable.
     I redid the example you provided to use the Sql Server and the IQueryable methods of the DataOperations class. Now, with each query, only the number of records that needs to be displayed on the Datagrid (pageSize) is unloaded. That is what I asked you earlier.
     But, returning to the original question, I cannot configure the loading of all the unique values ​​of the Excel filter for the use of the IQueryable methods of the DataOperations class. Please give an example of how the “dm.Select” part of the code should look like in this case:

            if (dm.Select != null)
            {
               //the code from your example does not work here
            }

Please do it based on my project (your project, which I had to redo). See attachment. The database is initialized from the Program.cs file using the SeedData class when the application is started.

Attachment: EJ2GridSampleM1879927353DB_d7d1535d.zip


CO Costa March 20, 2019 12:00 PM UTC

Ok, I solved the problem as follows:
...
   int count = DataSource.Count();
   if (dm.Select != null)
            {
                var DataSourceSelect = operation.PerformSelect(DataSource, dm.Select);  // Selected the columns value based on the filter request
                DataSourceSelect = DataSourceSelect.Cast().Distinct(); // Get the distinct values from the selected column
                return dm.RequiresCounts ? Json(new { result = DataSourceSelect, count }) : Json(DataSourceSelect);
            }
...

It seems to work.

P.S. Although dm.RequiresCounts is true, it seems that the value of "count" in this case does not matter.


TS Thavasianand Sankaranarayanan Syncfusion Team March 20, 2019 12:06 PM UTC

Hi Costa, 
 
We are happy that the problem has been solved. 
 
Please get back to us if you need any further assistance.  
                          
Regards, 
Thavasianand S.

Loader.
Up arrow icon