Anyone know What is wrong with my syntax here? I am making dynamic eSQL Queries and running into an error when trying to make where condition for DateTime data type. This is the error:
The query syntax is not valid. Near term '2011', line 1, column 135.
If it matters the DateTime type in my entity is actually nullable DateTime?
However, I thought this is the correct syntax from everything I've read.
Here is the code:
List<EntityFilter<FirstRead>> filters = new List<EntityFilter<FirstRead>>()
{
new EntityFilter<StudyFirstRead> { PropertyName = "username", OpType = ExpressionType.Equal, Value = "cwoodhouse" },
new EntityFilter<StudyFirstRead> { PropertyName = "FirstRead", OpType = ExpressionType.LessThan, Value = "DATETIME'2011-02-01 00:00'" }
};
Where EntityFilter is:
public class EntityFilter<T>
{
public string PropertyName { get; set; }
public ExpressionType OpType { get; set; }
public object Value { get; set; }
And I am building dynamic queries like so:
StringBuilder builder = new StringBuilder();
int counter = 0;
string baseStr = @"SELECT VALUE val FROM " + contextName + "." + tableName + " AS val WHERE val.";
builder.Append(baseStr);
foreach (EntityFilter<T> filter in filters)
{
//builder.Append(filter.PropertyName + " " + filter.OpTypeString() + " @p" + counter);
builder.Append(filter.PropertyName + " " + filter.OpTypeString() + "'" + filter.Value + "'");
counter++;
if (counter < filters.Count)
builder.Append(" AND val.");
else
{
break;
}
}
return builder.ToString();
It actually is the correct syntax for entity SQL (different than regular SQL or T-SQL).
Turns out the problem was too many single quotes, because I had them in both the EntityFilter object and the method that built the dynamic query.