C# Linq to SQL DateTime 值是本地的（Kind=Unspecified） - 我如何使它成为 UTC？

Linq to SQL DateTime values are local (Kind=Unspecified) - How do I make it UTC?

提问by ericsson007

Isn't there a (simple) way to tell Linq To SQL classes that a particular DateTime property should be considered as UTC (i.e. having the Kind property of the DateTime type to be Utc by default), or is there a 'clean' workaround?

是否有（简单）方法告诉 Linq To SQL 类特定的 DateTime 属性应被视为 UTC（即默认情况下，DateTime 类型的 Kind 属性为 Utc），或者是否有“干净”的解决方法?

The time zone on my app-server is not the same as the SQL 2005 Server (cannot change any), and none is UTC. When I persist a property of type DateTime to the dB I use the UTC value (so the value in the db column is UTC), but when I read the values back (using Linq To SQL) I get the .Kind property of the DateTime value to be 'Unspecified'.

我的应用服务器上的时区与 SQL 2005 Server 不同（不能更改任何），并且没有一个是 UTC。当我将 DateTime 类型的属性保留到 dB 时，我使用 UTC 值（因此 db 列中的值是 UTC），但是当我读回值（使用 Linq To SQL）时，我得到了 DateTime 的 .Kind 属性值为“未指定”。

The problem is that when I 'convert' it to UTC it is 4 hours off. This also means that when it is serialized it it ends up on the client side with a 4 hour wrong offset (since it is serialized using the UTC).

问题是，当我将它“转换”为 UTC 时，它是 4 小时。这也意味着当它被序列化时，它在客户端以 4 小时的错误偏移量结束（因为它是使用 UTC 序列化的）。

回答by Marc Gravell

The only way I can think to do this would be to add a shim property in a partial class that does the translation...

我能想到的唯一方法是在执行翻译的部分类中添加一个 shim 属性......

回答by Hyman Ukleja

This code snippet will allow you to convert the DateTimes (Kind=Unspecified) you get back from LINQ to SQL into UTC times without the times being affected.

此代码片段将允许您将从 LINQ 返回到 SQL 的 DateTimes（Kind=Unspecified）转换为 UTC 时间，而不会影响时间。

TimeZoneInfo UTCTimeZone = TimeZoneInfo.FindSystemTimeZoneById("UTC");
DateTime utcTime = TimeZoneInfo.ConvertTimeToUtc(myLinq2SQLTime, UTCTimeZone);

There are probably cleaner ways to do this but I had this to hand and could test it quickly!

可能有更简洁的方法可以做到这一点，但我手头有这个，可以快速测试！

I am not sure if there is a way to get it working with LINQ to SQL classes transparently - you might want to look in the partial class and see if you can hook where the values are read/written.

我不确定是否有办法让它透明地与 LINQ to SQL 类一起使用 - 您可能想查看部分类，看看是否可以挂钩读取/写入值的位置。

回答by Michal Rogozinski

I you want UTC, TimeZone class can do it for you, if you want to convert between different timezones, than TimeZoneInfo is for you. exemple from my code with TimeZoneInfo:

我你想要 UTC，TimeZone 类可以为你做，如果你想在不同的时区之间转换，比 TimeZoneInfo 更适合你。来自我的 TimeZoneInfo 代码示例：

TimeZoneInfo cet = TimeZoneInfo.FindSystemTimeZoneById("Central European Standard Time");
ac.add_datetime = TimeZoneInfo.ConvertTime(DateTime.Now, cet);            

回答by Joe

SQL Server DateTime does not include any timezone or DateTimeKind information, therefore DateTime values retrieved from the database correctly have Kind = DateTimeKind.Unspecified.

SQL Server DateTime 不包括任何时区或 DateTimeKind 信息，因此从数据库中正确检索的 DateTime 值具有 Kind = DateTimeKind.Unspecified。

If you want to make these times UTC, you should 'convert' them as follows:

如果您想将这些时间设为 UTC，您应该按如下方式“转换”它们：

DateTime utcDateTime = new DateTime(databaseDateTime.Ticks, DateTimeKind.Utc);

or the equivalent:

或等价物：

DateTime utcDateTime = DateTime.SpecifyKind(databaseDateTime, DateTimeKind.Utc);

I assume your problem is that you are attempting to convert them as follows:

我认为您的问题是您正在尝试按如下方式转换它们：

DateTime utcDateTime = databaseDateTime.ToUniversalTime();

This may appear reasonable at first glance, but according to the MSDN documentation for DateTime.ToUniversalTime, when converting a DateTime whose Kind is Unspecified:

乍一看，这似乎是合理的，但根据DateTime.ToUniversalTime 的 MSDN 文档，当转换 Kind 为 Unspecified 的 DateTime 时：

The current DateTime object is assumed to be a local time, and the conversion is performed as if Kind were Local.

当前 DateTime 对象被假定为本地时间，并且转换的执行就像 Kind 是本地时间一样。

This behavior is necessary for backwards compatibility with .NET 1.x, which didn't have a DateTime.Kind property.

为了与没有 DateTime.Kind 属性的 .NET 1.x 向后兼容，此行为是必要的。

回答by RobSiklos

