CODEWITHSUNDEEP
sqlentity-framework
Marco
Marco

Reputation: 5109

EF generated SQL Query returns wrong result

I want to filter on a specific date. Because of the control I use I can also filter on a range of dates, but when I filter on a single date it should also work so I have total flexibility.

When I filter on 2004-11-24 my code generates the following query with Entity Framework. However somehow the query returns me the results of 2004-11-25 instead of 2004-11-24.

What is wrong with this query? 

SELECT TOP (26) 
[Filter1].[Id] AS [Id], 
[Filter1].[AliquotId] AS [AliquotId], 
[Filter1].[ScientificStudyId] AS [ScientificStudyId], 
[Filter1].[ExpectedSpecimenId] AS [ExpectedSpecimenId], 
[Filter1].[ExpectedAliquotId] AS [ExpectedAliquotId], 
[Filter1].[VisitId] AS [VisitId], 
[Filter1].[BoxId] AS [BoxId], 
[Filter1].[ParticipantId] AS [ParticipantId], 
[Filter1].[Product] AS [Product], 
[Filter1].[BodyMaterial] AS [BodyMaterial], 
[Filter1].[AliquotNr] AS [AliquotNr], 
[Filter1].[ReservationName] AS [ReservationName], 
[Filter1].[Visit] AS [Visit], 
[Filter1].[Win32Color] AS [Win32Color], 
[Filter1].[Position] AS [Position], 
[Filter1].[Barcode] AS [Barcode], 
[Filter1].[Temperature] AS [Temperature], 
[Filter1].[DivergentStorageTemperature] AS [DivergentStorageTemperature], 
[Filter1].[BoxName] AS [BoxName], 
[Filter1].[CollectionDate] AS [CollectionDate], 
[Filter1].[Positioned] AS [Positioned], 
[Filter1].[CheckedOut] AS [CheckedOut], 
[Filter1].[CheckinDate] AS [CheckinDate], 
[Filter1].[CheckoutDate] AS [CheckoutDate], 
[Filter1].[LabelsPrinted] AS [LabelsPrinted], 
[Filter1].[ParticipantNr] AS [ParticipantNr], 
[Filter1].[Specimen] AS [Specimen], 
[Filter1].[ContainerType] AS [ContainerType], 
[Filter1].[ContainerTypeId] AS [ContainerTypeId], 
[Filter1].[Quantity] AS [Quantity], 
[Filter1].[Unit] AS [Unit], 
[Filter1].[QuantityUnit] AS [QuantityUnit], 
[Filter1].[StudyVersion] AS [StudyVersion], 
[Filter1].[ThawCount] AS [ThawCount], 
[Filter1].[Sop] AS [Sop], 
[Filter1].[NrOfRemarks] AS [NrOfRemarks], 
[Filter1].[RemarkSummary] AS [RemarkSummary]
FROM ( SELECT [Extent1].[Id] AS [Id], [Extent1].[AliquotId] AS [AliquotId], [Extent1]. [ScientificStudyId] AS [ScientificStudyId], [Extent1].[ExpectedSpecimenId] AS [ExpectedSpecimenId], [Extent1].[ExpectedAliquotId] AS [ExpectedAliquotId], [Extent1].[VisitId] AS [VisitId], [Extent1].[BoxId] AS [BoxId], [Extent1].[ParticipantId] AS [ParticipantId], [Extent1].[Product] AS [Product], [Extent1].[BodyMaterial] AS [BodyMaterial], [Extent1].[AliquotNr] AS [AliquotNr], [Extent1].[ReservationName] AS [ReservationName], [Extent1].[Visit] AS [Visit], [Extent1].[Win32Color] AS [Win32Color], [Extent1].[Position] AS [Position], [Extent1].[Barcode] AS [Barcode], [Extent1].[Temperature] AS [Temperature], [Extent1].[DivergentStorageTemperature] AS [DivergentStorageTemperature], [Extent1].[BoxName] AS [BoxName], [Extent1].[CollectionDate] AS [CollectionDate], [Extent1].[Positioned] AS [Positioned], [Extent1].[CheckedOut] AS [CheckedOut], [Extent1].[CheckinDate] AS [CheckinDate], [Extent1].[CheckoutDate] AS [CheckoutDate], [Extent1].[LabelsPrinted] AS [LabelsPrinted], [Extent1].[ParticipantNr] AS [ParticipantNr], [Extent1].[Specimen] AS [Specimen], [Extent1].[ContainerType] AS [ContainerType], [Extent1].[ContainerTypeId] AS [ContainerTypeId], [Extent1].[Quantity] AS [Quantity], [Extent1].[Unit] AS [Unit], [Extent1].[QuantityUnit] AS [QuantityUnit], [Extent1].[StudyVersion] AS [StudyVersion], [Extent1].[ThawCount] AS [ThawCount], [Extent1].[Sop] AS [Sop], [Extent1].[NrOfRemarks] AS [NrOfRemarks], [Extent1].[RemarkSummary] AS [RemarkSummary], row_number() OVER (ORDER BY [Extent1].[CollectionDate] ASC) AS [row_number]
FROM [dbo].[ClaimedAliquotPositionsReadModels] AS [Extent1]
WHERE (cast('0ebebae6-594d-4be0-ac8b-a078013f1370' as uniqueidentifier) = [Extent1].[ScientificStudyId])
AND ([Extent1].[CheckoutDate] >= convert(datetime2, '2004-11-24 00:00:00.0000000', 121))
AND ([Extent1].[CheckoutDate] < convert(datetime2, '2004-11-25 00:00:00.0000000', 121))
)  AS [Filter1]
WHERE [Filter1].[row_number] > 0
ORDER BY [Filter1].[CollectionDate] ASC

Upvotes: 1

Views: 301

Answers (1)

Marco
Marco

Reputation: 5109

Solved it by parsing my input like this.

DateTime.TryParse(splitted[0], CultureInfo.InvariantCulture, DateTimeStyles.AssumeLocal | DateTimeStyles.AdjustToUniversal, out date1)

intstead of

DateTime.TryParse(splitted[0], out date1)

This results in the following where clause

WHERE (cast('0ebebae6-594d-4be0-ac8b-a078013f1370' as uniqueidentifier) = [Extent1].[ScientificStudyId])
AND ([Extent1].[CheckoutDate] >= convert(datetime2, '2004-11-23 23:00:00.0000000', 121))
AND ([Extent1].[CheckoutDate] < convert(datetime2, '2004-11-24 23:00:00.0000000', 121))

The sql generated is 1 day before the value provided. So 2004-11-24 becomes 2004-11-23

Upvotes: 2

Related Questions