Prevent NOW () in LINQ to EF Query

Question

UPDATE . My original question was invalid because I did not read MySql logs correctly. I'm sorry. See below, updated.

I am using the LINQ query:

var homework = ctx.Threads.Where(t => t.ClassName == "10L" && t.EndDate != null && t.StartDate <= DateTime.Now && t.EndDate > DateTime.Now) .OrderByDescending(o => o.EndDate) .FirstOrDefault(); 

This creates SQL (MySQL 5.5.14):

 SELECT `Project1`.`id`, `Project1`.`title`, `Project1`.`startdate`, `Project1`.`enddate`, `Project1`.`class`, `Project1`.`body`, `Project1`.`threadtype` FROM (SELECT `Extent1`.`id`, `Extent1`.`title`, `Extent1`.`startdate`, `Extent1`.`enddate`, `Extent1`.`class`, `Extent1`.`body`, `Extent1`.`threadtype` FROM `threads` AS `Extent1` WHERE (((`Extent1`.`class` = '10L') AND (`Extent1`.`enddate` IS NOT NULL)) AND (`Extent1`.`startdate` <= (NOW()))) AND (`Extent1`.`enddate` > (NOW()))) AS `Project1` ORDER BY `Project1`.`enddate` DESC LIMIT 1 

How does LINQ to EF know how to use the NOW() function? Of course, I just pass it a regular DateTime structure by value?

If I use var now = DateTime.Now; and then use the now variable in the LINQ query, the date is passed as a literal. What's happening?

Answer 1

LINQ-to-whatever works by analyzing expression trees to convert from .NET code to the appropriate SQL language. This allows you to request processing of the largest possible side of the database instead of the client side. As a result, when you say:

 ... myField <= DateTime.Now 

The parser uses the DateTime.Now expression reference without evaluating it to maximize the conversion of the code into the corresponding SQL language. This is what allows LINQ to work efficiently, but as a side effect, everything in the query is interpreted as an expression and tries to convert to the appropriate SQL code. When you store it in a variable instead of:

 var now = DateTime.Now; 

The value is immediately evaluated and stored in now , and that value is used literally in your query instead of an expression.

Answer 2

DateTime.Now is a function (getter property for Now), so linq2EF will consider it as a function when trying to create an expression tree. it's how to say how .ToString() works in link2EF. Also, when you use it from a temporary variable, it will be considered as a temporary variable, for example, when you save the result .ToString() in a temporary variable, the final changes do not affect your result.

Answer 3

Good question and answer. I just wanted to add that in my environment using Microsoft SQL Server 2008 R2, a link to DateTime.Now in a LINQ to Entities query calls the following SQL expression:

CAST( SysDateTime() AS datetime2)

I believe this will be similar to creating NOW() in MySQL, so the same behavior is observed regardless of the underlying database.

Of course, this behavior can be a problem if you want the local time at which your LINQ code is running, and not the database server. The workaround for assigning DateTime.Now local variable and using it in a LINQ expression worked fine with MS SQL, as expected.