LINQ TO SQL, a dynamic query with fields of type DATE

Question

I create a query with the LINQ dynamic library, so I don’t know how many potential parameters I have, and I get an error when I try to request fields of type DATE:

The operator '> =' is not compatible with the operand types 'DateTime' and 'String'

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

Any ideas?

BR

the code:

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

Answer 1

You should use a parameterized query like

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

Answer 2

I was in the same boat, and I was able to solve this by changing one method in a dynamic library. This is a hack, but it allows me to use dates in expressions with equality operators (=,>, <, etc.).

I post the code here if 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); } } 

Basically, the code checks to see if you are trying to compare a date field (left) with a string (right). And then it converts the correct 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 3

I had the same problem but with boolean, so I generalized 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); } 

The only drawback that I see in this approach is that if a failure of the analysis is not possible, we will raise and exclude it, but given that we throw it all the same, I do not see that this is too important.

Answer 4

Because I needed to do a comparison for DateTime? and I made 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!