How do I format date literals in dynamic linq?

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 fi开发者_运维问答eld and a Int32 field, meaning the query has interpreted my date literals as integers.

How should I be formatting the dates?

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.

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.

The Dynamic LINQ string would need to look something like:

"Date >= DateTime(2015, 10, 21)"

This is mentioned in the documentation in the DynamicQuery project in the download mentioned at http://weblogs.asp.net/scottgu/dynamic-linq-part-1-using-the-linq-dynamic-query-library.

Note, there isn't a new before the DateTime constructor.

I tried this and it works. I'm using Telerik's RadGrid control for ASP.NET AJAX. The grid builds the filter string and I needed to add the filter to my query to get the filter to execute in the database using LINQ to Entities. The problem is that the generated filter needed to be altered a little for it to work with LINQ to Entities as opposed to LINQ to Objects. It was doing a DateTime.Parse() which isn't supported in LINQ to Entities.