Closed as not planned
Description
openedon Sep 20, 2023
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment