从数据库加载后，DateTime.Kind设置为未指定，而不是UTC [英] DateTime.Kind set to unspecified, not UTC, upon loading from database

问题描述

当我创建 Buyin 对象时，来自ASP.NET MVC控制器的响应，（返回Json（response，JsonRequestBehavior.AllowGet）; ，看起来像这样：

 购买：{
 Id：95， 
 PlayerSessionId：88，
 PlayerId：45，
 PlayerName： Alan，
 Amount：888，
 BuyinType： 贷方，
描述：null，
授权：true，
 SignPath：〜/ Signs / Buyins\\95.png，
付款：null，
 CreationDate： / Date（1477242738042）/ 
}，
  

如果我在

但是种类设置为未指定，所以我认为这是引起问题的原因。

目前我还没有设置任何全球化设置。

所以基本上我的问题是：当ASP.NET MVC加载时数据库中的日期是否有一种方法可以告诉服务器，在我将种类设置为 UTC 的情况下加载日期

使用实体框架保存和加载数据库。

更新接受答案之后

接受的答案很好，但是我的日期值已经作为UTC日期存储在数据库中，所以我修改了 GetDateTime 到此：

 公共替代DateTime GetDateTime（int ordinal）
 {
 var date = base.GetDateTime（ordinal）; 
 var utcDate = DateTime.SpecifyKind（date，DateTimeKind.Utc）; 
返还utcDate; 
 //返回base.GetDateTime（ordinal）.ToUniversalTime（）; 
} 
  

解决方案

假设您正在使用EF6和您要将从数据库中检索到的任何 DateTime 值的种类属性设置为 Utc 。

有人问过类似的问题，答案往往暗示着要加入 ObjectContext.ObjectMaterialized 事件，但对于使用投影的查询并不会触发。

我要提出的解决方案适用于实体查询和投影查询，方法是执行 DbDataReader 级别的转换（此类型的查询使用）。