The generated LinqToSql code provides extensibility points, so you can set values when the objects are loaded.

生成的 LinqToSql 代码提供了扩展点，因此您可以在加载对象时设置值。

The key is to create a partial class which extends the generated class, and then implement the OnLoadedpartial method.

关键是创建一个分部类来扩展生成的类，然后实现OnLoaded分部方法。

For instance, let's say your class is Person, so you have a generated partial Personclass in Blah.designer.cs.

例如，假设您的类是Person，因此您Person在Blah.designer.cs.

Extend the partial class by creating a new class (must be in a different file), as follows:

通过创建一个新类（必须在不同的文件中）来扩展分部类，如下所示：

public partial class Person {

  partial void OnLoaded() {
    this._BirthDate = DateTime.SpecifyKind(this._BirthDate, DateTimeKind.Utc);
  }
}

回答by Gautam Jain

@rob263 provided an excellent method.

@rob263 提供了一个很好的方法。

This is only an additional help I wish to provide if you are using Entity Framework instead of Linq To Sql.

如果您使用的是实体框架而不是 Linq To Sql，这只是我希望提供的额外帮助。

Entity Framework does not support OnLoaded event.

实体框架不支持 OnLoaded 事件。

Instead, you can do the following:

相反，您可以执行以下操作：

 public partial class Person
    {
        protected override void OnPropertyChanged(string property)
        {
            if (property == "BirthDate")
            {
                this._BirthDate= DateTime.SpecifyKind(this._BirthDate, DateTimeKind.Utc);
            }

            base.OnPropertyChanged(property);
        }

    }

回答by aarondcoleman

For our case it was impractical to always specify the DateTimeKind as stated previously:

对于我们的例子，总是像前面提到的那样指定 DateTimeKind 是不切实际的：

DateTime utcDateTime = DateTime.SpecifyKind(databaseDateTime, DateTimeKind.Utc);

We are using Entity Framework, but this should be similar to Linq-to-SQL

我们正在使用实体框架，但这应该类似于 Linq-to-SQL

If you want to force all DateTime objects coming out of the database to be specified as UTC you'll need to add a T4 transform file and add additional logic for all DateTime and nullable DateTime objects such that they get initialized as DateTimeKind.Utc

如果你想强制所有来自数据库的 DateTime 对象被指定为 UTC 你需要添加一个 T4 转换文件并为所有 DateTime 和可为空的 DateTime 对象添加额外的逻辑，以便它们被初始化为 DateTimeKind.Utc

I have a blog post which explains this step by step: http://www.aaroncoleman.net/post/2011/06/16/Forcing-Entity-Framework-to-mark-DateTime-fields-at-UTC.aspx

我有一篇博客文章逐步解释了这一点：http: //www.aaroncoleman.net/post/2011/06/16/Forcing-Entity-Framework-to-mark-DateTime-fields-at-UTC.aspx

In short:

简而言之：

1) Create the .tt file for your .edmx model (or .dbml for Linq-to-SQL)

1）为您的 .edmx 模型（或 Linq-to-SQL 的 .dbml）创建 .tt 文件

2) Open the .tt file and find the "WritePrimitiveTypeProperty" method.

2）打开.tt文件，找到“WritePrimitiveTypeProperty”方法。

3) Replace the existing setter code. This is everything between the ReportPropertyChangingand the ReportPropertyChangedmethod callbacks with the following:

3) 替换现有的 setter 代码。这是ReportPropertyChanging和ReportPropertyChanged方法回调之间的所有内容，如下所示：

<#+ if( ((PrimitiveType)primitiveProperty.TypeUsage.EdmType).PrimitiveTypeKind == PrimitiveTypeKind.DateTime)
            {
#>
        if(<#=code.FieldName(primitiveProperty)#> == new DateTime())
        {
            <#=code.FieldName(primitiveProperty)#> = StructuralObject.SetValidValue(value<#=OptionalNullableParameterForSetValidValue(primitiveProperty, code)#>);
<#+ 
            if(ef.IsNullable(primitiveProperty))
            {  
#>              
            if(value != null)
                <#=code.FieldName(primitiveProperty)#> = DateTime.SpecifyKind(<#=code.FieldName(primitiveProperty)#>.Value, DateTimeKind.Utc);
<#+             } 
            else
            {#>
            <#=code.FieldName(primitiveProperty)#> = DateTime.SpecifyKind(<#=code.FieldName(primitiveProperty)#>, DateTimeKind.Utc);                
<#+ 
            } 
#>
        }
        else
        {
            <#=code.FieldName(primitiveProperty)#> = StructuralObject.SetValidValue(value<#=OptionalNullableParameterForSetValidValue(primitiveProperty, code)#>);
        }
<#+ 
        }
        else
        {
#>
    <#=code.FieldName(primitiveProperty)#> = StructuralObject.SetValidValue(value<#=OptionalNullableParameterForSetValidValue(primitiveProperty, code)#>);
<#+ 
        }
#>

回答by Alex

I use this way to specify the DateTimeKindon the fly:

我使用这种方式动态指定DateTimeKind：

DateTime myDateTime = new DateTime(((DateTime)myUtcValueFromDb).Ticks, DateTimeKind.Utc);