OData V4 service should support DateTime

[LianwMS]

OData V4 should support DateTime or you should give the developer a way of converting datatime to datetimeoffset when the service is called.
I have several serializable objects that are in 3rd party libraries (even Microsoft core classes) that have datatime properties that will not be exposable through OData V4 services.

here is OASIS meeting showing that Edm.DateTime was discussed as adding back but then was "defer this to a future version"?
https://issues.oasis-open.org/browse/ODATA-220

Work Item Details

Original CodePlex Issue: Issue 2072 Status: Active Reason Closed: Unassigned Assigned to: xuzhg Reported on: Jul 22, 2014 at 3:18 PM Reported by: goroth Updated on: Thu at 9:56 PM Updated by: jbeanky

[ImGonaRot]

@robertmclaws The workaround does not work when data binding to grids (Winform or ASPX) using 3rd party controls. The grid thinks it is text / string and will not show a date time picker as the editor. Also you can't query the odata service correctly to filter by date if you are only exposing a string for the date.

[robertmclaws]

Yes you can, if you implement the getters and setters properly. In WinForms you can also use a Model that inherits from your Client object and does the conversions for you.

[ImGonaRot]

@robertmclaws You can't implement your own getters / setters and get LINQ to work properly when querying the OData service. The OData libraries don't know how to convert LINQ queries into OData "URL" queries when using custom get / set. Both Telerik and DevExpress have their own custom OData Data Source and neither of those data sources work with querying the OData service when you implement a DateTimeOffset as a String. While I can write my own custom data source, I would lose out on things like built in paging and custom filters the both Telerik and DevExpress have already written that can convert those into OData query syntax. OData V3 was much easier and worked with all my 3rd party controls out of the box without generating tons of extra code. All I had to do was generate a proxy using the OData Client generator and then it just worked. With OData V4 I still generate the proxy using the OData Client generator but I have to add lots of extra code on the client side to account for DateTimeOffset. The point is that I am tied to 3rd party controls and if I have to "extend" my classes by creating fake properties or extend controls, it is not saving me time and is not worth the effort to move to OData V4. I do agree that DateTimeOffset is nice but I also agree that I still need to support DateTime "while" I try to transition to DateTimeOffset.

[TehWardy]

Where i get annoyed is that UI frameworks refuse to support datetimeoffset because they build for multiple back ends and Microsoft seems to be the owner of the datetimeoffset definition.

Personally though i can see a situation for just collecting or returning a date or a time and not a whole datetime and the fact that I can't seem to declare either is baffling to me.