为此，我们需要一个自定义的 DbDataReader 实现，该实现可拦截 GetDateTime 方法。不幸的是，实现 DbDataReader 派生类需要大量样板代码。幸运的是，我已经从 Dynamic的答案中创建了基类。进行翻译以避免C＃语法错误，该错误只是将每个方法委派给基础 DbDataReader 实例，因此我将从那里获取它：

 抽象类DelegatingDbDataReader：DbDataReader 
 {
只读DbDataReader源； 
 public DelegatingDbDataReader（DbDataReader source）
 {
 this.source = source; 
} 
公共替代对象this [string name] {get {return source [name]; }} 
公共替代对象this [int ordinal] {get {return source [ordinal]; }} 
公共重写int深度{获取{返回source.Depth; }} 
公共重写int FieldCount {get {return source.FieldCount; }} 
公共重写bool HasRows {获取{返回source.HasRows; }} 
公共替代布尔值IsClosed {get {返回source.IsClosed; }} 
公共重写int RecordsAffected {获取{返回source.RecordsAffected; }} 
公共重写bool GetBoolean（int ordinal）{return source.GetBoolean（ordinal）; } 
公共替代字节GetByte（int ordinal）{返回source.GetByte（ordinal）; } 
公共重写long GetBytes（int ordinal，long dataOffset，byte [] buffer，int bufferOffset，int length）{返回source.GetBytes（ordinal，dataOffset，buffer，bufferOffset，length）; } 
公共重写char GetChar（int ordinal）{返回source.GetChar（ordinal）; } 
公共重写long GetChars（int序数，long dataOffset，char []缓冲区，int bufferOffset，int长度）{返回source.GetChars（ordinal，dataOffset，buffer，bufferOffset，长度）; } 
公共替代字符串GetDataTypeName（int ordinal）{返回source.GetDataTypeName（ordinal）; } 
公共重写DateTime GetDateTime（int ordinal）{返回source.GetDateTime（ordinal）; } 
公共优先级十进制GetDecimal（int ordinal）{返回source.GetDecimal（ordinal）; } 
公共重写double GetDouble（int ordinal）{返回source.GetDouble（ordinal）; } 
公共重写IEnumerator GetEnumerator（）{return source.GetEnumerator（）; } 
公共重写Type GetFieldType（int ordinal）{返回source.GetFieldType（ordinal）; } 
公共重写float GetFloat（int ordinal）{返回source.GetFloat（ordinal）; } 
公共重写Guid GetGuid（int ordinal）{返回source.GetGuid（ordinal）; } 
公共重写short GetInt16（int ordinal）{返回source.GetInt16（ordinal ;; } 
公共重写int GetInt32（int ordinal）{返回source.GetInt32（ordinal ;; } 
公共重写long GetInt64（int ordinal）{返回source.GetInt64（ordinal ;; } 
公共替代字符串GetName（int ordinal）{返回source.GetName（ordinal）; } 
公共重写int GetOrdinal（字符串名称）{返回source.GetOrdinal（name）; } 
公共替代字符串GetString（int ordinal）{返回source.GetString（ordinal）; } 
公共替代对象GetValue（int ordinal）{返回source.GetValue（ordinal）; } 
公共重写int GetValues（object [] values）{return source.GetValues（values）; } 
公共替代布尔值IsDBNull（int ordinal）{返回source.IsDBNull（ordinal）; } 
公共重写bool NextResult（）{返回source.NextResult（）; } 
公共重写bool Read（）{return source.Read（）; } 
公共重写void Close（）{source.Close（）; } 
公共优先级T GetFieldValue< T>（int序数）{返回source.GetFieldValue< T>（序数）； } 
公共优先任务< T> GetFieldValueAsync< T（整数，CancellationToken cancelledToken）{返回源。GetFieldValueAsync< T>（常规，cancellationToken）； } 
公共重写Type GetProviderSpecificFieldType（int ordinal）{返回source.GetProviderSpecificFieldType（ordinal ;; } 
公共替代对象GetProviderSpecificValue（int ordinal）{返回source.GetProviderSpecificValue（ordinal ;; } 
公共重写int GetProviderSpecificValues（object [] values）{返回source.GetProviderSpecificValues（values）; } 
公共重写DataTable GetSchemaTable（）{return source.GetSchemaTable（）; } 
公共重写Stream GetStream（int ordinal）{return source.GetStream（ordinal）; } 
公共重写TextReader GetTextReader（int顺序）{返回source.GetTextReader（ordinal ;; } 
公共替代Task< bool> IsDBNullAsync（int ordinal，CancellationToken cancelleToken）{返回源。IsDBNullAsync（ordinal，cancellingToken）; } 
公共替代Task< bool> ReadAsync（CancellationToken cancelledToken）{返回source.ReadAsync（cancellationToken）; } 
公共重写int VisibleFieldCount {获取{返回source.VisibleFieldCount; }} 
} 
  

并在其上构建所需的实际类：

  class UtcDateTimeConvertingDbDataReader：DelegatingDbDataReader 
 {
 public UtcDateTimeConvertingDbDataReader（DbDataReader source）：base（source）{} 
公共重写DateTime GetDateTime（int ordinal）
 {
返回DateTime.SpecifyKind（base.GetDateTime（ordinal），DateTimeKind.Utc）; 
} 
} 
  

一旦有了，我们需要将其插入使用EF 拦截的EF基础架构。

我们将首先创建一个自定义的 DbCommandInterceptor 派生类：

  class UtcDateTimeConvertingDbCommandInterceptor：DbCommandInterceptor 
 {
公共重写void ReaderExecuted（DbCommand命令，DbCommandInterceptionContext< DbDataReader>拦截上下文）
 {
 base.ReaderExecuted（command，拦截上下文）; 
 if（！（interceptContext.Result是UtcDateTimeConvertingDbDataReader）
&&interceptionContext.Result！= null 
&& interceptionContext.Exception == null）
 =新的UtcDateTimeConvertingDbDataReader（interceptionContext.Result）; 
} 
} 
  

注册（例如从您的 DbContext 派生类静态构造函数）：

 公共类YourDbContext：DbContext 
 { 
静态YourDbContext（）
 {
 DbInterception.Add（new UtcDateTimeConvertingDbCommandInterceptor（））; 
} 
 // ... 
} 
  

