LINQ TO SQL, Dynamic query with DATE type fields

Question

Hello,

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 need to use DateTime.Parse(String s) to convert your string to be a valid DateTime before you plug it into your LINQ statement.

Answer 2

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