for example ... when an invoice is created is typically a point in time (represented by a datetimeoffset but it's common to refer to the creation date of an invoice as well ... exactly that ... it's "Creation DATE".

This debate has come about because the whole ecosystem in this area needs reworking. The way something is rendered or bound to a UI should be subjective and based on business context but the way something is stored is not a subjective matter.

My issue is that I am forced to always add the precision of a datetimeoffset no matter my situation of reason for the field in question. I would prefer a means to declare in the odata model "this field is just a date" and have it return just a Edm.Date instead of being forced to use datetimeoffset in all cases and then be forced to make assumptions about historical data that cause me as a programmer to code in a potential inaccuracy in the value returned purely because the information isn' there and a standard forces it to be.

The suggestion above of using strings also wont work because date logic would then not be able to be added to fields during queries so there has to be some means of working with a date & time aware object.

As stated above somewhere ... this need to force the extra precision is like not supporting integers and forcing decimal values on the entire api layer ... the mind boggles!

[TehWardy]

@Vaccano in your case ... so you have an api that is accepting datetimes? are these values a representation of a point in time from the source system or just a date on which an event occurred.

I find this distinction important but agree that your api should ALWAYS store utc data and do the conversion internally if one is required. I have found ... much like you though ... there's a difference between "this happened on this date some 500 years ago" (which requires no precision) and "this invoice was created at this point in time" which depending on context may need the precision or may not.

I think it's good that the WebAPI guys and the Oasis guys are trying to promote good working standards and everything is workable ... but it may often result in assumptions.

In your case ... the caller could be in any timezone, so if they say "here's a datetime value" how do you translate that? This to me is the biggest problem we have with OData ... there's no way to easily in the API determine the time zone the caller means when they don't provide it ... so the oasis answer is "force them to provide it or assume UTC".

That of course means forcing an entire industry of legacy systems to start behaving ... oh dear ... Did these guys ever work in IT ?

lol

[Vaccano]

@TehWardy - I have kind of given up on this (really I gave up on OData several years ago). I got my hopes back up by misinterpreting one of the their tags.

It is kind of a lost cause because @mikepizzo and the others that control the project have made up their minds on this. And not enough people voice a dissenting opinion to make a difference.

But I agree that it seems they have never had to work in a real enterprise environment. OData's design decisions are centered around simple systems doing simple communications. Mostly it assumes that the OData system is the source/owner of the data that it provides.

OData could do so much more, but alas, they have decided to limit it so that a few lower end developers don't have to make one data type decision.

[mikepizzo]

I truly appreciate this thread because, contrary to how it may appear, I agree that this is an issue and I don't yet understand how best to address it.

The OData TC made a very hard decision to remove DateTime when adopting OData as an ISO standard, and I've tried (as impartially as possible) to convey the thinking behind that decision.

While I believe in the reasons for making this decision, I also believe that we need to be able to support accessing existing systems, and I honestly want to understand these scenarios to see if there are options for supporting the type in the framework without generating payloads that violate the ISO Standard.

I provided the feedback link for the OData TC because that is where we need to go to make a change to the protocol. Separately, I am open to suggestions for supporting DateTime without violating the current protocol.

As an aside, note that OData V4 did add Date and TimeOfDay types to satisfy common scenarios where the timezone is not relevant.

[ImGonaRot]

@mikepizzo I original opened this issue back in 2014 on CodePlex when I had first moved from OData V3 into OData V4.
"https://archive.codeplex.com/?p=aspnetwebstack -- OData V4 service should support DateTime" Our company adopted the OData standard even back when it was just a WCF extension (AKA Data Services). Using proxy generators with WCF and importing those into both MS WinForms and MS WebForms was a breeze up until V4... In V4 we have been working with both Telerik and DevExpress on trying to get them to support DateTimeOffset for that past 4 years with little to no luck. I have several open tickets with both companies on DateTimeOffset. I personally think DateTimeOffset is a great way to move forward but like most developers, I am tied to the controls I use. We are currently getting by some of the issues when posting the data to the service by using the "SetTimeZoneInfo" in the WebApiConfig but that is only upstream. We also have several OData Functions and Actions that can do the Date conversions on the server side when talking to the controllers. For client side we have been able to get by most issues by using partial classes that are on top of the proxy generated classes and doing DateTime to DateTimeOffset conversions inside the getters / setters. But that only gets us so far down the road and does not work well when using LINQ to OData queries.

OData V4 Date is good if you don't need the time but we need the time and I have yet to find a good use for TimeOfDay.

I know that SAP has adopted some of the OData standard. I wonder what issues they are facing with DateTimeOffset if any at all?

[Vaccano]

@mikepizzo - I personally don't see any way to make it work in the current OData standard.

That is why I said the choice to abandon DateTime was shortsighted.

I thought long and hard back when you cut out DateTime on how I could find a way around the issue and still not have to lie to the consumers of my APIs.

As I said, I was a total OData fanboy back then and had planned to get my whole enterprise using OData for our SOA implementation. So the breaking change to make OData incompatible with our enterprise was a VERY hard pill for me to swallow. I really tried HARD to find a way around the issue. (Creative uses of Date and TimeOfDay, documentation to tell everyone to ignore the timezone, forking the project and trying to maintain my own version, using strings, etc)

But every solution was just too much work to add to the "cost" of OData when trying to convince my management that OData was the product we should use for our REST services.

I firmly believe the only way to fix this is to readdress this with the standards team and get DateTime added back in.

I think OData will find a significant upswing in adoption if it decides to support DateTime. As I said before, pretty much every programming language and database in existence supports DateTime. By looking at compatibility a bit more, I think OData has the potential to become the defacto REST standard. (And would have been already, but for the missteps with V4.)

[TehWardy]

@mikepizzo I agree with the others here ... Whilst having a standard is great this particular standard (the OData one) is short sighted and doesn't resolve the fact that the real world isn't perfect and often API providers don't control both ends.

I urge you to consider the following scenarios ...

1. The unknown assumption A remote system sending a "date" that the current implementation can parse in to a DateTimeOffset object. due to the way in which data is transferred to the OData service (as JSON) there's no way to tell the source data type or timezone we simply have to assume and build potentially incorrect precision information to make this work.

   2. The precision enforcement issue An API being given data without precision should never imply it or force it to be there, doing so inherently makes the data received incorrect (in law). In my situation I'm building out cloud based API's that receive invoice data ... what will i ever do if there's a legal battle about a set of invoice data where the data sent does not match the data in the db (which is what happens due to point 1 unless I accept the post as a raw string then parse away from OData).

   3. UI frameworks: binding issues I like many others use frameworks like Telerik's jquery based control library and find myself constantly having to "map" or "parse" or "side step" or "assume" some conversion issue about the data because of the problems introduced from this situation. Not having to fight that would take literally months off of the hours i've already put in to UI work on dates, times, and points in time

   4. UI frameworks: querying issues A date cannot be asked a time related question, doing so makes no sense. A time cannot be asked a date related question doing so makes no sense. So by logical deduction ... A point in time created from either has a set of questions that when asked make no sense. I always both send and receive strings (a fact of http comms and serialized messaging) so my metadata should be able to explain the subset of questions that make sense or how a DateTimeOffset should be correctly interpreted if no support for the correct data type is added.

A suggestion: I would start by allowing the model to contain all 3 of the types above and some guidance of how they can be used (bound to, transferred, correctly and accurately interpreted), and here's why ...

The fact is systems out there use both DateTime and DateTimeOffset, and this is a .Net implementation of a framework sat on top of a framework that supports both ... to remove either is bad on idea purely on that basis. Yes OData is a clearly well defined standard but the WebAPI implementation should also be willing to accept (like browsers do with the global standards for html) that it can vary from the standard for valid reasons.

I worded the end of point 3 very precisely that way to highlight a point that OData, .Net and WebAPI all have failed to address since their invention ... There is call for data types from the whole web stack to handle:

1. A date (nothing that I know of handles this)
2. A time (system.TimeSpan can handle this)
3. A precise point in time (System.DateTimeOffset handles this)

If a remote system in an unknown timezone sends me a serialised DateTime string because that's how it's defined in that system I need to be able to receive that AS IS ... by inferring any difference in the data I could be breaking the law since Invoices are a form of legal document and thus NOT open to interpretation.

[mikepizzo]

Thanks, everyone, for the feedback.

I brought this up at our weekly OData TC meeting today, and we discussed a few different mechanisms for allowing a service, particularly one that integrates with a legacy system, to convey the fact that a DateTime value has no offset information (without encouraging its use for new systems). Part of the challenge is introducing this without breaking existing OData V4 clients.

One option that we discussed is defining a core "DateTime" annotation that could be applied to a string to say "this string represents a DateTime value with no offset". We could take this one step further, and define a core TypeDefinition for DateTime whose underlying type is Edm.String, but has this annotation applied, as well as a validation that the syntax matches the ISO 8601 DateTime syntax.

This works well since any date/time types are conveyed as strings in JSON anyway.

In our stack, we would then support the CLR DateTime datatype, advertise it in $metadata either as a string with this annotation, or as the TypeDefinition. Existing clients would still be able to read this as a string, and clients that understood the annotation would be able to read the result and understand it as a DateTime value. In our client libraries we would add support for understanding this annotation when generating strongly typed classes and materialize the result using the DateTime datatype.

Given that we have such an engaged group of interested parties, I wanted to reach out to this group to see if that would address your real-world scenarios?

[Vaccano]

@mikepizzo - This is a very good idea. It will require clients to create custom code to properly understand it, but it is workable.

It keeps with the goal of OData to discourage the use of DateTime, while allowing a way forward for those of us who have to use it. (I still maintain that OData should not be in the business of choosing the good datatypes from the bad, but rather creating a protocol that can connect REST services to clients with as little friction as possible.)

But I think this is a great compromise that will get both groups what they are looking for!

I really appreciate you keeping on this issue and working to find a way for us to use the awesomeness that is OData.

I have one worry, will the underlying data type is String allow for date filtering operations? (I would guess not, as it would be just a string...)

[ImGonaRot]

@mikepizzo Assuming that they add back the "edm.DataTime" (was in OData V3) into the metadata generated from the odata service, this would be great.

This is also great since the models on the OData service don't have to change if the developer left them as DateTime properties. BUT it will cause issues since any dev using the DateTime property in their models currently get that property mapped to DateTimeOffset in the metadata... Maybe add an option that the dev can set in the WebApiConfig? config.EnableDateTime or something like that and then default to FALSE so it does not break all current implementations. Then this flag can be used to generate the metadata. If TRUE the metadate would have "edm.DateTimeOffset" else "edm.DateTime" any where the model had properties of "DateTime". If the model already is of type "DateTimeOffset" then ignore the flag. There is code already in OData classes that is turning DateTime properties into DateTimeOffset metadata so this should be easy to wrap with the "EnableDateTime" flag. Just a suggestion...

This would require little change at the client side for users currently using OData controls from Telerik or DevExpress with Winform and ASP.NET WebForm clients, since those controls still have lingering code from the V3 standard. Unfortunately, any client that generated the proxy from the OData Client code generator would have to update their generated file on the clients AND the OData Client code generator would need tested but I think it still has code for generating based on "Edm.DateTime".

Hate that I posted this issue back in 2014 and it is just now getting attention but some "love" is better than "no love". 👍

I have an environment that includes both OData V3 and OData V4 in production and would be happy to test any changes to the OData libraries.

[TehWardy]

@mikepizzo thanks for the persistence in this ... much appreciated.

consider this object definition ...

public class TestObject
{
    // a point in time (should be offset ... I agree with the standard here)
    public DateTimeOffset CreatedDate { get; set; }

    // represents an offset from a given date within a day to point to a time on a clock
    public TimeSpan TimeOfDay { get; set; }    

    // I see this all the time, i receive these and have to deal with them, 
    // I don't know the client offset, all i can do is store this as is I assume the "offset" is "whatever the source systems offset is" and have no choice but to store only what i have as UTC (best practice)
    public DateTime TransactionDate{ get; set; }

    // now here's what I think you are proposing for the above property situation
    [Date]
    public string TransactionDate { get; set; }
}

... that raises lots of questions for me ... If DateTime is hated so much why not remove the obscurity altogether and do this ...

public class TestObject2 
{
       // hold only year month day and supports via OData "date operations"
       // currently doesn't exist, could be defined in OData framework and mappable to either DateTime / DateTimeOffset using a rule defined by the developer
       public Date ADate { get; set; }  

       // holds only time of day (or an amount of time)
       public TimeSpan TimeAmount { get; set; }

      // holds the time zone information
      public TimeSpan Offset { get; set; }
}

In short ... I think the best solution is to allow for WebAPI OData to "configure the assumption to be made" for a DateTime value, but "prefer" using the latter property types in TestObject2 with a means to take an ISO standard string for a Date only or Time only as needed.

I think the latter should be written in as "the standard" and the former with the assumption "supported" for legacy reasons but documented as "deprecated" to encourage developers to more to the new standard but at least give them the option where it's not possible.

[ImGonaRot]

@TehWardy Unfortunately TestObject2 does not work well with grid controls and filtering. On the client side, devs would have to "merge" the two columns "ADate + TimeAmount" into one column (assuming they want one column containing date + time) and then intercept the filter events on the grid controls to query the OData service based on the data in the "merged" column. It gets even more complicated when using "strings" as the datetime because this "01/01/2018 01:04" is not the same as "1/1/2018 1:04" and using compares like > or < trip this up unless the dev adds string to date formatters on the grid controls but then again we are back to intercepting filter events on the grids.

This would make the binding of grid controls more complicated than they should be. OData V3 and grid binding in both DevExpress and Telerik just work out of box.

[TehWardy]

@ImGonaRot I disagree ... if you are combining the two fields then either use DateTime (which lacks precision and therefore results in assumptions ... or use DateTimeOffset which is the correct way to determine a point on calendar which is the binding scenario you are describing and works perfectly fine today (i have examples of exactly this scenario working perfectly with Kendo UI in business applications already).

Collection of new data should only be done to the latest standard and if you refuse to follow the standards for new data collection scenarios I think it's fair to any framework to fight you with that.

For old data scenarios / scenarios where you don't control the other end though I believe what I have suggested works fine as I still allow for supporting DateTime or a means to "map" a single string ina DTO on the endpoint to multiple fields or a DateTimeOffset as needed even without DateTime support but I have clearly stated that I as API builder would allow the receiving of DateTime values even if it wasn't part of the OData standard to allow for businesses that refuse to join the latest standards or scenarios where doing so is reasonably possible.

A note worth adding here ... Any half decent server stack for OData would allow for OData endpoints that map to entities (which OData already supports) ... so even if you choose to store a DateTime value in your database there's no reason why you can't receive it as TestObject2 then map anyway.

In a situation where you can't reasonably do that I would question the value of using OData at all as you likely want to do that how I handle data imports for systems like SAP (which seems to refuse to adhere to any form standards and even goes as far as making up its own standards for XML which make no logical sense) ... which is to receive a raw blob of XML as a string and then parse it and map to an entity behind the API layer.

I think to be fair to @mikepizzo and the OData team here, having a goal that pushes people forward instead of holding people back or encouraging that is a good call as long as allowances are made that mean we can handle the odd scenario.

One possibility is to look at attribute based mapping in the object sent to the API something like this ...

public class FooController : ODataController<Foo>
{
       static Func<Foo> customParser = (request) => { return new Foo(request.Content.ReadAsString()); }

       [DeserializeWith(c => customParser)]
       public IHttpActionResult Post([FromBody]T aFoo)
       {
             ....
       }
}

something like this reduces the complexity of building out "custom serializers and deserializers" but also allows for virtually any data to be received as any kind of object.

A similar logic pattern could also be applied to get requests too. I currently feel that mapping to and from a request stream is way too complex and part of the reason for that may be something to do with the fact that OData is not just about API's that receive data in 1 format.

I challenge you @ImGonaRot to find a scenario that couldn't be handled by this post and my previous combined, or some variation of.

[ImGonaRot]

@TehWardy Like @robertmclaws, you are assuming devs are using HTML5 controls like Kendo with JavaScript libraries like Moment.js. I am a dev that lives in the "past" with WinForms, WPF, and ASP.NET WebForms. I currently still develop and maintain apps in this "legacy" world and I'm 99.9% sure I will remain in that "legacy" world for 10 more years. Anyway... As such, I still use MS Grids, Telerik Grids, and DevExpress Grids to help my development time on projects go faster and I can tell you that those companies don't support DateTimeOffset with "any" of their WinForm / WebForm controls out of box. It is sad that very large 3rd party companies like Telerik and DevExpress are not updating their controls to support DateTimeOffset but until they do, I am stuck with large modifications to my code base to "work around" their DateTimeOffset issues when using OData V4. I would love for the companies that employ me to move into the "new" world you speak of where everyone is using DateTimeOffset (which is a Microsoft standard) but unfortunately that will not happen any time soon.

My point is that OData V3 worked perfect with DateTime yet in OData V4 they removed it. I would like OData V4 and beyond to support BOTH DateTime and DateTimeOffset. I'm not sure why we can't have both. MS SQL supports both and so does the MS .NET Framework. I currently use REST OData V3 and some WCF as the business tiers without any problems but OData V4 does not work well for our companies as a REST web service without breaking the current web contracts for DateTime.

If MS does not want to add it back that is fine, our current OData V3 and WCF is working fine and will for many years to come.

I only opened the ticket to see if they could put it back.

[ImGonaRot]

I think if OData is not going to support "DateTime" then they should throw an exception when the dev tries to create a class with DateTime properties RATHER than automatically converting them into DateTimeOffset in the metadata and the serializer / deserializer.

So this should FAIL if the OData team does not want to support DateTime

public class TestObject
{
    public Datetime CreatedDate { get; set; }
}

This way any proxy generators would also fail and it would be clear to the service consumer that DateTime is not supported.

[TehWardy]

@ImGonaRot I use telerik controls both on the web and for desktop clients. I have code running in WPF and winforms apps ... the lack of support for DateTime is not an issue if you know what to do.

I have OData V4 working on top of an EF model that exposes a legacy database with DateTime values bound to UI in both desktop and web components.

At this point i'm actually confused as to what your problem really is? My issue boils down to implicit accuracy ...

Binding works fine since the API always serves up "strings" of serialized data anyway ... worst case you have a single block of custom serialization and a single block deserialization code to write and you're done.

Could you show us an example of something that won't work unless OData supports DateTime values?

[ImGonaRot]

@TehWardy I never said it doesn't work. I said it is more difficult than it was in OData V3. Yes I can bind grids in WinForms, WebForms, and WPF to OData V4 datasources BUT with OData V3 and WCF, I don't have to make any code changes on the client to get things like filtering, sorting, editing to work once they are bound to an OData V3 datasource. When binding to OData V4 I have to edit the filtering events, sorting events, and editors so they "understand" DateTimeOffset, else they will fail. Both Telerik and DevExpress Date editors bound inside a grid don't understand DateTimeOffset.

If the OData team decides to choose String for DateTime then there will be other issue such as querying. Since date string "01/01/2018 04:33" is not the same as date string "1/1/2018 4:33" and if you try do a "BETWEEN" you will find that the two will not filter correctly since they are of a different sort order in a string list.

Here is a link for DateTimeOffset with DevExpress WebForms Quote "Our ASPxDateEdit editor doesn't support the DateTimeOffset type out of the box. " https://github.com/DevExpress-Examples/aspxgridview-how-to-edit-a-datetimeoffset-column-t584661

And Telerik Quote "Adding support for DateTimeOffset is not currently planned" https://www.telerik.com/forums/datetimeoffset-column-filtering-and-sorting

Please let me know if you want source code and I can drum up a sample project. But to be clear, I am already using both V3 and V4 in production on both Win and Web forms and for V3 I made no client modifications but for V4 I have made lots of modifications and can post those here if needed.

[TehWardy]

Ah I c ... I use telerik stuff but only the JS based pieces with my web apps. Doing so means I can take the raw DateTimeOffset string and treat it like a DateTime leveraging the fact that the browser supports both types and treats them equally as a "new Date" when given a string representation in a known format.

In short ... your problem is caused by strong typing in .Net languages and your choice to use the server side frameworks.

I wrote a convention based JS wrapper around Kendo UI's datasource object that can construct a datasource for any Grid (or other control that hangs off an odata-v4 endpoint). The wrapper implements the parse function once and I call that wrapper all over my code so I had only one place to solve the problem.

Whilst agree it's a pain in the ass in some situations the code is actually dead simple if you model your framework around kendo right. To get it working though I had to extend OData too. I'll throw a gist together so that people like @mikepizzo can see too (if you guys think there's some value to it).

[chris-clark]

EF Core 2.1's new ValueConverter functionality offers a potential workaround for those looking to query on SQL datetime columns. The problem here is that regardless of the CLR type on the entity, OData converts date/time literals to the DateTimeOffset CLR type in all cases. This type then serializes to SQL with too much precision, resulting in a cast exception being returned from SQL Server (it simply has too many milliseconds).

We explored adding our own IUriLiteralParser to the Microsoft.OData.UriParser.CustomUriLiteralParsers class, but Microsoft.AspNet.OData.Query.Expressions.FilterBinder (and related helpers) are very strict about types (which is good, if only they were more extensible). We made it through two reflection hacks before deciding the only way to accomplish this is a fork, which would likely take at least a couple days to get to a "working for my use case" state. To be clear, we eventually want to convert everything to datetime2/datetimeoffset, but constraints imposed by legacy systems make the unfeasible presently.

EF Core 2.1 introduced the ValueConverter which, in addition to converting values in the entity to and from the persistence store, also inspects the LINQ abstract syntax tree and performs type conversions on constants/literals. So OData transforms the date/time literal in the query string "$filter=orderDate ge 2015-01-01T00:00:00.000Z" to a DateTimeOffset, and then our ValueConverter will transform it back to a DateTime.

You must use DateTimeOffset on your entity (your SQL datatype can still be datetime): public DateTimeOffset OrderDate { get; set; }

Create a ValueConverter: internal static ValueConverter<DateTimeOffset, DateTime> DateTimeToDateTimeOffset = new ValueConverter<DateTimeOffset, DateTime>(model => model.DateTime, store => DateTime.SpecifyKind(store, DateTimeKind.Local));

Map it: entityTypeBuilder.Property(x => x.Entered).HasConversion(ValueConverters.DateTimeToDateTimeOffset);

Caveats:

• You'll need to modify the ValueConverter to handle timezones in a way that is appropriate for your use case
• You CANNOT disable query parameterization as EF will no longer perform the type/value conversion. This is enabled by default and there aren't many reasons to turn it off. [EnableQuery(EnableConstantParameterization = false)] // This will break our hack!
• If you can use datetime2/datetimeoffset, do so and avoid this hack

From our exploration, we don't see any reason why the OData team couldn't add full support for DateTime. I realize this would be a non-standard feature outside of the OData V4 spec, but historically Microsoft hasn't shied away from adding their own extensions to public specifications :-). With so many projects working to modernize legacy systems using an agile, incremental approach, this is critical functionality, and the workaround above has limits..

[TehWardy]

@ImGonaRot code templating or generics might help you there ... fix it once, re-apply the same thing everywhere ... works fine for my js framework.

That said ... i'm used to working with telerik stuff which is basically a framework of such hacks ... after the first MB of crap work around code you give up trying to do things perfectly and just build hooks that let you override every last aspect of the poor underlying framework you're sat on ... in that respect the implementation we have of OData is much the same ... a hack here, a tweak there.

[TehWardy]

@chris-clark at one point i considered writing my own OData framework just using raw WebAPI and the ODataLib library to do the uri params to LINQ handling.

I actually genuinely don't understand why this is so complex as a problem until i notice that Microsoft wants you to pull in their full rest-tier and then it all clicks ... the problems we face just using the basics are because we choose not to take a dependency on 100 other things from nuget.

How dare we! It really bugs me when i can't just take what i need and get forced to take lots of other stuff.

Feels like MS is keen to become SUN and certain teams want to build C# libs like Java ... WHY!!!

[mikepizzo]

I just wanted to update the thread on the original issue:

We discussed this at length at our September Face-to-Face OASIS OData Technical Committee meeting, and ultimately agreed to add a formal "Core.LocalDateTime" typedefinition to the vocabulary. The semantics of this typedefinition is that it is a string that represents a date-time value with no offset, and the format of the string is enforced through a matches validation. In addition, we stated that the cast function must accept such a string (lacking an offset) to cast to a DateTimeOffset (as UTC) in order to support use of the values in datetime expressions.

See https://issues.oasis-open.org/browse/ODATA-1229 for details.

We are in the process of implementing support for this new TypeDefinition in ODataLibrary and WebAPI OData. While there is still a strong preference to represent datetime values with timezone where-ever possible, hopefully this will address some of the very valid concerns raised in this thread.

[ImGonaRot]

@mikepizzo This is great news but if it is a string, how will this convert back into SQL queries when using filters?

Here is what I posted a while back.

If the OData team decides to choose String for DateTime then there will be other issue such as querying. Since date string "01/01/2018 04:33" is not the same as date string "1/1/2018 4:33" and if you try do a "BETWEEN" you will find that the two will not filter correctly since they are of a different sort order in a string list.

I look forward to seeing / testing the implementation / source code. Thanks for looking into this issue.

[mikepizzo]

@ImGonaRot Still to be designed, but in WebAPI we would probably know that the underlying value was a CLR DateTime value, so we would build the correct LINQ expression to be passed down to the underlying provider. Very eager to get feedback on this feature...

[kbarkhausen]

I am updating a NET 4.7 project to latest OData libraries. I need to have Date value support on the OData queries within the $filter option (i.e. $filter=BirthDate eq 1900-01-01). I was able to provide this support by creating an action filter that converts my Date values into DateTimeOffset string values before the OData request is handled by the OData framework.

Here is a link to my solution https://github.com/kbarkhausen/OData.ActionFilter.AddDateTimeSupport

Welcome any comments, suggestions or ideas on how to improve this support. You can easily customize this filter to support any DateTime value (i.e. $filter=BirthDate eq '03/14/2018')

[habbes]

Closing this issue due to inactivity. If this still affects the latest versions of the library, kindly open a new issue.

[SomeoneIsWorking]

I'm suffering greatly from this because I want to use DateTime values as exactly the same as strings. No timezone conversions, no reading from another timezone or converting to another timezone. If it the DB says X then the page shows X and if I send Y then it's stored as Y. This is not up to me either, it's in the project's specifications designed by the director. The only reason I installed this library is to have query string filtering such as $filter=startsWith('Name', 'ti') Now I have to trim timezones and add them back again for no reason.