.net 如何让 Web API OData v4 使用 DateTime

Question

提问by Vaccano

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

我有一个相当大的数据模型，我想使用 OData V4 协议使用 Web API OData 公开它。

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

基础数据存储在 SQL Server 2012 数据库中。该数据库中有许多 DateTime 列。

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

当我连接它时，我收到一个错误，不支持 System.DateTime。

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

所以这是我的问题，我该怎么做才能在 OData 提要中看到我的 DateTime 列？

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

注意：我无法返回并将所有列更改为 DateTimeOffset 列。

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

我尝试在实体框架 edmx 中更改列的类型，但它给了我这个错误：

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

指定的成员映射无效。'MyProject.MyEntity' 类型中成员 'MyPropertyHere' 的类型 'Edm.DateTimeOffset[Nullable=False,DefaultValue=,Precision=]' 与 'SqlServer.datetime[Nullable=False,DefaultValue=,Precision=3] 不兼容' 类型为 'MyDataModel.Store.MyEntity' 的成员 'MyColumnName'。

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

（基本上认为 DateTime 与 DateTimeOffset 不兼容。）

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

Web API OData 团队真的只是忽略了需要使用 SQL Server 类型的每个人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.

更新：我已经找到了解决方法，但他们需要更新 EF 模型才能工作。如果可以避免的话，我宁愿不必单独更新数百个属性。

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. Transactionsand ordering of insertsbeing 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.)

更新：这个问题让我意识到微软管理其 OData 产品的方式存在严重缺陷。问题很多，但最突出的就是这个。Web API OData 中有大量缺失的功能。事务和插入排序是其中的两个。这两项（在 OData 规范中并且在 Microsoft 杀死它之前在 WCF 数据服务中）对于任何真实系统都至关重要。
但是，与其将时间花在那些缺少 OData 规范中的功能的关键点上，他们决定将时间花在删除对许多开发人员非常有帮助的功能上。它体现了糟糕的管理，优先考虑删除工作功能而不是添加急需的功能。
我尝试与 Web API OData 代表讨论这些问题，最后，我打开了一个问题/工单，几天后又将其关闭。那是他们愿意做的事情的结束。
正如我所说，Web API OData 的管理还有很多问题（与 DateTime 无关，因此我不会在此列出）。 我一直是 OData 的坚定支持者，但 Web API OData 管理方面的明显问题迫使我和我的团队/公司放弃它。
幸运的是，普通的 Web API 可以设置为使用 OData 语法。设置控制器需要更多的工作，但最终效果很好。它支持日期时间。（而且似乎管理层至少可以避免做出疯狂的错误决定。）

Answer 1

采纳答案by Sam Xu

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

到目前为止，DateTime 不是OASIS OData V4 标准的一部分，Web API 不支持 DateTime 类型，但它支持 DateTimeOffset 类型。

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.

但是，OData 团队现在正致力于支持 DataTime 类型。我希望您可以在下一个 Web API 版本中使用 DateTime 类型。如果你等不及下一个版本，我根据博客写了一个例子。希望它可以帮助你。谢谢。

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.

正常添加 OData 控制器。

Test

测试

Customer Data in the DB

数据库中的客户数据

Payload

有效载荷

The customers payload

客户有效载荷

Answer 2

回答by Iman Mahmoudinasab

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

最后，Web API OData v4 现在支持DateTime版本 5.5 中的类型。获取最新的 nuget 包，不要忘记设置：

config.SetTimeZoneInfo(TimeZoneInfo.Utc);

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

否则 dateTime 属性的时区将被视为本地时区。

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

更多信息： ASP.NET Web API for OData V4 Docs DateTime support

Answer 3

回答by crimbo

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

另一种解决方案是修补项目，以便DateTime再次允许 - 这就是我所做的，如果需要，您可以在此处获取代码：

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:

我还推送了一个 NuGet 包以使其易于使用，您可以使用此处的信息获取该包：

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.

...我不知道我是否可以发布包含修补过的 Microsoft 库的 NuGet 包，我希望请求宽恕比许可更容易。

Note that the work item to officially restore the ability to use DateTime in OData 4 is tracked here: https://aspnetwebstack.codeplex.com/workitem/2072Please 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.

请注意，在此处跟踪正式恢复在 OData 4 中使用 DateTime 的能力的工作项：https://aspnetwebstack.codeplex.com/workitem/2072 如果您愿意，请为该问题投票；虽然它看起来像是计划在 5.4-beta 上发布，所以它应该会在这几天到来。

Answer 4

回答by Jon Alberghini

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.

这种变通方法和来自http://damienbod.wordpress.com/2014/06/16/web-api-and-odata-v4-crud-and-actions-part-3/ 的变通方法都不起作用。它们仅以一种方式工作，这意味着使用 filter 命令查询 odata datetimeoffset 失败，因为它不是模型的一部分。

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.

Reminder 和 Iscomplete 是从活动到 AppUserActivities 的日期和日期时间。

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

这很奇怪。有没有人有办法解决吗？

I connect to MySQL. There is not a datetimeoffset.

我连接到 MySQL。没有日期时间偏移。

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

每个人都想知道为什么没有人愿意为 Microsoft 技术进行开发。

Answer 5

回答by Konstantin Ryazantsev

Unfortunatly, https://aspnetwebstack.codeplex.com/SourceControl/network/forks/johncrim/datetimefixesfork, given by crimbodoesn`t realy support DateTime.

不幸的是，crimbo给出的https://aspnetwebstack.codeplex.com/SourceControl/network/forks/johncrim/datetimefixesfork并不真正支持 DateTime。

There is the new fork https://aspnetwebstack.codeplex.com/SourceControl/network/forks/kj/odata53datetime?branch=odata-v5.3-rtmbased on OData v5.3 RTM, where DateTime properties in entities and complex types are supported in server answers, client POST/PUT/PATCH requests, $orderby and $filters clauses, function parameters and returns. We start to use it in production code and will support this fork, until DateTime support will return in future official releases.

还有就是新的分支https://aspnetwebstack.codeplex.com/SourceControl/network/forks/ KJ/odata53datetime?branch=odata-v5.3-rtm基于OData的V5.3 RTM，凡在实体和复杂类型的DateTime属性在服务器应答、客户端 POST/PUT/PATCH 请求、$orderby 和 $filters 子句、函数参数和返回中支持。我们开始在生产代码中使用它并将支持这个分支，直到 DateTime 支持将在未来的正式版本中返回。

Answer 6

回答by NicoJuicy

Since i use a library for odata with angular, i investigated it there:

由于我使用带有 angular 的 odata 库，因此我在那里对其进行了调查：

https://github.com/devnixs/ODataAngularResources/blob/master/src/odatavalue.js

There you can see ( javascript)

在那里你可以看到（javascript）

var generateDate = function(date,isOdataV4){
        if(!isOdataV4){
            return "datetime'" + date.getFullYear() + "-" + ("0" + (date.getMonth() + 1)).slice(-2) + "-" + ("0" + date.getDate()).slice(-2) + "T" + ("0" + date.getHours()).slice(-2) + ":" + ("0" + date.getMinutes()).slice(-2)+':'+("0" + date.getSeconds()).slice(-2) + "'";
        }else{
            return date.getFullYear() + "-" + ("0" + (date.getMonth() + 1)).slice(-2) + "-" + ("0" + date.getDate()).slice(-2) + "T" + ("0" + date.getHours()).slice(-2) + ":" + ("0" + date.getMinutes()).slice(-2)+':'+("0" + date.getSeconds()).slice(-2) + "Z";
        }
    };

And the test expects ( cfr. here)

并且测试预期（参见此处）

 $httpBackend.expectGET("/user(1)?$filter=date eq 2015-07-28T10:23:00Z")

So this should be your "formatting"

所以这应该是你的“格式化”

2015-07-28T10:23:00Z

Answer 7

回答by Steve Pettifer

Having spent a frustrating day trying to do this very thing, I have just stumbled across the only way I could get it to work. We wanted to be able to filter by date ranges using OData and Web API 2.2, which isn't an uncommon use case. Our data is in SQL Server and is stored as DateTime and we're using EF in the API.

花了令人沮丧的一天试图做这件事，我刚刚偶然发现了我可以让它工作的唯一方法。我们希望能够使用 OData 和 Web API 2.2 按日期范围进行过滤，这并不是一个不常见的用例。我们的数据在 SQL Server 中并存储为 DateTime，我们在 API 中使用 EF。

Versions:

版本：

Microsoft.AspNet.WebApi.OData 5.7.0
Microsoft.AspNet.Odata 5.9.0
Microsoft.OData.Core 6.15.0
Microsoft.OData.Edm 6.15.0
Microsoft.Data.OData 5.7.0

微软.AspNet.WebApi.OData 5.7.0
微软.AspNet.Odata 5.9.0
Microsoft.OData.Core 6.15.0
Microsoft.OData.Edm 6.15.0
微软.Data.OData 5.7.0

Entity Snippet:

实体片段：

[Table("MyTable")]
public class CatalogueEntry
{
    [Key]
    public Guid ContentId { get; set; }
    [StringLength(15)]
    public string ProductName { get; set; }
    public int EditionNumber { get; set; }
    public string Purpose { get; set; }
    public DateTime EditionDate { get; set; }
}

WebApiConfig

网络接口配置

public static class WebApiConfig
{
    public static void Register(HttpConfiguration config)
    {
        config.Routes.MapODataServiceRoute("ProductCatalogue", "odata", GetImplicitEdm());

        // Web API routes
        config.MapHttpAttributeRoutes();

        config.Filters.Add(new UnhandledExceptionFilter());

        var includeVersionHeaders = ConfigurationManager.AppSettings["IncludeVersionHeaders"];
        if (includeVersionHeaders != null && bool.Parse(includeVersionHeaders))
        {
            config.Filters.Add(new BuildVersionHeadersFilter());
        }

        config.SetTimeZoneInfo(TimeZoneInfo.Utc);
    }

    private static IEdmModel GetImplicitEdm()
    {
        ODataModelBuilder builder = new ODataConventionModelBuilder();
        builder.EntitySet<CatalogueEntry>("ProductCatalogue");
        return builder.GetEdmModel();
    }
}

Controller Snippet:

控制器片段：

public class ProductCatalogueController : EntitySetController<CatalogueEntry, string>
{
    [EnableQuery]
    public override IQueryable<CatalogueEntry> Get()
    {
        return _productCatalogueManager.GetCatalogue().AsQueryable();
    }

    protected override CatalogueEntry GetEntityByKey(string key)
    {
        return _productCatalogueManager.GetCatalogue().FirstOrDefault(c => c.ContentId.ToString() == key);
    }
}

Here's the magic bit - see the bottom of this MSDN pageunder the heading 'Referencing Different Data Types in Filter Expressions' and you will find a note saying:

这是神奇的地方 - 请参阅此 MSDN 页面底部“在过滤器表达式中引用不同数据类型”标题下的内容，您会发现一条说明：

DateTime values must be delimited by single quotation marks and preceded by the word datetime, such as datetime'2010-01-25T02:13:40.1374695Z'.

DateTime 值必须用单引号分隔并以单词datetime开头，例如 datetime'2010-01-25T02:13:40.1374695Z'。

http://localhost/Product-Catalogue/odata/ProductCatalogue?$filter=EditionDate lt datetime'2014-05-15T00:00:00.00Z'

So far we have this working in Postman and we're now building the client which should hopefully work with this requirement to stick 'datetime' in front of the actual value. I'm looking at using Simple.OData.Client in an MVC controller but we may even decide to call straight to the API from client side JavaScript depending on how much refactoring we have to do. I'd also like to get Swagger UI working using Swashbuckle.OData but this is also proving to be tricky. Once I've done as much as I have time to, I'll post an update with useful info for those who follow as I found it very frustrating that it was so hard to find out how to do something that is ostensibly a simple requirement.

到目前为止，我们已经在 Postman 中进行了这项工作，我们现在正在构建客户端，希望它能够满足此要求，将“日期时间”放在实际值之前。我正在考虑在 MVC 控制器中使用 Simple.OData.Client 但我们甚至可能决定从客户端 JavaScript 直接调用 API，这取决于我们必须进行多少重构。我还想使用 Swashbuckle.OData 使 Swagger UI 工作，但这也被证明很棘手。一旦我尽可能多地完成了我有时间做的事情，我会为那些关注的人发布一个包含有用信息的更新，因为我发现这非常令人沮丧，以至于很难找到如何做一些表面上很简单的要求.

Answer 8

