efcore: Conversion of DateTime.Date doesn't work as expected

Hello everyone,

We have a project we are working on and we need to query the database for some data for a particular date.

We configured our DB to read and write date as UTC.

When writing the query to get the data, I noticed that the data for a date was not being pulled from the database.

Here is the code:

transactionDate = Convert.ToDateTime("2021-11-10:T10:00:00").ToLocalTime();

var transactions2 = _transactionsRepo.Query()
  .Where(transaction => transaction.AccountId == pharmacy.AccountId.Value)
  .Where(transaction => transaction.TransactionDate.Date == transactionDate.Date)
  .OrderByDescending(transaction => transaction.TransactionDate)
  .Skip(numToSkip)
  .Take(pageSize);

On investigation, I noticed that when pulling the data, the DB returns the date as UTC as it should and the date is compared to the input date. But no data is returned. I checked the query generated and noticed this:

DECLARE @__transactionDate_1 datetime = '2021-11-10T10:00:00.000';
DECLARE @__p_2 int = 0;
DECLARE @__p_3 int = 10;

SELECT *
FROM [WalletTransactions] AS [w]
WHERE ([w].[AccountId] = @__AccountId_Value_0) AND (CONVERT(date, [w].[TransactionDate]) = @__transactionDate_1)
ORDER BY [w].[TransactionDate] DESC
OFFSET @__p_2 ROWS FETCH NEXT @__p_3 ROWS ONLY

From the above, the query generated shows that the TransactionDate is converted to just Date and compared to the input date @__transactionDate_1 which is in DateTime form.

Any help on this will be deeply appreciated.

About this issue

Original URL
State: closed
Created 2 years ago
Comments: 20 (16 by maintainers)

Most upvoted comments

First, while I understand the desire to avoid refactoring your entire codebase to use UTC, I do recommend considering this. If the intent is to store UTC timestamps, then it’s highly recommended that your DateTime instances be of Kind=UTC. This would also prevent possible bugs if someone calls e.g. ToUniversalTime, or various other cases. Bottom line: if it’s UTC, mark it as such.

That aside, you should be able to specifically work around this issue by making sure that the specific DateTime instance is UTC:

// Assume we get an Unspecified DateTime from somewhere:
var dateTime = new DateTime(2022, 07, 07, 12, 0, 0);
// Mark the Unspecified DateTime as UTC as follows (this can be done outside the query as well).
// This will make the result of Date be a (truncated) UTC DateTime as well, and your value converter calling ToUniversalTime won't have any effect on it.
var query = context.Entities.Where(x => x.DateTime.Date == DateTime.SpecifyKind(dateTime, DateTimeKind.Utc).Date);

roji on Jul 10, 2022

However, the parameter isn’t actually being compared directly to the property, but a property on that property (x.DateTime.Date). So is it actually right to pass the parameter value through that converter?

Well, consider dateTime.AddDays() instead of Date:

var query = context.Entities.Where(x => x.DateTime.AddDays(1) == dateTime.AddDays(1));

It’s essentially the same as the Date example above, but I suspect you do want the value converter to apply to the results of AddDays(), no?

In this case, time obviously doesn’t pass through the property’s value converter and the query is like this: […] So I’m think that just because x.DateTime.Date returns the same type as x.DateTime, it still doesn’t mean the parameter value should pass through the value converter for x.DateTime.

We could indeed do this, and stop propagating the operand’s type mapping when Date is applied (we could even make it return date instead of datetime2). But I’m not sure that’s what most users want; consider the following:

var query = context.Entities.Where(x => x.DateTime.Date == dateTime);

If dateTime is Unspecified/Local, I think user’s may legitimately expect it to be converted to UTC before being compared; that’s why they set up the value converter, after all.

In other words, I can see pros and cons in both directions, and I think some people would be unhappy with either one. If that’s the case, it’s probably better to not break anything and leave the current behavior.

roji on Jul 8, 2022

@stevendarby That doesn’t repro in the full project posted:

      Executed DbCommand (3ms) [Parameters=[@__transactionDate_Date_0='2022-07-06T00:00:00.0000000Z' (DbType = DateTime), @__p_1='0', @__p_2='10'], CommandType='Text', CommandTimeout='30']
      SELECT COUNT(*)
      FROM (
          SELECT [a].[TransactionId], [a].[AccountId], [a].[Amount], [a].[Balance], [a].[DateCreated], [a].[DateUpdated], [a].[TransactionDate], [a].[TransactionStatus], [a].[TransactionType]
          FROM [AccountTransactions] AS [a]
          WHERE CONVERT(date, [a].[TransactionDate]) = @__transactionDate_Date_0
          ORDER BY [a].[TransactionDate] DESC
          OFFSET @__p_1 ROWS FETCH NEXT @__p_2 ROWS ONLY
      ) AS [t]

ajcvickers on Jul 7, 2022