How do I format date literals in dynamic linq?

Question

I use dynamic Linq to return data for custom search criteria. My query works fine except for the dates selected by the user. 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 request crashes because the comparison is between the DateTime field and the Int32 field, which means the request interpreted my date literals as integers.

How do I format dates?

Answer 1

Why do you parse strings in a LINQ expression? The whole point of LINQ is to avoid this.

 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 the comments: I create this at runtime. The question is not at run time, but at compile time; it is "in lines" and "in code." StringBuilder allows you to add text; LINQ allows you to connect lamps. It all works the same way: except for your code, the type and syntax checked with lambdas are safe.

To demonstrate this concept again, the following code compiles and works fine, and allows you to modify the Where clause based on the 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 these values, it will use zero, one or both where clauses.

Answer 2

Use

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

instead.

In response to Val's comment:

OK, then you can do:

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

You need to convert the date in the query to a string, and then compare its quoted string literal. Without the quotes, the parser enters the numbers inserted in the where clause as integers, which should explain the error you have encountered.

Answer 3

The Dynamic LINQ line should look something like this:

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

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

Note that there is no new before the DateTime constructor.

I tried this and it works. I am using the Telerik RadGrid control for ASP.NET AJAX. The grid builds a filter string, and I need to add a filter to my query so that the filter runs in the database using LINQ to Entities. The problem is that the generated filter needs to be modified a bit so that it works with LINQ to Entities, not LINQ to Objects. It executed DateTime.Parse() , which is not supported in LINQ for Entities.