[Solved] DateTime filtering using FilterDescription and ExpressionBuilder

[Jean]

Hi,

We currently use a custom bound Telerik grid. When passing the filters to our controller it throws an  SqlDateTime overflow exception when we apply a date filter. I had a look at the LINQ Expression that is generated for the datetime filter and here it is :

.Where(item => (IIF((item != null), item.CreatedAt, 01/01/0001 00:00:00) > 22/02/2012 00:00:00))

Is that supposed to happen?

The code is that simple :

IQueryable<Customer> customers = customerService.FindByCustomerQueryable(Customer.Id);
filters = new List<IFilterDescriptor>();
filters.Add(new FilterDescriptor{ Member = "CreatedAt", Value = new DateTime(2012,2,22), MemberType = typeof(DateTime) , Operator = FilterOperator.IsGreaterThan});
customers  = customers .Where(ExpressionBuilder.Expression<Customer>(filters)).ToList();

Telerik version: 2010.3.1110.235

Thanks
Jean

[Dadv]

Hi,

I don't know if it's your problem but often this error could be see in linq to sql when linq try to compare an unset datetime to an other date. Then it take the "default" date (01/01/0001 00:00:00) but Sql server is restrict to :1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

You should have an nullable value in you data source (CreateAt is nullable ?)

Try to add a Nullable datetime in the MemberType:
filters.Add(new FilterDescriptor{ Member = "CreatedAt", Value = new DateTime(2012,2,22), MemberType = typeof(DateTime?) , Operator = FilterOperator.IsGreaterThan})

but i think you will be in the same case of an other post : http://www.telerik.com/community/forums/aspnet-mvc/grid/bug---adding-filter-to-nullable-datetimediseable-popup-filter-box.aspx 

I have send a pits request for that.

[Jean]

CreatedAt is not nullable in our Datasource and should not be. I did try your suggestion but without success. 

Thanks for your help.

Jean

[Dadv]

Sorry not enough information for find a solution, can you copy/paste some part of the controller and the view?

[Jean]

I managed to get it working by creating my own Expression like this :

ParameterExpression value = Expression.Parameter(typeof(Customer), "x");
Expression left = Expression.PropertyOrField(value, filter.Member);
Expression e1 =   filter.Operator.GetExpression(left, Expression.Constant(filter.Value));
var exp = Expression.Lambda<Func<Customer, bool>>(e1, value);
customers  = customers .Where(exp);

GetExpression is just an extension method for DateTime :

public static Expression GetExpression(this FilterOperator filterOperator, Expression left, Expression right)
{
    switch (filterOperator)
    {
        case FilterOperator.IsLessThan:
            return Expression.LessThan(left,right);
        case FilterOperator.IsLessThanOrEqualTo:
            return Expression.LessThanOrEqual(left,right);
        case FilterOperator.IsEqualTo:
            return Expression.Equal(left,right);
        case FilterOperator.IsNotEqualTo:
            return Expression.NotEqual(left,right);
        case FilterOperator.IsGreaterThanOrEqualTo:
            return Expression.GreaterThanOrEqual(left,right);
        case FilterOperator.IsGreaterThan:
            return Expression.GreaterThan(left,right);
        case FilterOperator.StartsWith:
        case FilterOperator.EndsWith:
        case FilterOperator.Contains:
        case FilterOperator.IsContainedIn:
        default:
            throw new NotImplementedException();
    }
}