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.)

Answer 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