Grid Filter value format

Question

Dear all,A did not find any solutions how to

effectively

parse the filter value query at server side (C# Core, MSSQL).I receive the following:(startswith(tolower(UserName),'anybody')) and (CreateDate lt datetime'2021-10-12T22:00:01.000Z'))How to convert this string to sql query?Is it possible to setup the grid component to pass filter values in json format For example: [{field: "UserName", operator: "eq", value: "anybody" ... }{field: "CreateDate", operator: "eq", value: "2021-10-12T22:00:01.000Z" ... }]ThanksZ

Rajapandiyan Settu · Answer

Hi Tihanyi, 

Greetings from Syncfusion support. 

By analyzing your query we suspect that you want to bind the SQL data to the EJ2 Grid. If so, we suggest you to use URL Adaptor to bind the SQL Data from the server to the Grid. Find the below documentation for your reference. 

URL Adaptor: https://ej2.syncfusion.com/angular/documentation/grid/edit/#url-adaptor 
KB: https://www.syncfusion.com/kb/11250/how-to-perform-data-operation-in-server-side-for-iqueryable-data 

Based on our EJ2 Grid architecture, the filter predicates are generated in complex way. If you want to get the filterQuery details (field, operator, value) separately in the server, then use the following way.  

You can send the filter queries with these properties separately – “Operator Name, Field Name, Field Value” by using the following approach - Retrieve the current filter query, use ‘for’ loop to create an object with the mentioned properties for the available predicates and push this into an object array, add this object array to the Grid’s request query using addParams method and then access it in the controller method where you can perform your required operations. This can be done in the Grid’s actionBegin event when the argument requestType is ‘filtering’ as demonstrated in the below code snippet,  







  actionBegin(args) { 
            if (args.requestType == "filtering") { 
                var grid = document.getElementById("Grid").ej2_instances[0]; 
                var whereQuery = []; 
                var currentqueries = grid.getDataModule().generateQuery().queries 
                for (var i = 0; i < currentqueries.length; i++) { 
                    if (currentqueries[i].fn == "onWhere") { 
                        var filterquery = currentqueries[0].e; 
                        if (currentqueries[0].e.isComplex) { 
                            var predicates = currentqueries[0].e.predicates; 
                            for (var j = 0; j < predicates.length; j++) { 
                                if (predicates[j].isComplex) { 
                                    for (var k = 0; k < predicates[j].predicates.length; k++) { 
                                        if (predicates[j].predicates[k].isComplex) { 
                                            var x = predicates[j].predicates[k].predicates; 
                                            for (var l = 0; l < x.length; l++) { 
                                                whereQuery.push({ field: x[l].field, operator: x[l].operator, value: x[l].value }) 
                                            } 
                                        } else { 
                                            whereQuery.push({ field: predicates[j].predicates[k].field, operator: predicates[j].predicates[k].operator, value: predicates[j].predicates[k].value }) 
                                        } 
                                    } 
                                } else { 
                                    whereQuery.push({ field: predicates[j].field, operator: predicates[j].operator, value: predicates[j].value }) 
                                } 
                            } 
                        } else { 
                            whereQuery.push({ field: filterquery.field, operator: filterquery.operator, value: filterquery.value }) 
                        } 
                    } 
                } 
                grid.query = new ej.data.Query().addParams('FilterQueries', whereQuery); 
            } 
        } 




[HomeControllers.cs] 

         public ActionResult UrlDatasource(TestDm dm) 
        { 
            --- 
            // get the filter query details and generate the SQL query as you want 
            if (dm.Params != null && dm.Params.FilterQueries != null) 
            { 
            var filterdetails = dm.Params.FilterQueries; 
            } 
            ---- 
            return dm.RequiresCounts ? Json(new { result = DataSource, count = count }) : Json(DataSource); 
        } 
 
        public class TestDm : DataManagerRequest 
        { 
            public string[] filterValue { get; set; } 
            public ParamObject Params { get; set; } 
        } 
        public class ParamObject 
        { 
            public List<FilterQuery> FilterQueries { get; set; } 
        } 
 
        public class FilterQuery 
        { 
            public string Field { get; set; } 
            public string Operator { get; set; } 
            public string Value { get; set; } 
        } 
 

By using this FilterQuery (additional parameter), You can frame the SQL query in your server side and perform your action as you want. 

If this not meets your requirement, kindly share the below details to proceed further. 


Share the full Grid code you have used.
Which type of data-base do you have used? (SQL/ OData/ ODataV4 / WebApi)

Regards, 
Rajapandiyan S