EF not generating SQL query correctly on fields that have conversion

[mhuete-iP]

Setup

I have an entity model with a DateTime property named "Fecha Compra":

public class Ticket{
   // Some properties

   DateTime FechaCompra;

   // Some other properties
}

This DateTime value is stored in the SQLServer database as a BIGINT with the format "yyyyMMddHHmmss". Let's say I have the date 2023/09/20 09:15:00, then the BIGINT stored in the database is 20230920091500. This is achieved using the ".HasConversion" method in the property:

entity.Property(e => e.FechaCompra).HasConversion(new DateTimeToLongConverter());

Issue

My problem comes when I try to filter using this field. For example, I have the follwing chunk of code:

int count = repo.TicketsOperaciones
    .Where(c => ((c.FechaCompra.Date >= 2023-09-17 00:00:00) And (c.FechaCompra.Date <= 2023-09-20 00:00:00)))
    .Count();

When entity automatically converts this to the SQL query I get the following:

SELECT COUNT(*)
FROM [APP_TICKETS_OPERACIONES] AS [a]
WHERE (CASE
    WHEN CONVERT(date, [a].[FechaCompra]) >= CAST(20230917000000 AS bigint) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN CONVERT(date, [a].[FechaCompra]) <= CAST(20230917000000 AS bigint) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) = CAST(1 AS bit)

As you can see, it is trying to compare a date on the left side of the comparison (it even has to convert it!!!) to a bigint in the right side of the comparison. So this gets me an exception every time:

Microsoft.Data.SqlClient.SqlException
  HResult=0x80131904
  Message=No se permite la conversión explícita del tipo de datos bigint a date.
  Source=Core Microsoft SqlClient Data Provider
  StackTrace:
   at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
   at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
   at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
   at Microsoft.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.InitializeReader(Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.<>c.<MoveNext>b__21_0(DbContext _, Enumerator enumerator)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable`1.Enumerator.MoveNext()
   at System.Linq.Enumerable.TryGetSingle[TSource](IEnumerable`1 source, Boolean& found)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at SQLServerCity.TicketRepo.ObtenerListOperacionesImportes(GetRequestList opciones) in C:\iParksa\iParkCity_CSharp\Infrastructure\Salida\SQLServerCity\TicketRepo.cs:line 26
   at ServicesCity.TicketService.ObtenerListaImportesOperaciones(GetRequestList opciones) in C:\iParksa\iParkCity_CSharp\Services\ServicesCity\TicketService.cs:line 56

  This exception was originally thrown at this call stack:
    [External Code]
    SQLServerCity.TicketRepo.ObtenerListOperacionesImportes(Utilities.Classes.GetRequestList) in TicketRepo.cs
    ServicesCity.TicketService.ObtenerListaImportesOperaciones(Utilities.Classes.GetRequestList) in TicketService.cs

Provider and version information

EF Core version: 7.0.11
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 7.0
Operating system: Windows 11 22H2
IDE: Visual Studio 2022 17.7.4

[roji]

What kind of SQL are you expecting for c.FechaCompra.Date? Given that c.FechaCompra is a number in the database, extracting a date out of that doesn't work (at least not without some serious hacking).

More generally, calling methods/members on value converted properties is not supported, see #10434. You can use user-defined function mapping to have some custom static .NET function which, when used in a query, translates to arbitrary SQL which would perform the date extraction; but you'd still need to figure out how to extract a date from your database long in T-SQL.

[roji]

Duplicate of #10434

[mhuete-iP]

First of all, thank you for the quick response.

Secondly, following on the topic, the SQL I'm expecting is the same but just removing the "CONVERT" statement, so it would simply compare a BIGINT to a BIGINT:

SELECT COUNT(*)
FROM [APP_TICKETS_OPERACIONES] AS [a]
WHERE (CASE
    WHEN [a].[FechaCompra] >= CAST(20230917000000 AS bigint) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END & CASE
    WHEN [a].[FechaCompra] <= CAST(20230917000000 AS bigint) THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END) = CAST(1 AS bit)

I see that EF core is able to correctly translate to SQL the conversion from DateTime to long and viceversa, so the issue is not that it has trouble converting properties. The problem is that it converts both the date to a long and the long to a date, and later comparing them, which ends up causing the exception of "Explicit conversion from bigint to date data type is not allowed."

I will dig deeper into #10434 and the resources you attached because, for now, I still don't get why it can't be done.

[roji]

@HU373 I'm not quite following... Your LINQ query has c.FechaCompra.Date >= .... - that means you're requesting that the time component be truncated from FechaCompra. This is what CONVERT(date, ...) does in the SQL you're seeing (though it doesn't work since FechaCompra isn't an actual SQL Server timestamp, but rather a number). The SQL you expect in the comment above seems to completely ignore that .Date part.