How to get Web API OData v4 to use DateTime

Question

I have a fairly large data model that I want to expose using Web API OData using the OData V4 protocol.

The underlying data is stored in a SQL Server 2012 database. That database has many DateTime columns in it.

As I was wiring it up I got an error that System.DateTime is not supported.

So here is my question, what can I do to get my DateTime columns to be seen in the OData feed?

NOTE: I am not able to go back and change all my columns to DateTimeOffset columns.

I tried changing the type of the column in the Entity Framework edmx, but it gave me this error:

Member Mapping specified is not valid. The type 'Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]' of member 'MyPropertyHere' in type 'MyProject.MyEntity' is not compatible with 'SqlServer.datetime[Nullable=False,DefaultValue=,Precision=3]' of member 'MyColumnName' in type 'MyDataModel.Store.MyEntity'.

(Bascially syas that DateTime is not compatable with DateTimeOffset.)

Did the Web API OData team really just leave out everyone who needs to use the SQL Server type of DateTime?

Update: I have found workarounds for this, but they require updating the EF Model for them to work. I would rather not have to update several hundred properties individually if I can avoid it.

Update: This issue has made me realize that there are deep flaws in how Microsoft is managing its OData products. There are many issues, but this one is the most glaring. There are huge missing features in the Web API OData. Transactions and ordering of inserts being two of them. These two items (that are in the OData spec and were in WCF Data Services before Microsoft killed it) are critical to any real system.

But rather than put time into those critical spots where they are missing functionality that is in the OData Specification, they decide to spend their time on removing functionality that was very helpful to many developers. It epitomizes bad management to prioritize the removal of working features over adding in badly needed features.

I tried discussing these with the Web API OData representative, and in the end, I got a issue/ticket opened that was then closed a few days later. That was the end of what they were willing to do.

As I said, there are many more issues (not related to DateTime, so I will not list them here) with the management of Web API OData. I have been a very strong supporter of OData, but the glaring issues with Web API OData's management have forced me and my team/company to abandon it.

Fortunately, normal Web API can be setup to use OData syntax. It is more work to setup your controllers, but it works just fine in the end. And it supports DateTime. (And seems to have management that can at least stay away from making insanely bad decisions.)

Solution 1

So far, DateTime is not the part of the OASIS OData V4 standard and Web API doesn't support the DateTime type while it do support the DateTimeOffset type.

However, OData Team are working on supporting the DataTime type now. I'd expect you can use the DateTime type in the next Web API release. If you can't wait for the next release, I wrote an example based on the blog . Hope it can help you. Thanks.

Model

public class Customer
{
    private DateTimeWrapper dtw;

    public int Id { get; set; }

    public string Name { get; set; }

    public DateTime Birthday
    {
        get { return dtw; }
        set { dtw = value; }
    }

    [NotMapped]
    public DateTimeOffset BirthdayOffset
    {
        get { return dtw; }
        set { dtw = value; }
    }
}

public class DateTimeWrapper
{
    public static implicit operator DateTimeOffset(DateTimeWrapper p)
    {
        return DateTime.SpecifyKind(p._dt, DateTimeKind.Utc);
    }

    public static implicit operator DateTimeWrapper(DateTimeOffset dto)
    {
        return new DateTimeWrapper(dto.DateTime);
    }

    public static implicit operator DateTime(DateTimeWrapper dtr)
    {
        return dtr._dt;
    }

    public static implicit operator DateTimeWrapper(DateTime dt)
    {
        return new DateTimeWrapper(dt);
    }

    protected DateTimeWrapper(DateTime dt)
    {
        _dt = dt;
    }

    private readonly DateTime _dt;
}

DB Context

public DbSet<Customer> Customers { get; set; }

EdmModel

ODataConventionModelBuilder builder = new ODataConventionModelBuilder();

builder.EntitySet<Customer>("Customers");

var cu = builder.StructuralTypes.First(t => t.ClrType == typeof(Customer));
cu.AddProperty(typeof(Customer).GetProperty("BirthdayOffset"));
var customer = builder.EntityType<Customer>();

customer.Ignore(t => t.Birthday);
var model = builder.GetEdmModel();

config.MapODataServiceRoute("odata", "odata", model);

Controller

Add the OData Controller as normal.

Test

Payload

Solution 2

Finally Web API OData v4 now supports DateTime type in release 5.5 . Get latest nuget package and don't forget setting this:

config.SetTimeZoneInfo(TimeZoneInfo.Utc);

otherwise the timezone of the dateTime property would be considered as local timezone.

More info: ASP.NET Web API for OData V4 Docs DateTime support

Solution 3

An alternate solution is to patch the project so that DateTime is allowed again - that's what I did, you can get the code here if you want it:

https://aspnetwebstack.codeplex.com/SourceControl/network/forks/johncrim/datetimefixes

I also pushed a NuGet package to make it easy to use, you can obtain the package using the info here:

http://www.nuget.org/packages/Patches.System.Web.OData/5.3.0-datetimefixes

... I don't know if it's ok for me to post a NuGet package containing a patched Microsoft library, I hope it's easier to ask forgiveness than permission.

Note that the work item to officially restore the ability to use DateTime in OData 4 is tracked here: https://aspnetwebstack.codeplex.com/workitem/2072 Please vote for the issue if you'd like to see it; though it looks like it's scheduled for 5.4-beta, so it should be coming one of these days.

Solution 4

Both the following work with ODATA 4

1 : This is the Latest update I see with .Net 4.7 and Microsoft.Aspnet.ODATA 5.3.1

$filter=DateofTravel lt cast(2018-05-15T00:00:00.00Z,Edm.DateTimeOffset)

2 : This also works , it needs to be in this yyyy-mm-ddThh:mm:ss.ssZ

