Entity SQL Datetime Syntax error

Question

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

Answer 1

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.

Answer 2

according to you code the end of your SQL statement would produce something like

AND val.FirstRead = 'DATETIME'2011-02-01 00:00''

when executed you will get error

Error 102: Incorrect syntax near '2011'.

obviously that is not syntactically correct SQL, the quick fix whould be to have your filters collection as such:

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 = "2011-02-01 00:00"
    }
};