entity-frameworklinq-to-entitiesentity-sql

Entity SQL Datetime Syntax error


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

Solution

  • 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.