DateTime.Date conversion in EF Core Generated Query-CodePudding

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 how to solve this will be deeply appreciated.

CodePudding user response：

For anyone facing the same issue, here is a link to the resolution on EF core repo on github:

https://github.com/dotnet/efcore/issues/28380

The issue was caused by a custom value converter that saves and reads all date as UTC.

This means a double conversion was happening and skewing the date time.

Solution is to specify that the input date is in UTC and hence should not be converted.