Like search for datetime fields using dynamic lambda expression

Question

I am forming the string expression like below.

     string Condition = " it.person_id = " + personId.ToString();

     if (lstPersonFields != null)
      {
         foreach (var field in lstPersonFields )
           {                           
              string  fieldCondition = " And it." + field.FieldName.ToString();
                if (field.FieldCondition == "Contains")
                  {
                    fieldCondition = fieldCondition + " Like '%" + field.FieldValue.ToString() + "%'";
                  }
                 else if (field.FieldCondition == "Equals")
                  {
                   fieldCondition = fieldCondition + " = '" + field.FieldValue.ToString()+"'";
                  }
                 Condition = Condition + fieldCondition;
           }
      }
      var personSearch = FullPersonlst.Where(Condition).ToList();

Above code working correctly for other than datetime values in like search and throwing the error for datetime fields like

Like arguments must be of string type

How to do like search for datetime fields?

I don't think it is possible in this case, beco'z we don't the field names. — User_MVC, Feb 08 '13 at 11:27
DO NOT CONCATENATE INPUT. Repeat: DO NOT CONCATENATE INPUT. This is very very very dangerous code... — Marc Gravell, Feb 08 '13 at 11:30
@User_MVC because it is hugely susceptible to SQL injection. If you aren't familiar with SQL injection, go look it up *right now*. It is the most prevalent way of illegally obtaining data, and destroying data. If you routinely write code like the above, your system is fundamentally broken and needs immediate attention. — Marc Gravell, Feb 08 '13 at 12:19

score 1 · Accepted Answer · answered Feb 08 '13 at 11:39

1

To do that as a dynamic lambda expression would be something like:

var arg = Expression.Parameter(typeof(Person), "it");
var body = Expression.Equal(
    Expression.PropertyOrField(arg, "PersonId"),
    Expression.Constant(personId));

if (lstPersonFields != null)
{
    foreach (var field in lstPersonFields)
    {
        var member = Expression.PropertyOrField(arg, field.FieldName);
        switch (field.FieldCondition)
        {
            case "Contains":
                body = Expression.AndAlso(body,
                    Expression.Call(typeof(SqlMethods), "Like", null,
                        member,
                        Expression.Constant("%" + field.FieldValue + "%")));
                break;
            case "Equals":
                body = Expression.AndAlso(body,
                    Expression.Equal(
                       member,
                       Expression.Constant(field.FieldValue)));
                break;
        }
    }
}
var lambda = Expression.Lambda<Func<Person,bool>>(body, arg);
var personSearch = FullPersonlst.Where(lambda).ToList();

answered Feb 08 '13 at 11:39

Marc Gravell

1,026,079
266
2,566
2,900

As i converted the above code into LINQ to Entities ,it is throwing this error like : LINQ to Entities does not recognize the method 'Boolean Like(System.String, System.String)' method, and this method cannot be translated into a store expression. – User_MVC Feb 08 '13 at 12:15
1

That version (`SqlMethods`) is LINQ-to-SQL - so yes it might not work in EF; you can also just use `Contains` - i.e. `Expression.Call(member, "Contains", null, Expression.Constant(field.FieldValue))`, or for more options, see here: http://stackoverflow.com/questions/1033007/like-operator-in-entity-framework – Marc Gravell Feb 08 '13 at 12:17
@ Marc Gravell: Above code not working for the datetime values.it seems require some changes in the code.Can u tell how to modify this code to work with datetime values. – User_MVC Feb 10 '13 at 14:04
@User either add some Expression.Convert (to string) or Expression.Call on "ToString". Or take DateTime inputs and just use Equal etc – Marc Gravell Feb 10 '13 at 17:47
@ Marc I did like this System.Reflection.PropertyInfo prop = typeof(vw_directory_search).GetProperty(field.FieldName); body = Expression.AndAlso(body, Expression.Call(member, "Equals", null, Expression.Convert(Expression.Constant(field.FieldValue), prop.PropertyType))); but it is throwing below error No coercion operator is defined between types 'System.String' and 'System.Nullable`1[System.DateTime]'. – User_MVC Feb 11 '13 at 12:36
@User_MVC it all depends on what the test is you want to do. If you are trying to compare it as a date, you'll need to parse the input as a date. – Marc Gravell Feb 11 '13 at 12:41
Yes, i want to compare with the date alone.can u provide some links on that. – User_MVC Feb 11 '13 at 12:43
`body = Expression.AndAlso(body, Expression.Equal(member, Expression.Constant(DateTime.Parse(field.FieldValue))))` ? – Marc Gravell Feb 11 '13 at 12:44
i tried this getting below error The binary operator Equal is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'. – User_MVC Feb 11 '13 at 12:57
@User_MVC then decide what which of these two things you want to compare: either convert the nullable one to non-nullable, or non-nullable to nullable. – Marc Gravell Feb 11 '13 at 13:04

score 0 · Answer 2 · answered Feb 08 '13 at 11:33

As the error says, you should not use like for non-string values. It also makes no sense. if you want to search for a specific datetime, you use datetime = value. For searching whitin a time range you can use between. You need to check the type of property you want to search for and use an appropriate way of fitting the type in a query.

Personally, i would never use such a 'dynamic query creator'.

Like search for datetime fields using dynamic lambda expression

2 Answers2