1

Closed

OData V4 service should not support DateTime

description

OData V4 should not support DateTime as follows:

4.2.2 Pruned: Edm.DateTime
This data type convenient for some services and painful for a lot of clients: pico-second precision that may be off by 14 hours due to a wrongly guessed time zone. Edm.DateTimeOffset lets services share their knowledge of the time zone with their clients.

But, it still generates $metadata as follows, when the model has a System.DateTime property:

<Property Name="Id" Nullable="false" Type="Edm.Int32"/>
<Property Name="Name" Type="Edm.String"/>
<Property Name="Phone" Type="Edm.String"/>
<Property Name="Birth" Nullable="false" Type="System.DateTime"/>
  1. We should generate a proper error message when DateTime is used in the model.
Closed May 8 at 11:46 PM by yjhong
verified..

comments

yjhong wrote Mar 12 at 10:20 PM

Plus, when a model has a property of DateTime?, $metadata is generated like
<Property Name="ReleaseDate" Type="System.Nullable_1OfDateTime"/>
<Property Name="SupportedUntil" Type="System.Nullable_1OfDateTime"/>

I think this should be fixed. Otherwise, OData Client will throws the following exception while it serializes the responses.

System.InvalidOperationException was unhandled
HResult=-2146233079
Message=The complex type 'ODataClient.System.Nullable_1OfDateTime' has no settable properties.
Source=Microsoft.OData.Client

jacalvar wrote Mar 15 at 10:24 PM

There are several aspects to consider for representing a DateTime value with a DateTimeOffset this. Read http://stackoverflow.com/questions/4331189/datetime-vs-datetimeoffset for more information.

zjufish wrote Mar 21 at 8:29 AM

please confirm whether it is webapi or odl bug

BullCreek wrote Apr 4 at 7:53 PM

