Converting filter conditions to a linq query in Web API data source

[Ehab Zaky]

Dear Great Support Team,

I am using DataGrid control with "WebApiAdaptor" data adapter. I have seen in your documentation the below code which extracts the filter string sent from the DataGrid control to the web api.

      skip = (queryString.TryGetValue("$skip", out var skipStringValues)) ? Convert.ToInt32(skipStringValues[0]) : 0;

      top = (queryString.TryGetValue("$top", out var takeStringValues)) ? Convert.ToInt32(takeStringValues[0]) : 0;

      filter = (queryString.TryGetValue("$filter", out var filterStringValues)) ? filterStringValues : "";

I can apply skip and top easily using related linq methods. However how to apply the filter condition extracted in the above filter variable to the where condition in linq query so that I return filtered data to the grid?

Thanks in advance.

Regards,

Ehab Zaky

[Santhosh Iruthayaraj]

Hi Ehab Zaky,

Greetings from Syncfusion Support!

In response to your request, we have prepared a sample to handle Grid filtering on the server side for WebApiAdaptor. Please refer to the code snippet and sample provided below:

[Controllers\OrdersController.cs]

 

      public object Get()

        {

            var queryString = Request.Query;

            var data = OrdersDetails.GetAllRecords().ToList();

            int skip = Convert.ToInt32(queryString["$skip"]);

            int take = Convert.ToInt32(queryString["$top"]);

            string sort = queryString["$orderby"];   // get the sort queries

            string filter = queryString["$filter"];  //filtering

            if (filter != null) // to handle filter operation

            {

                if (filter.Contains("substring"))//searching

                {

                    var key = filter.Split(new string[] { "'" }, StringSplitOptions.None)[1];

                    data = data.Where(fil => fil.CustomerID.ToLower().ToString().Contains(key.ToLower())

                                            || fil.EmployeeID.ToString().Contains(key)

                                            || fil.ShipCity.ToLower().Contains(key.ToLower())

                                            || fil.OrderID.ToString().Contains(key)).ToList();

                }

                else

                {

                    var newfiltersplits = filter;

                    var filtersplits = newfiltersplits.Split('(', ')', ' ');

                    var filterfield = filtersplits[1];

                    var filtervalue = filtersplits[3];

                    if (filtersplits.Length == 5)

                    {

                        if (filtersplits[1] == "tolower")

                        {

                            filterfield = filter.Split('(', ')', '\'')[2];

                            filtervalue = filter.Split('(', ')', '\'')[4];

                        }

                    }

                    if (filtersplits.Length != 5)

                    {

                        filterfield = filter.Split('(', ')', '\'')[3];

                        filtervalue = filter.Split('(', ')', '\'')[5];

                    }

                    if (filterfield == "orderID")

                    {

                        data = (from cust in data

                                where cust.OrderID.ToString() == filtervalue.ToString()

                                select cust).ToList();

                    }

                    if (filterfield == "employeeID")

                    {

                        data = (from cust in data

                                where cust.EmployeeID.ToString() == filtervalue.ToString()

                                select cust).ToList();

                    }

                    if (filterfield == "customerID")

                    {

                        data = (from cust in data

                                where cust.CustomerID.ToLower().StartsWith(filtervalue.ToString())

                                select cust).ToList();

                    }

                    if (filterfield == "shipCity")

                    {

                        data = (from cust in data

                                where cust.ShipCity.ToLower().StartsWith(filtervalue.ToString())

                                select cust).ToList();

                    }

                    if (filterfield == "verified")

                    {

                        data = (from cust in data

                                where cust.Verified == bool.Parse(filtervalue.ToString())

                                select cust).ToList();

                    }

                }

            }

            return take != 0 ? new { result = data.Skip(skip).Take(take).ToList(), count = data.Count() } : new { result = data, count = data.Count() };

        }

 

Make sure to configure the filtering for each individual column based on its type to properly filter the data.

You can find the sample attached below for your reference.

Regards,

Santhosh I

Attachment: CoreGridApp_10d703ac.zip