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();
}

Amundsen

I think we need to see a bit of what your code is doing - I don't think we know enough to answer without lots of assumptions and guesses – Methodius

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();
}

Hulky

What if you don't know how many potential parameters will there be ? – Amundsen

Then you build up the array as you go. That's not hard. – Hulky

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

Brennabrennan

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;
    }

Godroon

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!

Leman

Mike did you ever have to do a comparison with just the date portion of the DateTime object? Trying to do this at the moment against an Entity Framework Entity Object. Have tried modifying the Dynamic Linq code to use myProperty.Value.Date using reflection but no go im afraid. Ideally I would like it to create a lambda like p=> p.MyDateTimeProperty.Value.Date == "2012/08/01/ rather than p=> p.MyDateTimeProperty == "2012/08/01". Any ideas on this one would be much appreciated. – Cramoisy