LINQ to Entities中DateTime两种使用方式的差异及原理探讨

Great question—this is a common gotcha with LINQ to Entities that trips up a lot of developers, so let’s unpack it step by step.

Why the Difference Happens #

The core issue comes down to how Entity Framework parses your LINQ expression:

• When you assign DateTime.Now to a variable first (var timeNow = DateTime.Now), that value is calculated on your client machine the moment the variable is declared. EF sees this as a fixed constant, so it generates a parameterized SQL query (via exec sp_executesql) where the parameter is set to that specific timestamp.
• When you use DateTime.Now directly inside the Where clause, EF doesn’t evaluate it on the client. Instead, it maps the .NET method to a database-native function—for SQL Server, that’s SysDateTime(). This means the database will calculate the current time for every row comparison during query execution.

That’s why you saw differing results: if your query takes even a fraction of a second to run, SysDateTime() will update as the query processes rows, while the variable approach uses a single fixed time from when the query started. For example, a row with a SendDate that’s inserted right in the middle of the query might be included with SysDateTime() but excluded with the variable (or vice versa).

Which Approach is the Best Practice? #

In almost all cases, using the variable approach is better—here’s why:

• Consistent results: Your query uses a single, fixed timestamp for every row comparison. No more worrying about inconsistent results if the query runs longer than expected or data changes mid-execution.
• Better performance: Parameterized queries are cached by SQL Server’s query plan optimizer. This means subsequent runs of the same query (with different timestamps) can reuse the same plan, speeding up execution. Queries with SysDateTime() don’t get this benefit as reliably.
• Easier testing: If you’re writing unit or integration tests, you can easily replace the timeNow variable with a fixed test timestamp (e.g., new DateTime(2024, 5, 20)). Testing with direct DateTime.Now would require mocking system time, which is far more cumbersome.

When to Use Direct DateTime.Now? #

The only exception is if you explicitly need the database server’s current time for every row comparison—for example, in a real-time system where you must prioritize the database’s clock over the client’s, and you accept that results might shift during query execution. This scenario is rare, though.

Example SQL Outputs #

To make this concrete, here’s what each approach generates:

• Variable approach SQL:

  exec sp_executesql N'SELECT [m].[Id], [m].[SendDate], ... FROM [MailshotHistoryDatas] AS [m] WHERE [m].[SendDate] < @p0', N'@p0 datetime2(7)', @p0='2024-05-20 14:45:30.1234567'
  

• Direct DateTime.Now SQL:

  SELECT [m].[Id], [m].[SendDate], ... FROM [MailshotHistoryDatas] AS [m] WHERE [m].[SendDate] < SysDateTime()
  

So to recap: save DateTime.Now to a variable first for most use cases—it’s more predictable, performant, and testable.

内容的提问来源于stack exchange，提问作者SumGuy