完成。

现在，来自数据库的每个 DateTime 值都将具有 Kind 属性设置为 Utc 。

When I create a Buyin object the response from the ASP.NET MVC controller, (return Json(response, JsonRequestBehavior.AllowGet);, looks like this:

"Buyin": {
        "Id": 95,
        "PlayerSessionId": 88,
        "PlayerId": 45,
        "PlayerName": "Alan",
        "Amount": 888,
        "BuyinType": "Credits",
        "Description": null,
        "Authorized": true,
        "SignPath": "~/Signs/Buyins\\95.png",
        "Payment": null,
        "CreationDate": "/Date(1477242738042)/"
    },

If I convert that on Epoch Converter I get this time: GMT: Sun, 23 Oct 2016 17:12:18.042 GMT

Looking in the database the stored datetime seems to be correct:

95  NULL    1   1   2016-10-23 17:12:18.043

When the response is sent out the Kind is set to UTC.

Now I call a controller to get all my data and all of the dates have several hours added to it:

 {
    "Id": 95,
    "PlayerSessionId": 88,
    "PlayerId": 45,
    "PlayerName": "Alan",
    "Amount": 888,
    "BuyinType": "Credits",
    "Description": null,
    "Authorized": true,
    "SignPath": "~/Signs/Buyins\\95.png",
    "Payment": null,
    "CreationDate": "/Date(1477267938043)/"
}

1477267938043 = GMT: Mon, 24 Oct 2016 00:12:18.043 GMT

However when I request this object I can see that the actual object has the correct date set:

But the Kind is set to Unspecified so I think this is causing the problem.

For the moment I don't have not set any globalization settings.

So basically my question is: When ASP.NET MVC loads the dates from the database is there a way to tell the server to load the dates with Kind set to UTC as I think that is the problem?

The database is saved and loaded using Entity Framework.

Update after the accepted answer

The accepted answer was great however my date values was already stored in the Database as UTC dates so I modified GetDateTime to this:

public override DateTime GetDateTime(int ordinal)
{
    var date = base.GetDateTime(ordinal);
    var utcDate = DateTime.SpecifyKind(date, DateTimeKind.Utc);
    return utcDate;
    //return base.GetDateTime(ordinal).ToUniversalTime();
}

解决方案

Assuming you are using EF6 and you want to set the Kind property of any DateTime value retrieved from the database to Utc.

Similar questions have been asked, and the answers tend to suggest hooking into the ObjectContext.ObjectMaterialized event, but it didn't fire for queries that use projection.

The solution I'm going to propose works for both entity and projection queries, by performing the conversion at the DbDataReader level (which is used by this type of queries).

In order to do that, we need a custom DbDataReader implementation that intercepts the GetDateTime method. Unfortunately implementing DbDataReader derived class requires a lot of boilerplate code. Luckily I already created a base class form my answer to Dynamic Translate to avoid C# syntax errors which simply delegates each method to the underlying DbDataReader instance, so I'll just take it from there:

abstract class DelegatingDbDataReader : DbDataReader
{
    readonly DbDataReader source;
    public DelegatingDbDataReader(DbDataReader source)
    {
        this.source = source;
    }
    public override object this[string name] { get { return source[name]; } }
    public override object this[int ordinal] { get { return source[ordinal]; } }
    public override int Depth { get { return source.Depth; } }
    public override int FieldCount { get { return source.FieldCount; } }
    public override bool HasRows { get { return source.HasRows; } }
    public override bool IsClosed { get { return source.IsClosed; } }
    public override int RecordsAffected { get { return source.RecordsAffected; } }
    public override bool GetBoolean(int ordinal) { return source.GetBoolean(ordinal); }
    public override byte GetByte(int ordinal) { return source.GetByte(ordinal); }
    public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length) { return source.GetBytes(ordinal, dataOffset, buffer, bufferOffset, length); }
    public override char GetChar(int ordinal) { return source.GetChar(ordinal); }
    public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length) { return source.GetChars(ordinal, dataOffset, buffer, bufferOffset, length); }
    public override string GetDataTypeName(int ordinal) { return source.GetDataTypeName(ordinal); }
    public override DateTime GetDateTime(int ordinal) { return source.GetDateTime(ordinal); }
    public override decimal GetDecimal(int ordinal) { return source.GetDecimal(ordinal); }
    public override double GetDouble(int ordinal) { return source.GetDouble(ordinal); }
    public override IEnumerator GetEnumerator() { return source.GetEnumerator(); }
    public override Type GetFieldType(int ordinal) { return source.GetFieldType(ordinal); }
    public override float GetFloat(int ordinal) { return source.GetFloat(ordinal); }
    public override Guid GetGuid(int ordinal) { return source.GetGuid(ordinal); }
    public override short GetInt16(int ordinal) { return source.GetInt16(ordinal); }
    public override int GetInt32(int ordinal) { return source.GetInt32(ordinal); }
    public override long GetInt64(int ordinal) { return source.GetInt64(ordinal); }
    public override string GetName(int ordinal) { return source.GetName(ordinal); }
    public override int GetOrdinal(string name) { return source.GetOrdinal(name); }
    public override string GetString(int ordinal) { return source.GetString(ordinal); }
    public override object GetValue(int ordinal) { return source.GetValue(ordinal); }
    public override int GetValues(object[] values) { return source.GetValues(values); }
    public override bool IsDBNull(int ordinal) { return source.IsDBNull(ordinal); }
    public override bool NextResult() { return source.NextResult(); }
    public override bool Read() { return source.Read(); }
    public override void Close() { source.Close(); }
    public override T GetFieldValue<T>(int ordinal) { return source.GetFieldValue<T>(ordinal); }
    public override Task<T> GetFieldValueAsync<T>(int ordinal, CancellationToken cancellationToken) { return source.GetFieldValueAsync<T>(ordinal, cancellationToken); }
    public override Type GetProviderSpecificFieldType(int ordinal) { return source.GetProviderSpecificFieldType(ordinal); }
    public override object GetProviderSpecificValue(int ordinal) { return source.GetProviderSpecificValue(ordinal); }
    public override int GetProviderSpecificValues(object[] values) { return source.GetProviderSpecificValues(values); }
    public override DataTable GetSchemaTable() { return source.GetSchemaTable(); }
    public override Stream GetStream(int ordinal) { return source.GetStream(ordinal); }
    public override TextReader GetTextReader(int ordinal) { return source.GetTextReader(ordinal); }
    public override Task<bool> IsDBNullAsync(int ordinal, CancellationToken cancellationToken) { return source.IsDBNullAsync(ordinal, cancellationToken); }
    public override Task<bool> ReadAsync(CancellationToken cancellationToken) { return source.ReadAsync(cancellationToken); }
    public override int VisibleFieldCount { get { return source.VisibleFieldCount; } }
}

