Nullable values on LINQ search of jqGrid

Question

first of all, sorry for my bad english, isnt my native language.

Im using jqgrid on a ASP.NET MVC project on my work, and have some problems when implement search. Im try to use one solution that find on internet using LinqExtensions. The problem ocurres when the entity have nullable value like:

public class MyClass()
{
   string StringValue { get; set; }
   int? IntegerValue { get; set; }
}

This is because in the database the values accepts null, and need the nullable values on my c# code for other reasons on the project.

In another class named LinqExtensions the where clause is like this:

public static IQueryable<T> Where<T>(this IQueryable<T> source, string searchProperty, string searchString, string searchOper)
{
        Type type = typeof(T);

        if (string.IsNullOrEmpty(searchString))
            return source;

        ConstantExpression searchFilter = Expression.Constant(searchString.ToUpper());

        ParameterExpression parameter = Expression.Parameter(type, "p");
        //PropertyInfo property = type.GetProperty(searchProperty);
        //Expression propertyAccess = Expression.MakeMemberAccess(parameter, property);

        MemberExpression memberAccess = null;
        String[] separador = {"__"};
        foreach (var property2 in searchProperty.Split(separador, StringSplitOptions.None))
            memberAccess = MemberExpression.Property
               (memberAccess ?? (parameter as Expression), property2);
        Expression propertyAccess = memberAccess;

        if (propertyAccess.Type == typeof(Nullable<DateTime>))
        {
            PropertyInfo valProp = typeof(Nullable<DateTime>).GetProperty("Value");
            propertyAccess = Expression.MakeMemberAccess(propertyAccess, valProp);
            Nullable<DateTime> tn = DateTime.Parse(searchString);
            searchFilter = Expression.Constant(tn);
        }


        //support int?
        if (propertyAccess.Type == typeof(Nullable<Char>))
        {
            PropertyInfo valProp = typeof(Nullable<Char>).GetProperty("Value");
            propertyAccess = Expression.MakeMemberAccess(propertyAccess, valProp);
            Nullable<Char> tn = Char.Parse(searchString);
            searchFilter = Expression.Constant(tn);
        }

        if (propertyAccess.Type == typeof(Nullable<Int16>))
        {
            PropertyInfo valProp = typeof(Nullable<Int16>).GetProperty("Value");
            propertyAccess = Expression.MakeMemberAccess(propertyAccess, valProp);
            Nullable<Int16> tn = Int16.Parse(searchString);
            searchFilter = Expression.Constant(tn);
        }

        if (propertyAccess.Type == typeof(Nullable<Int32>))
        {
            PropertyInfo valProp = typeof(Nullable<Int32>).GetProperty("Value");
            propertyAccess = Expression.MakeMemberAccess(propertyAccess, valProp);
            Nullable<Int32> tn = Int32.Parse(searchString);
            searchFilter = Expression.Constant(tn);
        }

        if (propertyAccess.Type == typeof(Nullable<Int64>))
        {
            PropertyInfo valProp = typeof(Nullable<Int64>).GetProperty("Value");
            propertyAccess = Expression.MakeMemberAccess(propertyAccess, valProp);
            Nullable<Int64> tn = Int64.Parse(searchString);
            searchFilter = Expression.Constant(tn);
        }

        //support decimal?
        if (propertyAccess.Type == typeof(Nullable<decimal>))
        {
            PropertyInfo valProp = typeof(Nullable<decimal>).GetProperty("Value");
            propertyAccess = Expression.MakeMemberAccess(propertyAccess, valProp);

            Nullable<decimal> tn = Decimal.Parse(searchString);
            searchFilter = Expression.Constant(tn);
        }

        if (propertyAccess.Type == typeof(Char))
            searchFilter = Expression.Constant(Char.Parse(searchString));

        if (propertyAccess.Type == typeof(Int16))
            searchFilter = Expression.Constant(Int16.Parse(searchString));

        if (propertyAccess.Type == typeof(Int32))
            searchFilter = Expression.Constant(Int32.Parse(searchString));

        if (propertyAccess.Type == typeof(Int64))
            searchFilter = Expression.Constant(Int64.Parse(searchString));

        if (propertyAccess.Type == typeof(decimal))
            searchFilter = Expression.Constant(Decimal.Parse(searchString));

        if (propertyAccess.Type == typeof(DateTime))
            searchFilter = Expression.Constant(DateTime.Parse(searchString));


        MethodInfo startsWith = typeof(string).GetMethod("StartsWith", new Type[] { typeof(string) });
        MethodInfo endsWith = typeof(string).GetMethod("EndsWith", new Type[] { typeof(string) });
        MethodInfo contains = typeof(string).GetMethod("Contains", new Type[] { typeof(string) });

        //MethodInfo contains = typeof(Int32Extensions).GetMethod("Contains", new Type[] { typeof(Int64), typeof(Int64) });

        Expression operation = null;

        switch (searchOper)
        {
            default:
            case "eq":
                operation = Expression.Equal(propertyAccess, searchFilter);
                break;
            case "ne":
                operation = Expression.NotEqual(propertyAccess, searchFilter);
                break;
            case "lt":
                operation = Expression.LessThan(propertyAccess, searchFilter);
                break;
            case "le":
                operation = Expression.LessThanOrEqual(propertyAccess, searchFilter);
                break;
            case "gt":
                operation = Expression.GreaterThan(propertyAccess, searchFilter);
                break;
            case "ge":
                operation = Expression.GreaterThanOrEqual(propertyAccess, searchFilter);
                break;
            case "bw":
                operation = Expression.Call(propertyAccess, startsWith, searchFilter);
                break;
            case "bn":
                operation = Expression.Call(propertyAccess, startsWith, searchFilter);
                operation = Expression.Not(operation);
                break;
            case "ew":
                operation = Expression.Call(propertyAccess, endsWith, searchFilter);
                break;
            case "en":
                operation = Expression.Call(propertyAccess, endsWith, searchFilter);
                operation = Expression.Not(operation);
                break;
            case "cn":
                operation = Expression.Call(propertyAccess, contains, searchFilter);
                break;
            case "nc":
                operation = Expression.Call(propertyAccess, contains, searchFilter);
                operation = Expression.Not(operation);
                break;
        }

        var whereExpression = Expression.Lambda(operation, parameter);

        var resultExpression = Expression.Call(typeof(Queryable), "Where", new Type[] { source.ElementType }, source.Expression, whereExpression);

        return source.Provider.CreateQuery<T>(resultExpression);
    }

