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

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?

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

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

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

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

For instance, let's say your class is Person, so you have a generated partial Person class in 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);
  }
}

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

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

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 http://msdn.microsoft.com/en-us/library/system.datetime.touniversaltime.aspx">the MSDN documentation for DateTime.ToUniversalTime, when converting a DateTime whose Kind is Unspecified:

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

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

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

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

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

We are using Entity Framework, but this should be similar to 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

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

In short:

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

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

3. Replace the existing setter code. This is everything between the ReportPropertyChanging and the ReportPropertyChanged method callbacks with the following:

   <#+ 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)#>); <#+ } #>

@rob263 provided an excellent method.

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

Entity Framework does not support OnLoaded event.

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);
        }

    }

I use this way to specify the DateTimeKind on the fly:

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

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.

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.

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:

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