and build the actual class that we need on top of it:

class UtcDateTimeConvertingDbDataReader : DelegatingDbDataReader
{
    public UtcDateTimeConvertingDbDataReader(DbDataReader source) : base(source) { }
    public override DateTime GetDateTime(int ordinal)
    {
        return DateTime.SpecifyKind(base.GetDateTime(ordinal), DateTimeKind.Utc);
    }
}

Once we have that, we need to plug it into EF infrastructure using EF interception.

We'll start by creating a custom DbCommandInterceptor derived class:

class UtcDateTimeConvertingDbCommandInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        base.ReaderExecuted(command, interceptionContext);
        if (!(interceptionContext.Result is UtcDateTimeConvertingDbDataReader)
            && interceptionContext.Result != null
            && interceptionContext.Exception == null)
            interceptionContext.Result = new UtcDateTimeConvertingDbDataReader(interceptionContext.Result);
    }
}

register it (for instance from your DbContext derived class static constructor):

public class YourDbContext : DbContext
{
    static YourDbContext()
    {
        DbInterception.Add(new UtcDateTimeConvertingDbCommandInterceptor());
    }
    // ...
}

and we are done.

Now every DateTime value coming from the database will have Kind property set to Utc.

这篇关于从数据库加载后，DateTime.Kind设置为未指定，而不是UTC的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！