The method in Model class is:

public JsonResult GetData(GridSettings grid)
    {
        if (Session["SomeValue"] != null)
        {
            var query = (new GridModel()).GetQuery();

            //Filters
            if (grid.IsSearch && grid.Where != null)
            {
                //And
                if (grid.Where.groupOp == "AND")
                    foreach (var rule in grid.Where.rules)
                        query = query.Where<MyClass>(rule.field, rule.data.ToUpper(), rule.op);
                else
                {
                    //Or
                    var temp = (new List<MyClass>()).AsQueryable();
                    foreach (var rule in grid.Where.rules)
                    {
                        var t = query.Where<MyClass>(rule.field, rule.data, rule.op);
                        temp = temp.Concat<MyClass>(t);
                    }
                    //Clean repeat elements
                    query = temp.Distinct<MyClass>();
                }
            }
            //Order
            query = query.OrderBy<MyClass>(grid.SortColumn,
                grid.SortOrder);
            //Count
            var count = query.Count();
            //Pager
            var data = query.Skip((grid.PageIndex - 1) * grid.PageSize).Take(grid.PageSize).ToArray();

            //Convert
            var result = new
            {
                .
                .
                .
            }
  }

I create the grid, display values very correctly but... when search by string any problem ocurres even if some values are null, and if trying to search by the IntegerValue (that supports null) and Exception throws when some values on entity are null. This problem being transform me on a crazy man.

Please, if any have the same problem or know how to solve it, i will be eternally grateful

Bye

rdarioduarte · Accepted Answer · 2013-01-23T12:27:15.800

Its more easy than i think, the best way is to override the operator called for the expression:

For example in my problem, override the equals method to:

    private static Expression LinqEqual(Expression e1, Expression e2)
    {
        if (IsNullableType(e1.Type) && !IsNullableType(e2.Type))
            e2 = Expression.Convert(e2, e1.Type);
        else if (!IsNullableType(e1.Type) && IsNullableType(e2.Type))
            e1 = Expression.Convert(e1, e2.Type);
        return Expression.Equal(e1, e2);
    }

    private static bool IsNullableType(Type t)
    {
        return t.IsGenericType && t.GetGenericTypeDefinition() == typeof(Nullable<>);
    }

and replace the piece of code:

Expression operation = null;

    switch (searchOper)
    {
        default:
        case "eq":
            operation = Expression.Equal(propertyAccess, searchFilter);
            break;
        .
        .
        .
    }

I hope the problem and solution help some one.

Thanks Stackoverflow

Nullable values on LINQ search of jqGrid

1 Answers1