This is a fairly significant change (not saying it isn't the right one). Might be nice if this was included in the http://blogs.msdn.com/b/webdev/archive/2014/03/13/getting-started-with-asp-net-web-api-2-2-for-odata-v4-0.aspx article that a lot of people will be looking at in the coming days along with procedure to migrate your model and DB schema - it isn't as simple as just ALTER TABLE - you also have to account for day light savings time, etc. A good article is here:

http://blog.alanta.nl/2012/01/converting-to-datetimeoffset-in-sql.html

BullCreek wrote Apr 4 at 10:39 PM

Another thing to consider - I think this makes the combination of odata v4 and entity framework a total non starter on anything but SQL Server. I know for a fact that both the standard mysql entity provider and also the devart entity provider for mysql only support mapping to/from DateTime - not DateTimeOffset - I suspect it is similar for the postgres providers.

yjhong wrote Apr 7 at 6:50 PM

I think we need to throw an exception with a recommendation because of the following reasons:
  1. OData Client for .NET 6.2.0 proxies cannot handle the DateTime properly.
  2. EF silently maps the DateTimeOffset properties to the SQL datetime fields, and lose the information.
  3. All the OData time functions such as year and time do not work properly.
I don't think there are good reaons to use System.DateTime for the DateTime properties

BullCreek wrote Apr 8 at 9:19 PM

If this were odata version 1 and all the common databases and database providers that this open standard might be implemented on top of supported the semantics of DateTimeOffset, I'd think requiring properties to be DateTimeOffset would be a good idea. Since it is version 4 however, and the previous versions worked fine with DateTime and no database engine/provider other than SQL Server 2008 or later properly supports the semantics of DateTimeOffset, I think requiring all date related properties be DateTimeOffset or forcing developers into hacking all their POCOs up to work around this requirement is a horrible idea.

If you want to only support DateTimeOffset on the wire, I can live with that, but I'd think it would be fairly easy to convert DateTimes automatically to DateTimeOffsets as needed in the lower level code - so if you have a property that is a DateTime, you get a DateTimeOffset that is equivalent but just uses the servers default timezone info or something like that. I think that would keep things compatible - and if the developer wanted/needed more control over the timezone information, they could always change their properties to DateTimeOffset to get all the benefits.

Would this be possible?

jacalvar wrote Apr 9 at 11:22 PM

There are several aspects to why that doesn't work. Automatically converting from DateTime to DateTimeOffset using Utf is not valid for a simple reason. You don't know if that DateTime was in the local timezone or in UTC. That's why there is a DateTimeKind.Unspecified value. Even if you convert from a DateTime to a DateTimeOffset value, once you convert it to a DateTimeOffset you can't roundrip the value.

This excerpt from the msdn documentation explains it:
While an offset from UTC is one characteristic of a time zone, it does not unambiguously identify a time zone. Not only do multiple time zones share the same offset from UTC, but the offset of a single time zone changes if it observes daylight saving time. This means that, as soon as a DateTimeOffset value is disassociated from its time zone, it can no longer be unambiguously linked back to its original time zone.
The right thing to do is to use DateTimeOffset all the way through.

goroth wrote Apr 22 at 1:43 AM

I'm getting "The complex type 'ODataClient.System.Nullable_1OfDateTime' has no settable properties." when I have a nullable datetime. This comes from the T4 template but does any one know when / how to fix it? I tried to replace all the "System.Nullable_1OfDateTime" with "System.DateTime?" but that does not fix the problem.

goroth wrote Apr 22 at 12:58 PM

The old DataService Client was generating nullable DateTime like this. <Property Name="ShipmentDate" Type="Edm.DateTime" /> but the new T4 template is generating it like this <Property Name="ShipmentDate" Type="System.Nullable_1OfDateTime" />. If you try to replace all the Nullable_1OfDateTime with Edm.DateTime you will get this error "The complex type 'System.Nullable`1[System.DateTime]' has no settable properties."

goroth wrote Apr 22 at 1:03 PM

I just noticed the comment "OData V4 should not support DateTime". Is this correct? Is DateTime being removed from from OData V4 and if so what will it be replaced with? What type should the model use for DateTime?

goroth wrote Apr 22 at 1:33 PM

Well the answer was in front of me... http://docs.oasis-open.org/odata/odata/v4.0/os/part3-csdl/odata-v4.0-os-part3-csdl.html#_Toc372794021
Looks like all I had to do was change my model type from DateTime to DateTimeOffset. It's a shame that the generated code from the template couldn't have converted the DateTime at the client level so that the model on the server could stay DateTime but the client code could just drop the offset before sending it to or grabbing it from the OData service.

GuilhermeM wrote Jul 19 at 4:56 AM

Introducing a breaking change like this with absolutely no regard for backwards compatibility and existing systems is a joke.

The world shouldn't revolve and adapt to decisions like this on a v4. It is simply not possibly to change existing databases from DateTime to DateTimeOffset - take Dynamics CRM for example.

Every single Microsoft data or communication layer since forever supports System.DateTime. OData v4 not doing so and providing absolutely not guidance on a workaround is a joke.

AlexHayton123 wrote Jul 22 at 11:06 AM

This breaking change is extremely unhelpful for anyone who is maintaining a legacy app and wants to expose an OData v4 endpoint.

At the very least can you provide a config option that allows us to enable backwards compatibility with data structures that use DateTime?

goroth wrote Jul 22 at 2:20 PM

GuilhermeM and AlexHayton123 ... and anyone else that feels it is important.

please "VOTE" on this work item to add back or give alternative to datatime.

https://aspnetwebstack.codeplex.com/workitem/2072

Vaccano wrote Aug 7 at 6:41 PM

It is crazy not to support this.

There should be a massive amount of blog posts indicating how to work around this "Feature".

Why would you do this to us? Just support both!!!!!!!!!!!!!!

RoryPS wrote Aug 9 at 7:07 PM

@yjhong and the OData team, you are comedians. This change will be a source of hilarity for everyone considering using AspNet OData for years to come. I am impressed by your motivation to fix the world and it's lack of exactness, but I think at the same time as this change you should really have stopped people from using floating-point numbers too, and while we're at it I've always found the value Null to be quite unknown - perhaps OData v4 could have rid the world of such indeterminism?

Rory

gsulcer wrote Aug 25 at 2:51 PM

How do you propose developers deal with this change when upgrading v3 to v4? Consider legacy applications where the database cannot be changed.

Tpancoast wrote Aug 31 at 11:41 AM

Well, I'm glad I found out about this before I got too much further. OData is clearly too immature for us to use. I thought at version 4, they had probably worked out the major kinks, but this is a deal breaker. We still have to support SQL 2005, and even if we didn't, we aren't restructuring the entire database and associated applications just to support some bloated sense of developer purity.

Tpancoast wrote Aug 31 at 11:02 PM

Since this is one of the first hits for this problem, here's a partial workaround (that I have not tested personally yet). The workaround gets Entity Framework to map a DateTimeOffset in the class to a DateTime in the database without having to map all your properties manually. Your public property still has to be DateTimeOffset for OData's sake.

http://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/

Credit, I think, goes to Ladislav Mrnka.

http://stackoverflow.com/questions/9389954/entity-framework-mapping-datetimeoffset-to-sql-server-datetime

And originally to Jiří Činčura for the general technique:

http://blog.cincura.net/232731-mapping-private-protected-properties-in-entity-framework-4-x-code-first/

Vaccano wrote Oct 10 at 5:56 PM

I think it is hilarious that this is listed as a "Medium" impact.

When in fact is will be the reason developers don't use OData v4 or implementers deviate from the spec.

Whoever thought of this was living in a blue sky dream world and clearly never has to do any real world programming.