回答by Sundara Prabu

Both the following work with ODATA 4

以下两个都适用于 ODATA 4

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)
this also works , it needs to be in this yyyy-mm-ddThh:mm:ss.ssZ
$filter=DateofTravel lt 2018-02-02T00:00:00.00Z

这是我在 .Net 4.7 和Microsoft.Aspnet.ODATA 5.3.1 上看到的最新更新
$filter=DateofTravel lt cast(2018-05-15T00:00:00.00Z,Edm.DateTimeOffset)
这也有效，它需要在这个 yyyy-mm-ddThh:mm:ss.ssZ
$filter=DateofTravel lt 2018-02-02T00:00:00.00Z

Answer 9

回答by user1931270

For those who are using older versions of OData without Datetime support, you can simply use the .AddQueryOption extension to include the datetime filters manually...

对于那些使用没有日期时间支持的旧版 OData 的人，您可以简单地使用 .AddQueryOption 扩展来手动包含日期时间过滤器...

Example - If you had a query defined as follows:

示例 - 如果您有一个如下定义的查询：

var date1 = new DateTime();
var query = from a in service.Entity
            where a.DateField = date1
            select a;

This would not give you the result you expect b/c the translated query would effectively be something like https://test.com/Entity?$filter=DateField eq 2020-02-24T00:00:00Z. When it gets to the server this wont execute the expected query b/c of the datetime offset.

这不会给您预期的结果 b/c 翻译后的查询实际上类似于https://test.com/Entity?$filter=DateField eq 2020-02-24T00:00:00Z。当它到达服务器时，它不会执行预期的日期时间偏移量查询 b/c。

to get around this do the following:

要解决此问题，请执行以下操作：

var date1 = new DateTime().ToString("yyyy-MM-dd");
var filters = "date(DateField) eq " + date1;
var query = from a in service.Entity.AddQueryOption("$filter", filters);

This will allow you to query against odata using datetime. Now, all you need to do is handle the POST, PUT, DELETE commands.

这将允许您使用日期时间查询 odata。现在，您需要做的就是处理 POST、PUT、DELETE 命令。

To do this, simply ensure that you have the timezone information or the client offset serialized in the header of the request. Use this in the web api to adjust the dates accordingly. I generally will add an extension method that is used in a custom serializer to adjust the dates when the payload is deserialized.

为此，只需确保您在请求的标头中序列化了时区信息或客户端偏移量。在 web api 中使用它来相应地调整日期。我通常会添加一个在自定义序列化程序中使用的扩展方法来调整有效负载反序列化时的日期。

Alos, be sure to write such that the newer version of web api that handle timezone information properly will continue to function as expected...

Alos，一定要这样写，以便正确处理时区信息的较新版本的 web api 将继续按预期运行......

Answer 10

回答by Craig Boland

Looks like a mapping DateTimeOffset<-> DateTimewill be included in Microsoft ASP.NET Web API 2.2 for OData v4.0 5.4.0:

看起来映射DateTimeOffset<-> DateTime将包含在Microsoft ASP.NET Web API 2.2 for OData v4.0 5.4.0 中：

https://github.com/OData/WebApi/commit/2717aec772fa2f69a2011e841ffdd385823ae822

.net 如何让 Web API OData v4 使用 DateTime

提问by Vaccano

采纳答案by Sam Xu

回答by Iman Mahmoudinasab

回答by crimbo

回答by Jon Alberghini

回答by Konstantin Ryazantsev

回答by NicoJuicy

回答by Steve Pettifer

回答by Sundara Prabu

回答by user1931270

回答by Craig Boland

相关推荐

最近更新

标签

.net 如何让 Web API OData v4 使用 DateTime

提问by Vaccano

采纳答案by Sam Xu

回答by Iman Mahmoudinasab

回答by crimbo

回答by Jon Alberghini

回答by Konstantin Ryazantsev

回答by NicoJuicy

回答by Steve Pettifer

回答by Sundara Prabu

回答by user1931270

回答by Craig Boland

相关推荐

.net 无法解析远程名称

VB.Net 'Sub Main' 未找到

.NET 中的反射有什么用？

适用于 .NET 3.5（或 4.0）的优秀 XMPP/Jabber 客户端库

相关推荐

最近更新

标签