How do I format date literals in dynamic linq?

Question

I am using dynamic Linq to return data for user-input search criteria. My query is working fine except for the user selected dates. My current code is:

        StringBuilder whereClause = new StringBuilder();

        if (startDate.HasValue || endDate.HasValue)
        {
            DateTime searchStartDate = startDate.HasValue ? startDate.Value : DateTime.MinValue;
            DateTime searchEndDate = endDate.HasValue ? endDate.Value : DateTime.MaxValue;

            whereClause.AppendFormat("Date >= {0} && Date <= {1}",
                searchStartDate.Date.ToUniversalTime(),
                searchEndDate.Date.ToUniversalTime());
        }

        if (whereClause.Length > 0)
        {
            return (from p in this.repository.GetQueryable<Party>() select p)
                .Where(whereClause.ToString())
                .ToList();
        }

The query falls over because the comparison is being done between a DateTime field and a Int32 field, meaning the query has interpreted my date literals as integers.

How should I be formatting the dates?

Answer 1

Use

.Where("Date >= @0 && Date <= @1",
                searchStartDate.Date.ToUniversalTime(),
                searchEndDate.Date.ToUniversalTime())

instead.

In reply to Val's comment:

OK, then you can do:

whereClause.AppendFormat("Date.ToString() >= \"{0}\" && Date.ToString() <= \"{1}\"",
                searchStartDate.Date.ToUniversalTime(),
                searchEndDate.Date.ToUniversalTime());

You have to convert the Date in the query to a string and then compare it a quoted string literal. Without the quotes the parser is inerpreting the numbers inserted into the where clause as integers - what should explain the error you originally got.

Answer 2

Why are you parsing strings in a LINQ expression? The entire point of LINQ is to avoid that.

var q =  from p in this.repository.GetQueryable<Party>() select p;

if (startDate.HasValue || endDate.HasValue) 
{ 
  var searchStartDate = startDate.HasValue ? startDate.Value : DateTime.MinValue; 
  var searchEndDate = endDate.HasValue ? endDate.Value : DateTime.MaxValue; 
  return 
         q.Where (p=> p.Date >= searchStartDate.ToUniversalTime() 
                   && p.Date <= searchEndDate.ToUniversalTime()).ToList();
} 
return q.ToList();

UPDATE: In response to comments: I'm building that one at run-time. The question isn't run-time vs compile-time; it's "in strings" vs "in code". StringBuilder lets you append text; LINQ lets to chain lamdbas. It all works out the same --- except your code is type-safe and syntax checked using lambdas.

To demostrate this concept further, the following code compiles & runs fine, and allows to you to change the Where clause based on the values of oddsOnly and lowerLimit.

int[] nums = {1,2,3,4,5,6,7,8,9,10};

bool oddsOnly = true; 
bool lowerLimit = 5;

var q = from i in nums select i;

if (oddsOnly)
    q = q.Where( n=> n%2 == 1);

if (lowerLimit != 0)
    q = q.Where( n=> n >= lowerLimit);

foreach(var i in q)
    Console.WriteLine(i);

Depending on how you set those values, it will use zero, one or both of the where clauses.