$filter=DateofTravel lt 2018-02-02T00:00:00.00Z

Solution 5

This workarounds and the one from http://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/, neither work. They work one way only, meaning that quering odata datetimeoffset with the filter command fails because it is not part of the model.

You can no longer filter or sort by these fields or get this error

/Aas/Activities?$top=11&$orderby=CreatedAt

Gives this error:

"code":"","message":"An error has occurred.","innererror":{
  "message":"The 'ObjectContent`1' type failed to serialize the response body for content type 'application/json; odata.metadata=minimal'.","type":"System.InvalidOperationException","stacktrace":"","internalexception":{
    "message":"The specified type member 'CreatedAt' is not supported in LINQ to Entities. Only initializers, entity members, and entity navigation properties are supported.","type":"System.NotSupportedException","stacktrace":"   

But this works as it is addressed indirectly:

/Aas/AppUsers%28102%29/AppUserActivities?$expand=Case&$filter=%28Reminder%20ne%20null%20and%20IsComplete%20eq%20null%29&$top=15&$orderby=Reminder&$count=true

Reminder and Iscomplete are date and datetiem from activity through AppUserActivities.

This is wierd that that works. Does anyone have a solution?

I connect to MySQL. There is not a datetimeoffset.

And everyone wonders why no one wants to develop for Microsoft technologies.

View more solutions

Comments

[iuristona]

When I try to use the 5.3.0 I get an error: ValueFactory at GlobalConfiguration.Configure(WebApiConfig.Register);

[crimbo]

Hmmm - all the unit tests passed. I'll look into this later today.

[iuristona]

In fact the full error message is: ValueFactory attempted to access the Value property of this instance soon as I try to use the 5.3.0 pre for Microsoft.AspNet.Odata wich is pre-requesite for the patch.

[iuristona]

So, I figured out the issue: config.MapHttpAttributeRoutes(); this is not working with the 5.3.0 version. But, now I am running the OData 4.0 with DateTime, but when I try to post a json like this: { name: 'iPhone 6', releaseDate: '2014-08-08' } is not working anymore. I replaced the DateTimeOffSet to DateTime in that ReleaseDate field (it works with DateTimeOffSet). So the I get a null for the Delta<Product> in my post/put/merge/patch methods.

[Kittoes0124]

Would allowing the usage of the tag [Column(TypeName = "date")] on DateTimeOffset properties be a simple solution to this?

[David McClelland]

Are you sure the OData Team is working on supporting the DataTime type now? Currently this issue (ODATA-220) is listed as "Unresolved" for OData v5: issues.oasis-open.org/browse/ODATA/fixforversion/10277/…

[Ali Mst]

I get "The type or namespace name 'DateTimeWrapper' could not be found"

[Sam Xu]

Sorry, I mean OData Team is working on supporting the DateTime type in Web API for OData.

[Adi Inbar]

It's not clear what you're saying. Is this even an answer, or a question? And what do you mean by "As to what I wrote earlier"? Wrote earlier where? I deleted the link that immediately followed that because it was a link to this page.

[Jon Alberghini]

What I am trying to say is this is not answered. Odata will run with this solution, those fields are useless for sorting or filtering because odata on filtering and sorting hands those fields off to EF and EF says wow this field is not part of the model. What good is a datetime work around that you cannot filter on?

[Jerther]

Same here. With the NuGet, even though the "unsupported" exception went away, the serialized date is an empty dictionary. The new fork works well. Just compile the solution in OData and use the System.Web.OData.dll file or the reference the project.

[Ali Mst]

Yep. But it does not support DateTime. Just supports converting from DateTime to DatetimeOffset. Better than nothing. But still does not allow migrating from WCF DataServices to Web API OData.

[Ali Mst]

Their "support" amounted to converting DateTimes into DateTimeOffsets. And it does not work if you need to filter on the date.

[Ali Mst]

This "support" just converts the DateTime into a DateTimeOffset. (So you still have to update any existing clients.) Also, last I read you still cannot filter on the date time. (A very common thing to do with dates.)

[Iman Mahmoudinasab]

@Vaccano 1.I have no idea what do you mean by "you still have to update any existing clients." ? 2. Web API OData v4 now supports DateTime type means you can filter on the DateTimes as I done in my project. As you mentioned it convert the DateTime to DateTimeOffset so it allows us to filter a DateTime.

[Ali Mst]

If you had a client application that was using DateTime (or several as my case is), you now have to update each of them to all use DateTimeOffset where they had DateTime. And there is a bug filtering on a DateTime that was converted to a DateTimeOffset in the current version (They said it would be fixed in 5.5, but then they pushed it to the still pending 5.6)

[Maximilian Wilson]

As of 5.9 v4 now supports DateTime as an actual Date ("5/9/2016") instead of a DateTimeOffset.

[Iman Mahmoudinasab]

@MaximilianWilson It is not mentioned in release note neither documents. Would you please provide any link?

[Vector]

Yes! I spent a couple hours trying to understand why ODataActionParameters was throwing a serialization error. It wouldn't recognize my DateTime

[goroth]

Why would anyone say it supports "DateTime"? OData V4 supports Date and DateTimeOffset but not edm:DateTime. If you look at the metadata from the OData service, you will not find DateTime.

[War]

now try grouping on that or replace the fixed value with a field on an entity in your OData model of Type DateTime then group on it

[War]

that's because this answer is a partial copy of an answer from above

[Glass Cannon]

It will throw an exception if you send a DateTime value without timezone to any OData method, PATCH odata/Game {"GameDateTime": "2022-01-01T12:14:16"} this will throw couldn't convert string literal to Edm.DateTimeOffset. Saying it supports DateTime is straight up misdirecting people.