1

Closed

Support DateTimeOffset.DateTime and DateTime.Date properties.

description

I'm finding it next to impossible to get the local DateTime component of a DateTimeOffset in entity framework. It must use a "cast(x as datetime2(7))" as opposed to a convert, because any kind of conversion typically treats it as a UTC time. Alternatively, add DbFunctions.TruncateOffset. There is already a TruncateTime function, but that's useless, because it truncates the time and keeps the offset. I need a method that truncates the offset and keeps the time, so that I can query for the local part of the DateTimeOffset field.



var startDate = new DateTime(2016,12,15,8,0,0); //Dec 15, 2016 8:00AM

Assuming 'StartInstant' is a nullable DateTimeOffset field, I'd like to be able to do one of these:

If you added support for the DateTIme property of DateTimeOffset:
query.Where(x => x.StartInstant.Value.DateTime >= startDate);
//Generated SQL... "where cast(StartInstant as datetime2(7)) >= @p__linq__0"

If you added DbFunctions method "DateTime? TruncateOffset(DateTimeOffset?)":
query.Where(x => DbFunctions.TruncateOffset(x.StartInstant) >= startDate);
//Generated SQL... "where cast(StartInstant as datetime2(7)) >= @p__linq__0"

Currently my only option is this monster, which also loses some precision and generates what surely is nasty index-busting SQL for the field:
query.Where(x => DbFunctions.CreateDateTime(x.StartInstant.Value.Year, x.StartInstant.Value.Month, x.StartInstant.Value.Day, x.StartInstant.Value.Hour, x.StartInstant.Value.Minute, x.StartInstant.Value.Second) >= startDate);
//Generated SQL (yes, seriously>>>)... ((convert (datetime2,right(''000'' + convert(varchar(255), DATEPART (year, [Extent1].[StartInstant])), 4) + ''-'' + convert(varchar(255), DATEPART (month, [Extent1].[StartInstant])) + ''-'' + convert(varchar(255), DATEPART (day, [Extent1].[StartInstant])) + '' '' + convert(varchar(255), DATEPART (hour, [Extent1].[StartInstant])) + '':'' + convert(varchar(255), DATEPART (minute, [Extent1].[StartInstant])) + '':'' + str( CAST( DATEPART (second, [Extent1].[StartInstant]) AS float), 10, 7), 121)) >= @p__linq__0
Closed Mar 13 at 8:05 PM by ajcvickers
EF Team Triage: Thanks for reporting an issue on Entity Framework 6.x. This project is now maintained on GitHub (https://github.com/aspnet/EntityFramework6). Please file your issue on the GitHub issue tracker. We will no longer be monitoring this issue tracker for comments, so please do not reply here.

comments

triynko wrote Dec 15, 2016 at 7:18 PM

var startDate = new DateTime(2016,12,15,8,0,0); //Dec 15, 2016 8:00AM

Assuming 'StartInstant' is a nullable DateTimeOffset field, I'd like to be able to do one of these:

If you added support for the DateTIme property of DateTimeOffset:
query.Where(x => x.StartInstant.Value.DateTime >= startDate);

If you added DbFunctions method "DateTime? TruncateOffset(DateTimeOffset?)":
query.Where(x => DbFunctions.TruncateOffset(x.StartInstant) >= startDate);

Currently my only option is this monster, which also loses some precision:
query.Where(x => DbFunctions.CreateDateTime(x.StartInstant.Value.Year, x.StartInstant.Value.Month, x.StartInstant.Value.Day, x.StartInstant.Value.Hour, x.StartInstant.Value.Minute, x.StartInstant.Value.Second) >= startDate);

triynko wrote Dec 15, 2016 at 7:26 PM

Lack of this feature is a HUGE oversight. Without it, DateTimeOffset type is useless, because it cannot be treated as anything other than a normal UTC time in linq-to-entity queries, and so it defeats the purpose of having a separate datetime component and offset component, because you cannot inspect the local datetime component of the field in any way.

triynko wrote Dec 27, 2016 at 8:03 PM

The only way I've found to work around this without loss of precision is as follows. If I have a DateTimeOffset field in the database named "DTOField" and some static local DateTime value to compare which we'll call variable "localDateTimeToCompare", then the following transform in entity framework will work:

var comparison = new DateTImeOffset( localDateTimeToCompare, TimeSpan.Zero );

DbFunctions.AddMinutes(x.DTOField, DbFunctions.GetTotalOffsetMinutes(x.DTOField)) <= comparison;

That tricks it into offset the local datetime component of the DateTimeOffset by the offset, so that when it's offset by the offset it ends up with the local time with a zero offset.... that local time with zero offset can then be compared to the value we constructed with a zero offset. However, this is ridiculous to have to do this. Add a DbFunctions.TrucateOffset method that accepts a DateTimeOffset and returns the local DateTime portion.