I am trying to write an EF6 LINQ query that takes a DateTimeOffset and retrieves all database entries that have a timestamp with the same local date as the given. The database has all timestamps as UTC, but the DateTimeOffset argument contains a local offset from UTC that serves as a basis for the "same day" identification.
For example, let's say the database has two entries with timestamps of "2024-09-01 4PM UTC+0" and "2024-09-02 5AM UTC+0" respectively.
If I pass in a DateTimeOffset that is "2024-09-01 11AM UTC-7", I would expect both entries to match: "2024-09-01 4PM UTC+0" converts to "2024-09-01 9AM UTC-7" and "2024-09-02 5AM UTC+0" converts to "2024-09-01 10PM UTC-7", and both results have a date that matches the argument's date of 2024-09-01.
If I pass in a DateTimeOffset of "2024-08-31 9PM UTC-7", there should be no matches because converting the database timestamps to UTC-7 produces dates falling on "2024-09-1" and not "2024-08-31". Please note that the given timestamp here converts to "2024-09-01 4AM UTC+0", but there are no matches because the local offset serves as the basis-- not UTC.
Finally, giving a DateTimeOffset of "2024-09-02 3AM UTC+1" should match only the database entry with a timestamp of "2024-09-02 5AM UTC+0" because that timestamp converts to "2024-09-02 6AM UTC+1" which has a matching 2024-09-02 date, but the entry with "2024-09-01 4PM UTC+0" converts to "2024-09-01 5PM UTC+1" and does not match.
Laying it out like above would make it appear to be simple to write a C# function that finds matches given a DateTimeOffset and a list of timestamps. However, I would like to do all of the processing on the database if possible, to make use of indexes and to minimize the number of rows retrieved (that is, fetching only the entries that pass the check).
As a result, I cannot use something like:
return context.Table.Where(row => row.Timestamp.ToOffset(givenDTO.Offset).Date == givenDTO.Date).ToList();
because ToOffset() and Date are not supported in LINQ to Entities.
I have tried to use System.Data.Entity.DbFunctions, but I can't find many examples of use, and so my results have all been erroneous or throw exceptions. For example:
return context.Table.Where(row => DbFunctions.DiffDays(row.Timestamp, givenDTO) <= 1).ToList()
fails the second case where "2024-08-31 9PM UTC-7" is given.
In an attempt to create a new DateTimeOffset for each entry via DbFunctions.CreateDateTimeOffset() to compare against the given DateTimeOffset, passing givenDTO.Offset.Hours for the int? timeZoneOffset parameter produces an OverflowException if the given DateTimeOffset's offset is negative.
I'm not sure how to write this query. Is it even possible?
Instead of trying to convert the database Timestamp values into local times for comparison, I believe you can more easily convert your given local reference time into a UTC date/time range, that is then used as a filter in your query.
Something like:
DateTime fromUtc = givenDTO.Date - givenDTO.Offset; // Not sure of the exact expression
DateTime toUtc = fromUtc.AddDays(1);
return context.Table
.Where(row => row.Timestamp >= fromUtc && row.Timestamp < toUtc)
.ToList();
One big advantage of this approach is that the resulting query is sargable, meaning an index on the Timestamp column can be used to efficiently select the desired rows.
Using your example: Given a datetimeoffset of 2024-09-01 11:00 UTC-7, we would truncate the date and adjust to UTC yielding fromUtc = 2024-09-01 07:00 and then add 24 hours for toUtc = 2024-09-02 07:00. We then query for rows having Timestamp >= '2024-09-01 07:00' && Timestamp < '2024-09-02 07:00'. This would select both of your example rows having Timestamp values of 2024-09-01 16:00 and 2024-09-02 05:00.
Note that the toUtc value is exclusive. Matching values are not included in the results.