RadGrid filter with Date when LinqExpression is enabled

[Dimuthu]

I Have a radgrid running with EnableLinqExpression="false". I am using this to get the filter string to support by the database. but when i try to filter with Datecolumn (GridDateTimeColumn) I were not able to execute the sql generated by filter string. my database is an Oracle 11g. is there any way to generate the filter string to support by oracle database? 

[Viktor Tachev]

Hi Dimuthu,

By default the filtering in RadGrid is performed internally. If you need to perform more complex filtering operations you can modify the FilterExpression of RadGrid manually like illustrated in the following article.

• Operating with the FilterExpression of RadGrid Manually

Regards,
Viktor Tachev
Telerik

 

 

[Dimuthu]

Hi Viktor,

thanks for the reply. Yes it is true that we can change the filter by modifying the filter expression. and I have done it in column level by using the CurrentFilterValue of the column. 

however performing filtration internally would not be a good idea when we have millions of records. because to perform it internally we have to get all the data from the database to application. I thinks it is a lot of waste of resources. that was the reason for me to make it in database level.

here is how I achieved the expected result. (here I only wanted to modify the date filters because for all other types in my grid telerik generate the sql filter expression without an issue) please advise me if I am doing something wrong.

var dateList = new List<DateTime>();

        var gridColumncollection = radGrid.MasterTableView.Columns;
        foreach (GridColumn column in gridColumncollection)
        {
            if (column.ColumnType == "GridDateTimeColumn" && string.IsNullOrEmpty(column.CurrentFilterValue) == false)
            {
                DateTime date;
                DateTime.TryParse(column.CurrentFilterValue, out date);
                if (date > DateTime.MinValue)
                {
                    var s = "TO_DATE('" + date.Month + "/" + date.Day + "/" + date.Year + "', 'mm/dd/rrrr')";
                    filterExpression = filterExpression.Replace("'" + column.CurrentFilterValue + "'", s);
                }
                else
                {
                    var sDate = column.CurrentFilterValue.Split(' ');
                    DateTime.TryParse(sDate[0], out date);
                    var s = "TO_DATE('" + date.Month + "/" + date.Day + "/" + date.Year + "', 'mm/dd/rrrr')";
                    filterExpression = filterExpression.Replace("'" + sDate[0] + "'", s);

                    DateTime.TryParse(sDate[1], out date);
                    s = "TO_DATE('" + date.Month + "/" + date.Day + "/" + date.Year + "', 'mm/dd/rrrr')";
                    filterExpression = filterExpression.Replace("'" + sDate[1] + "'", s);
                }
            }
        }

        filterExpression = filterExpression.Replace('[', ' ').Replace(']', ' ');

[Viktor Tachev]

Hi Dimuthu,

You can use the described approach to build a filter expression and pass it to the datasource to retrieve the results.

Thank you for sharing your solution with the community.

Regards,
Viktor Tachev
Telerik