Prevent 'NOW()' in LINQ to EF query

Question

UPDATE: My original question was invalid because I was misreading the MySql logs. Sorry. Please 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 the 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 to use the NOW() function? Surely I'm just passing it a regular DateTime struct 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 going on?

Answer 1

LINQ-to-whatever operates by parsing expression trees to convert from .NET code to the appropriate SQL language. This allows the query to process as much as possible database-side instead of client-side. As a result, when you say:

... myField <= DateTime.Now

The parser uses the expression reference DateTime.Now, without evaluating it, in order to convert as much as possible of the code to appropriate 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 attempted to convert to appropriate SQL code. When you store it in a variable instead, as:

var now = DateTime.Now;

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