LINQ TO SQL, Dynamic query with DATE type fields

Question

I'm building a query with the LINQ dynamic library so I don't know how many potential parameters will I have and I get an error when trying to query DATE type fields:

Operator '>=' incompatible with operand types 'DateTime' and 'String'

When I step through the debugger in the Dynamic.cs it shows that the value is of type string and the field is of type date so the problem is obvious but I have no idea how to approach it.

Any ideas?

BR

Code:

using (MyEntities db = new MyEntities())
{
String SQLparam = "CreateDate >= \"" + DateTime.Now.ToShortDateString() + "\"";
List<UserList> UserList = db.UserList.Where(SQLparam).ToList();
}

Answer 1

You have to use a parameterized query, e.g.

using (MyEntities db = new MyEntities())
{
    String SQLparam = "CreateDate >= @1";
    List<UserList> UserList = db.UserList.Where(SQLparam, new [] { DateTime.Now }).ToList();
}

Answer 2

I had the same problem, but with Boolean as well, so I generalised the solution

    Expression ConstantParser<T>(Expression left, Token op, Expression right, Func<string, T> parser)
    {
        if (right is ConstantExpression)
        {
            try
            {
                var value = ((ConstantExpression)right).Value.ToString();
                return Expression.Constant(parser(value));
            }
            catch (Exception)
            {
                throw IncompatibleOperandsError(op.text, left, right, op.pos);  
            }
        }

        throw IncompatibleOperandsError(op.text, left, right, op.pos);  
    }

The lines in the main function then become...

else if (left.Type == typeof(DateTime) && right.Type == typeof(string))
{
    right = this.ConstantParser(left, op, right, DateTime.Parse);
}
else if (left.Type == typeof(DateTime?) && right.Type == typeof(string))
{
    right = this.ConstantParser(left, op, right, x => { DateTime? t = DateTime.Parse(x); return t; });
}
else if (left.Type == typeof(Boolean) && right.Type == typeof(string))
{
    right = this.ConstantParser(left, op, right, Boolean.Parse);
}

Only disadvantage I can see to this approach is that if the Parse fails, we will raise and exception, but given that we throw one anyway, I don't see that it matters too much

Answer 3

I was in the same boat and I was able to solve this by changing one method in the Dynamic Library. It's a hack, but it allows me to use dates in expressions with equality operators (=,>,<, etc..).

I'm posting the code here in case someone still cares.
The code I added is in the if block around line 53

else if (left.Type == typeof(DateTime) && right.Type == typeof(string))
{
    if (right is ConstantExpression)
    {
        DateTime datevalue;
        string value = ((ConstantExpression) right).Value.ToString();
        if (DateTime.TryParse(value, out datevalue))
        {
            right = Expression.Constant(datevalue);
        }
        else
        {
            throw IncompatibleOperandsError(op.text, left, right, op.pos);
        }
    }
    else
    {
        throw IncompatibleOperandsError(op.text, left, right, op.pos);
    }
}

The code basically checks if you are trying to compare a date field (left) with a string (right). And then it converts the right expression to a date constant

Here is the whole ParseComparison method:

        // =, ==, !=, <>, >, >=, <, <= operators
    Expression ParseComparison()
    {
        Expression left = ParseAdditive();
        while (token.id == TokenId.Equal || token.id == TokenId.DoubleEqual ||
            token.id == TokenId.ExclamationEqual || token.id == TokenId.LessGreater ||
            token.id == TokenId.GreaterThan || token.id == TokenId.GreaterThanEqual ||
            token.id == TokenId.LessThan || token.id == TokenId.LessThanEqual)
        {
            Token op = token;
            NextToken();
            Expression right = ParseAdditive();

            bool isEquality = op.id == TokenId.Equal || op.id == TokenId.DoubleEqual ||
                op.id == TokenId.ExclamationEqual || op.id == TokenId.LessGreater;
            if (isEquality && !left.Type.IsValueType && !right.Type.IsValueType)
            {
                if (left.Type != right.Type)
                {
                    if (left.Type.IsAssignableFrom(right.Type))
                    {
                        right = Expression.Convert(right, left.Type);
                    }
                    else if (right.Type.IsAssignableFrom(left.Type))
                    {
                        left = Expression.Convert(left, right.Type);
                    }
                    else
                    {
                        throw IncompatibleOperandsError(op.text, left, right, op.pos);
                    }
                }
            }
            else if (IsEnumType(left.Type) || IsEnumType(right.Type))
            {
                if (left.Type != right.Type)
                {
                    Expression e;
                    if ((e = PromoteExpression(right, left.Type, true)) != null)
                    {
                        right = e;
                    }
                    else if ((e = PromoteExpression(left, right.Type, true)) != null)
                    {
                        left = e;
                    }
                    else
                    {
                        throw IncompatibleOperandsError(op.text, left, right, op.pos);
                    }
                }
            }
            else if (left.Type == typeof(DateTime) && right.Type == typeof(string))
            {
                if (right is ConstantExpression)
                {
                    DateTime datevalue;
                    string value = ((ConstantExpression) right).Value.ToString();
                    if (DateTime.TryParse(value, out datevalue))
                    {
                        right = Expression.Constant(datevalue);
                    }
                    else
                    {
                        throw IncompatibleOperandsError(op.text, left, right, op.pos);
                    }
                }
                else
                {
                    throw IncompatibleOperandsError(op.text, left, right, op.pos);
                }
            }
            else
            {
                CheckAndPromoteOperands(isEquality ? typeof(IEqualitySignatures) : typeof(IRelationalSignatures),
                    op.text, ref left, ref right, op.pos);
            }
            switch (op.id)
            {
                case TokenId.Equal:
                case TokenId.DoubleEqual:
                    left = GenerateEqual(left, right);
                    break;
                case TokenId.ExclamationEqual:
                case TokenId.LessGreater:
                    left = GenerateNotEqual(left, right);
                    break;
                case TokenId.GreaterThan:
                    left = GenerateGreaterThan(left, right);
                    break;
                case TokenId.GreaterThanEqual:
                    left = GenerateGreaterThanEqual(left, right);
                    break;
                case TokenId.LessThan:
                    left = GenerateLessThan(left, right);
                    break;
                case TokenId.LessThanEqual:
                    left = GenerateLessThanEqual(left, right);
                    break;
            }
        }
        return left;
    }

Answer 4

Because I had to make a comparison for a DateTime? and I had make this modification.

else if (left.Type == typeof(DateTime?) && right.Type == typeof(string))
{
  if (right is ConstantExpression)
  {
    DateTime datevalue;
    string value = ((ConstantExpression)right).Value.ToString();
    if (DateTime.TryParse(value, out datevalue))
    {
      DateTime? nullableDateValue = datevalue;
      right = Expression.Constant(nullableDateValue, typeof(DateTime?));
    }
    else
    {
      throw IncompatibleOperandsError(op.text, left, right, op.pos);
    }
  }
  else
  {
    throw IncompatibleOperandsError(op.text, left, right, op.pos);
  }
}

Thanks for the tip!