Efcore: DateTime.Kind comes back as Unspecified

[gzak]

If you store a DateTime object to the DB with a DateTimeKind of either Utc or Local, when you read that record back from the DB you'll get a DateTime object whose kind is Unspecified. Basically, it appears the Kind property isn't persisted. This means that a test to compare equality of the before/after value of a round trip to the DB will fail.

Is there something that needs to be configured for the Kind to be preserved? Or what's the recommended way to handle this?

[gzak]

var entity = db.Entity.Single(e => e.Id == 123);
var now = DateTime.UtcNow;
entity.Date = now;
db.SaveChanges();

entity = db.Entity.Single(e => e.Id == 123);
Assert.Equal(now, entity.Date); // fails the assert

[rmja]

A slight variation of this is that it would be nice if one could configure the default DateTimeKind. For example, I know that all dates in my database are Utc, so it would be nice if this was specified for all fetched dates in the DbContext, or maybe configurable per entity property?

[ilmax]

+1 on whar @rmja suggested, it's a really nice addition.

[gzak]

One thing worth noting is that I think the DateTime type in C♯ has more precision than the DB equivalent, so equality probably won't be preserved after a round trip even with this change.

[ajcvickers]

Note for triage: Investigated mapping to DateTime CLR type to SQL Server datetimeoffset by default, but even when switching to the typed value buffer factory this still won't work without type conversions.

[rowanmiller]

Conclusions from triage:

• This is fundamentally a limitation of SQL Server/SQL Client, where the kind of datetime is not persisted.
• We could work around this by introducing a separate property to persist kind... but there is already DateTimeOffset which is designed to specifically store date/time in a time zone aware way. So really the solution isn't to try and workaround the limitations of DateTime, but educate folks to use DateTimeOffset.

[rmja]

@rowanmiller how about my suggestion? It would be really useful in systems where the kind does not need to be stored, because it is agreed upon by convention.

[rowanmiller]

@rmja I should have mentioned that too... at the moment we rely on ADO.NET to construct the value and then we set it on the property. We do want to introduce a feature where you can control how that value is get/set from/to the property. This would allow things like setting DateTime.Kind, type conversions, using Get/Set methods rather than properties, etc. https://github.com/aspnet/EntityFramework/issues/240 is the issue tracking this feature

[gzak]

@rowanmiller would you mind giving an example (like my second comment) that uses DateTimeOffset to correct the issue? Also, what type should I use for the corresponding column in the DB (assuming MSSQL server)?

[gzak]

Never mind my last comment, I didn't realize there was a corresponding sql type called datetimeoffset. Basically, we've switched everything over to the -offset variants in both C# and the DB which solved this issue as well as #5175.

[rowanmiller]

@gzak glad it's working for you now

[hikalkan]

I also would want such a convention @rmja mentioned (https://github.com/aspnet/EntityFramework/issues/4711#issuecomment-201405020). Because not all database providers have datetimeoffset type.

[hikalkan]

I found a solution for that and shared as a blog post: http://volosoft.com/datetime-specifykind-in-entity-framework-core-while-querying/

[havheg]

I had this same issue today. Why is Datetime/Timezones still an issue? I don't really care how DateTime is stored in the database as long as the returned value is the same as when I stored it. It could even be stored as a standardized(ISO 8601) string for all I care.

[EEaglehouse]

Switching to DateTimeOffset doesn't solve the problem. It isn't time zone aware; it only stores the UTC offset for a specific instant in time. There is no simple way to derive the time zone where the value was created. Picking the underlying UTC value out of the DateTimeOffset value would be the best you could do and DateTime is just as good at assuming that.

[ajcvickers]

@EEaglehouse and others. I'm working on a change that will allow the kind to be be preserved by storing the DateTime as a long in the database and automatically converting it on the way in and out. Should be in 2.1 as part of #242.

[EEaglehouse]

@ajcvickers Thank you. It would be nice to have that as an option. For me, the better solution remains to have an option to force the Kind to a particular value (Utc in my case) for DateTime values. Reading it as a DateTimeOffset is useless to me because I would still need to convert to DateTime with Kind as Utc in my applications' data layer; all local time zone conversions are handled in the presentation layer. Converting DateTime to a long for storage may be useful, but would effectively obfuscate the column type in the database. But please don't take this as discouraging you from implementing the custom type mapping, because then I could do for myself what I'm asking.

[ajcvickers]

@EEaglehouse Yes, after #242 you will be able to set things up to still store as a datetime in the database, but force a specific kind whenever it is read from the database.

[werwolfby]

We are using this updated workaround from: http://volosoft.com/datetime-specifykind-in-entity-framework-core-while-querying/
Thank you @hikalkan, but I've made few changes, after few hours of debugging EF:

```c#
public class DateTimeKindMapper
{
public static DateTime Normalize(DateTime value)
=> DateTime.SpecifyKind(value, DateTimeKind.Utc);

    public static DateTime? NormalizeNullable(DateTime? value)
        => value.HasValue ? DateTime.SpecifyKind(value.Value, DateTimeKind.Utc) : (DateTime?)null;

    public static object NormalizeObject(object value)
        => value is DateTime dateTime ? Normalize(dateTime) : value;
}

public class DateTimeKindEntityMaterializerSource : EntityMaterializerSource
{
    private static readonly MethodInfo _normalizeMethod =
        typeof(DateTimeKindMapper).GetTypeInfo().GetMethod(nameof(DateTimeKindMapper.Normalize));

    private static readonly MethodInfo _normalizeNullableMethod =
        typeof(DateTimeKindMapper).GetTypeInfo().GetMethod(nameof(DateTimeKindMapper.NormalizeNullable));

    private static readonly MethodInfo _normalizeObjectMethod =
        typeof(DateTimeKindMapper).GetTypeInfo().GetMethod(nameof(DateTimeKindMapper.NormalizeObject));

    public override Expression CreateReadValueExpression(Expression valueBuffer, Type type, int index, IProperty property = null)
    {
        if (type == typeof(DateTime))
        {
            return Expression.Call(
                _normalizeMethod,
                base.CreateReadValueExpression(valueBuffer, type, index, property));
        }

        if (type == typeof(DateTime?))
        {
            return Expression.Call(
                _normalizeNullableMethod,
                base.CreateReadValueExpression(valueBuffer, type, index, property));
        }

        return base.CreateReadValueExpression(valueBuffer, type, index, property);
    }

    public override Expression CreateReadValueCallExpression(Expression valueBuffer, int index)
    {
        var readValueCallExpression = base.CreateReadValueCallExpression(valueBuffer, index);
        if (readValueCallExpression.Type == typeof(DateTime))
        {
            return Expression.Call(
                _normalizeMethod,
                readValueCallExpression);
        }

        if (readValueCallExpression.Type == typeof(DateTime?))
        {
            return Expression.Call(
                _normalizeNullableMethod,
                readValueCallExpression);
        }

        if (readValueCallExpression.Type == typeof(object))
        {
            return Expression.Call(
                _normalizeObjectMethod,
                readValueCallExpression);
        }

        return readValueCallExpression;
    }
}

Workaround from @hikalkan works, except cases in subquery, like:

```c#
context.Entity.Select(e => new
{
    DateTimeField = e.NavigationProperty.OrderBy(n => n.OrderField).FirstOrDefault().DateTimeField
});

So extended EntityMaterializerSource can handle this as well, but because it wraps every object response as well, it can cause additional performance cost because of object to DateTime cast.

And it has additional fix for nullable DateTime as well. Before 2.1 we will use this workaround.

[EEaglehouse]

Hi, Alexander,

That looks like a nice solution and more advanced than the solution we have been using, which was to use T4 to generate a custom DBContext. The code you presented is pretty easy to understand and not very complicated. When we migrate to .NET Core, this will definitely come in handy.

Thank you very much for taking the time to share it with me!

--Ed

From: Alexander Puzynia [mailto:[email protected]]
Sent: Thursday, January 18, 2018 1:32 AM
To: aspnet/EntityFrameworkCore [e-mail]
Cc: Ed Eaglehouse [e-mail]; Mention [e-mail]
Subject: Re: [aspnet/EntityFrameworkCore] DateTime.Kind comes back as Unspecified (#4711)

We are using this updated workaround from: http://volosoft.com/datetime-specifykind-in-entity-framework-core-while-querying/

public class DateTimeKindMapper

{

    public static DateTime Normalize(DateTime value)

        => DateTime.SpecifyKind(value, DateTimeKind.Utc);



    public static DateTime? NormalizeNullable(DateTime? value)

        => value.HasValue ? DateTime.SpecifyKind(value.Value, DateTimeKind.Utc) : (DateTime?)null;



    public static object NormalizeObject(object value)

        => value is DateTime dateTime ? Normalize(dateTime) : value;

}



public class DateTimeKindEntityMaterializerSource : EntityMaterializerSource

{

    private static readonly MethodInfo _normalizeMethod =

        typeof(DateTimeKindMapper).GetTypeInfo().GetMethod(nameof(DateTimeKindMapper.Normalize));



    private static readonly MethodInfo _normalizeNullableMethod =

        typeof(DateTimeKindMapper).GetTypeInfo().GetMethod(nameof(DateTimeKindMapper.NormalizeNullable));



    private static readonly MethodInfo _normalizeObjectMethod =

        typeof(DateTimeKindMapper).GetTypeInfo().GetMethod(nameof(DateTimeKindMapper.NormalizeObject));



    public override Expression CreateReadValueExpression(Expression valueBuffer, Type type, int index, IProperty property = null)

    {

        if (type == typeof(DateTime))

        {

            return Expression.Call(

                _normalizeMethod,

                base.CreateReadValueExpression(valueBuffer, type, index, property));

        }



        if (type == typeof(DateTime?))

        {

            return Expression.Call(

                _normalizeNullableMethod,

                base.CreateReadValueExpression(valueBuffer, type, index, property));

        }



        return base.CreateReadValueExpression(valueBuffer, type, index, property);

    }



    public override Expression CreateReadValueCallExpression(Expression valueBuffer, int index)

    {

        var readValueCallExpression = base.CreateReadValueCallExpression(valueBuffer, index);

        if (readValueCallExpression.Type == typeof(DateTime))

        {

            return Expression.Call(

                _normalizeMethod,

                readValueCallExpression);

        }



        if (readValueCallExpression.Type == typeof(DateTime?))

        {

            return Expression.Call(

                _normalizeNullableMethod,

                readValueCallExpression);

        }



        if (readValueCallExpression.Type == typeof(object))

        {

            return Expression.Call(

                _normalizeObjectMethod,

                readValueCallExpression);

        }



        return readValueCallExpression;

    }

}

Workaround from volosoft works, except cases in subquery, like:

context.Entity.Select(e => new

{

DateTimeField = e.NavigationProperty.OrderBy(n => n.OrderField).FirstOrDefault().DateTimeField

});

So extended EntityMaterializerSource can handle this as well, but because it wraps every object response as well, it can cause additional performance cost because of object to DateTime cast.

And it has additional fix for nullable DateTime as well. Before 2.1 we will use this workaround.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHubhttps://github.com/aspnet/EntityFrameworkCore/issues/4711#issuecomment-358551156, or mute the threadhttps://github.com/notifications/unsubscribe-auth/ASrwvT9VrPRI9gyXit0b6RQOJd0K7-zyks5tLuVMgaJpZM4HrCRe.

[ajcvickers]

Please note that EntityMaterializerSource is internal code and any solution like that shown above may get broken with a new release of EF. That doesn't mean I'm saying not to use it--it's publicly available internal code for a reason--but please be aware of the risk associated with it.

Starting in EF Core 2.1, this would be one way to deal with DateTime.Kind:
C# modelBuilder .Entity<Foo>() .Property(e => e.SomeDate) .HasConversion(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Local));
This will ensure every time the date is read from the database it is specified as Local automatically.

[werwolfby]

@ajcvickers but my snippet above works with projections as well.

And we are waiting for EF Core 2.1 to remove this code from our solution, but until 2.1 we have to use it 😞.

[ajcvickers]

@werwolfby You are correct--I copy pasted that code snippet from another post and missed that last sentence, which was relevant there but not here. The workaround you posted is a good one--this is probably the best way to make this work, and is, in effect, what the value converter in 2.1 is doing.

Being able to do things like this is why we made the internal code available. However, we have to be careful that internal code is only used when people understand the risks. Otherwise too many people may start complaining when they get broken and we will be under pressure to make it really internal again.

[werwolfby]

@ajcvickers Yes I understand the risk using this code. And I completely agree, but we have to use it, and thank you to make this API public available 😄.
And I create gist to keep this "helper" up-to-date until EF Core 2.1: https://gist.github.com/werwolfby/7f04558bc21c8114e209d5727fb2e9f8

[cycbluesky]

Mark DateTimeKind

[martavoi]

@ajcvickers .HasConversion option don't cover computation, i.e:

var res = context.Select(i => new {Deadline = i.Created + i.Estimated});

[werwolfby]

@martavoi This is expected, because you're selecting anonymous object, and you didn't (and can't) specify conversion for anonymous type. And even if Created and Estimated has conversion, this will not help because it was converted to Sql without any conversion.

And as far as I understand you can't solve this issue with current .HasConversion functionality.
Because you can specify it only for Entities, but not for anonymous type.

There should be something to map to views, maybe it will help.

[martavoi]

@werwolfby i would say there must be smth allow us to configure DateTime materialization for entire Context. We usually store UTC dates and expect to see DateTime.Kind == UTC for all queries

[werwolfby]

@martavoi, we had tried to migrate to EF Core 2.1 as well. And yep, only few queries starts to work, but all this projection still doesn't work. This is because all this Value Converter can be set for properties only, it doesn't affect projection. So we still continue to use my workaround from above 😞 (gist)

We still need something from EF, to be able work on projections as well. CCed @ajcvickers.

For example this projection works partially, but I expected that it should works always:

c# context.Entities.Select(e => new ProjectionEntity { LastTested = e.Results .Where(x => x.CompletedDate.HasValue) .OrderByDescending(x => x.CompletedDate) .FirstOrDefault(). .CompletedDate, RevisionDate = e.CreationDate }
And RevisionDate converted right using value converter, while CompletedDate don't.
Both properties had converters.

I think this issue should be reopened.

[ajcvickers]

@martavoi Can you please post a new issue with a complete runnable project/solution or code listing that reproduces the behavior you are seeing? It would be good to infer the converter to use for Deadline, but I don't know how feasible it is now.

@werwolfby Can you also post another new issue with a complete runnable project/solution that reproduces what you are seeing? I think we need to understand better how that query is being executed in order to understand what is going on.

[werwolfby]

@ajcvickers Will try to implement it in few days.

But the main question here is still the same. Do you plan to allow specify converters globally on type, instead of on each property?

[ajcvickers]

@werwolfby This is tracked by #10784

[springy76]

The EF 6.x provider of System.Data.SQLite has an option for the connection string which lets you configure which DateTimeKind ANY DateTime retrieved by the provider will have no matter if entities or anonymous objects or whatsoever. SINCE YEARS!

[forlayo]

I am using the solution that @rowanmiller suggest; and I would say that it should be considered as correct solution.

Because, as he points, SQL Server does not allow to store that datetime is on an specific timezone, and setting a default one break the consistency conceptually through the system.

You can do same you're doing with datetime with datetimeoffset and it would include the timezone, so why not using it?

[EEaglehouse]

Because datetimeoffset includes only the difference between UTC and local time with the saved datetime value; it does not include the time zone. Anyone who has to deal with different time zones is well-advised to learn the difference. The offset alone cannot identify the actual time zone nor if Daylight Saving Time was in effect, so datetimeoffset is of extremely limited use. Internally, it is represented by a UTC value anyway, so it adds little value over simply saving a datetime value as UTC in the first place.
People need to be educated to not use datetimeoffset unless they know in what rare and isolated cases it may be truly helpful. Slapping datetimeoffset in as a replacement for datetime and expecting to have the time zone included is misguided, at best, because it won't do that.
If HasConversion() or a ValueConverter can be applied to types rather than just properties, it would be a good solution to supplying the expected DateTimeKind.

[Compufreak345]

If anyone finds it helpful - I modified the great snippet from @ajcvickers to make sure all my dates are saved as UTC and read as UTC.

foreach(var entityType in modelBuilder.Model.GetEntityTypes()) {
 foreach(var property in entityType.GetProperties()) {
  if (property.ClrType == typeof(DateTime)) {
   modelBuilder.Entity(entityType.ClrType)
    .Property < DateTime > (property.Name)
    .HasConversion(
     v => v.ToUniversalTime(),
     v => DateTime.SpecifyKind(v, DateTimeKind.Utc));
  } else if (property.ClrType == typeof(DateTime ? )) {
   modelBuilder.Entity(entityType.ClrType)
    .Property < DateTime ? > (property.Name)
    .HasConversion(
     v => v.HasValue ? v.Value.ToUniversalTime() : v,
     v => v.HasValue ? DateTime.SpecifyKind(v.Value, DateTimeKind.Utc) : v);
  }
 }
}

[springy76]

@Compufreak345 will this work with queries composing into anonymous (or non-entity) types?

[Compufreak345]

@Compufreak345 will this work with queries composing into anonymous (or non-entity) types?

I am not sure what you mean by "queries composing into anonymous types", if you do your writing database transactions using LINQ to SQL with entities it should work for everything you do. It should also work if you convert your properties to another object type while reading LINQ entities, if you do something outside of entity framework like hard-coded SQL queries it will not.

[clement-fifty]

Note that in my case, I have queries (View equivalent) and that doesn't seem to like this workaround, so I had to use the following :

foreach(var entityType in builder.Model.GetEntityTypes()) {
    if (entityType.IsQueryType) continue; // This is the difference!

    foreach(var property in entityType.GetProperties()) {
        if (property.ClrType == typeof(DateTime)) {
            builder.Entity(entityType.ClrType)
                .Property < DateTime > (property.Name)
                .HasConversion(
                    v => v.ToUniversalTime(),
                    v => DateTime.SpecifyKind(v, DateTimeKind.Utc));
        } else if (property.ClrType == typeof(DateTime ? )) {
            builder.Entity(entityType.ClrType)
                .Property < DateTime ? > (property.Name)
                .HasConversion(
                    v => v.HasValue ? v.Value.ToUniversalTime() : v,
                    v => v.HasValue ? DateTime.SpecifyKind(v.Value, DateTimeKind.Utc) : v);
        }
    }
}

[ChristopherHaws]

@clement-fifty I wasn't able to get your solution working whenever I had the property manually configured (for example if I give the column a custom name in the db). I was able to make it work by doing the following though:

```c#
var dateTimeConverter = new ValueConverter(
v => v.ToUniversalTime(),
v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

var nullableDateTimeConverter = new ValueConverter(
v => v.HasValue ? v.Value.ToUniversalTime() : v,
v => v.HasValue ? DateTime.SpecifyKind(v.Value, DateTimeKind.Utc) : v);

foreach (var entityType in builder.Model.GetEntityTypes())
{
if (entityType.IsQueryType)
{
continue;
}

foreach (var property in entityType.GetProperties())
{
    if (property.ClrType == typeof(DateTime))
    {
        property.SetValueConverter(dateTimeConverter);
    }
    else if (property.ClrType == typeof(DateTime?))
    {
        property.SetValueConverter(nullableDateTimeConverter);
    }
}

}
```

[btecu]

.IsQueryType doesn't seem to be present for EF Core 3.

[smitpatel]

entityType.IsKeyless

[jeremycook]

I also check the column type since I have some 'date' fields in my database that are not UTC. For a SQL Server datatime field that looks like this:

property.GetColumnType() == "datetime"

[ChristopherHaws]

@jeremycook I recently have run into a similar problem where certain fields I didnt want to be tracked as UTC, so I made the following changes to my code:

public class ApplicationContext : DbContext
{
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
        builder.ApplyUtcDateTimeConverter();
    }
}

public static class UtcDateAnnotation
{
    private const string IsUtcAnnotation = "IsUtc";
    private static readonly ValueConverter<DateTime, DateTime> UtcConverter =
        new ValueConverter<DateTime, DateTime>(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

    public static PropertyBuilder<TProperty> IsUtc<TProperty>(this PropertyBuilder<TProperty> builder, bool isUtc = true) =>
        builder.HasAnnotation(IsUtcAnnotation, isUtc);

    public static bool IsUtc(this IMutableProperty property) =>
        ((bool?)property.FindAnnotation(IsUtcAnnotation)?.Value) ?? true;

    /// <summary>
    /// Make sure this is called after configuring all your entities.
    /// </summary>
    public static void ApplyUtcDateTimeConverter(this ModelBuilder builder)
    {
        foreach (var entityType in builder.Model.GetEntityTypes())
        {
            foreach (var property in entityType.GetProperties())
            {
                if (!property.IsUtc())
                {
                    continue;
                }

                if (property.ClrType == typeof(DateTime) ||
                    property.ClrType == typeof(DateTime?))
                {
                    property.SetValueConverter(UtcConverter);
                }
            }
        }
    }
}

Which allows me to mark a property as not UTC with the following code (UTC is true by default):

builder.Property(x => x.DateField).IsUtc(false);

[JonPSmith]

Can I ask a question on nullable value converters. The EF docs says

A null value will never be passed to a value converter. This makes the implementation of conversions easier and allows them to be shared amongst nullable and non-nullable properties.

(I bolded the part in question).

But The comment by @ChristopherHaws shows two versions for a DateTime and DateTime?. Is that necessary or can you assume that your converter won't be called if the value is null?

[EEaglehouse]

Great question. The way I read that note, if the only difference in the second ValueConverter is checking for null, it won't be called anyway. In the DateTime? case here, it would be unnecessary.

But thinking about it, it says a null value will never be passed to a value converter and this allows them to be shared. But it does not say a null value cannot be returned from a value converter (say for example, converting an empty string to a null), so a nullable option could still be useful.

Would somebody who has experience with this please comment?

[ajcvickers]

@JonPSmith @EEaglehouse Null will never be passed to a converter, so the same non-nullable converter can be used for the PK and the FK. We propagate the converter set on the PK automatically to FKs, so you only really need to set the converter on the PK.

Returning null from a converter is possible, but may result in unexpected behavior for FKs since it changes the relationship.

[ChristopherHaws]

@EEaglehouse I updated the code snippet to exclude the nullable case. Thanks @ajcvickers for confirming that. 👍

[gabynevada]

@ChristopherHaws I modified the code snippet to include Utc conversion for nullable DateTime objects (DateTime?)
```c#
public class ApplicationContext : DbContext
{
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);

  builder.ApplyConfigurationsFromAssembly(Assembly.GetExecutingAssembly());
  builder.ApplyUtcDateTimeConverter();

}
}

public static class UtcDateAnnotation
{
private const String IsUtcAnnotation = "IsUtc";
private static readonly ValueConverter UtcConverter =
new ValueConverter(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

private static readonly ValueConverter UtcNullableConverter =
new ValueConverter(v => v, v => v == null ? v : DateTime.SpecifyKind(v.Value, DateTimeKind.Utc));

public static PropertyBuilder IsUtc(this PropertyBuilder builder, Boolean isUtc = true) =>
builder.HasAnnotation(IsUtcAnnotation, isUtc);

public static Boolean IsUtc(this IMutableProperty property) =>
((Boolean?)property.FindAnnotation(IsUtcAnnotation)?.Value) ?? true;

///

/// Make sure this is called after configuring all your entities.
///

public static void ApplyUtcDateTimeConverter(this ModelBuilder builder)
{
foreach (var entityType in builder.Model.GetEntityTypes())
{
foreach (var property in entityType.GetProperties())
{
if (!property.IsUtc())
{
continue;
}

    if (property.ClrType == typeof(DateTime))
    {
      property.SetValueConverter(UtcConverter);
    }

    if (property.ClrType == typeof(DateTime?))
    {
      property.SetValueConverter(UtcNullableConverter);
    }
  }
}

}
}
```

[ChristopherHaws]

Null will never be passed to a converter, so the same non-nullable converter can be used for the PK and the FK. We propagate the converter set on the PK automatically to FKs, so you only really need to set the converter on the PK.

Returning null from a converter is possible, but may result in unexpected behavior for FKs since it changes the relationship.

@gabynevada Per this comment, you shouldn't need to handle null, that is why I removed it from my snippet. Converters don't get called for null values.

[rallets]

@ChristopherHaws I can confirm that if I have a nullable DateTime I need an explicit DateTime? converter as posted by @gabynevada

[JonPSmith]

Hi @rallets,

Doesn't look like @ChristopherHaws is around. I can confirm that you don't need an explicit DateTime? converter. A null value (of any type) is just passed through as a null would calling your Value Converter. See this note at the in the EF Core docs on Value Converters.

I hope that helps.

[jeremycook]

@JonPSmith my experience matches @rallets ... Maybe there is some confusion over configuration for a nullable type vs. a null value being passed. I found that configuration is needed for both the nullable and non-nullable version of a DateTime if your model has properties of both types.

To clarify by example (and this was with EF Core 3 or 3.1, not sure if 5.0 behaves different) if I have a property like public DateTime? Created { get; set; } and I want to apply a type converter to it, the signature must match DateTime? and not simply DateTime. Perhaps this is a bug or somehow we are not using the type converter configuration API correctly!?

[rallets]

@JonPSmith Thanks, I see where the difference is.
The link you are providing is about the built-in modelBuilder.[...].HasConversion(converter).
In the snippet used to apply ~globally~ automatically the validators, there is a conditional check on the property type:
if (property.ClrType == typeof(DateTime))
this means that if the type of the property is DateTime? the converter is not applied.

Hence in this case we need to handle the DateTime?, too:

if (property.ClrType == typeof(DateTime?)) {
    property.SetValueConverter(UtcNullableConverter);
}

Probably it's possible to make it working using only one converter (UtcConverter) for both types:
if (property.ClrType == typeof(DateTime) || property.ClrType == typeof(DateTime?))
but I need to test it for confirmation.

[JonPSmith]

Yes @rallets, I didn't realise that you were referring to @gabynevada example which applies a value converter. Yes, changing the test would fix that.

Also see Arthur Vickers' comment in this issue. If anyone knows the right answer, then Arthur does!

[joakimriedel]

Probably it's possible to make it working using only one converter (UtcConverter) for both types:
if (property.ClrType == typeof(DateTime) || property.ClrType == typeof(DateTime?))
but I need to test it for confirmation.

Just verified that this works in my codebase, using the same value converter for both nullable and non-nullable. Thanks for the tip @rallets !

[ChristopherHaws]

Thanks for confirming that bug in my snippet. I have updated the snippet to reflect the changes. 👍

[peabnuts123]

Is it possible to wrap up some of the code-snippets posted above into some kind of annotation? Like

  [Required]
  [HasDateTimeKind(DateTimeKind.Utc)]
  public DateTime CreatedOn { get; set; }

My knowledge of customising EFCore's serialisation etc is only limited. If possible, maybe this could even land in something like System.ComponentModel.Annotations? Seems like there is a pretty wide use-case/need for people specifying the kind, as database providers to not encode this information.

[jeremycook]

@peabnuts123 That would be convenient. That attribute and the annotations folks graciously provided here still don't overcome the fact that you can still shoot yourself in the foot with DateTime...like when you instantiate a DateTime object without specifying its kind, or by specifying the wrong kind like when DateTime.Now is used when DateTime.UtcNow should have been. For folks who are growing tired of worrying about DateTime I suggest just using DateTimeOffset for now (no pun intended) and/or upvoting the Native support for NodaTime issue.

[ChristopherHaws]

@peabnuts123 Here is an updated version of my snippet that includes an attribute called IsUtc the behaves the same as the builder method:

public static class UtcDateAnnotation {
    private const string IsUtcAnnotation = "IsUtc";
    private static readonly ValueConverter<DateTime, DateTime> UtcConverter =
        new ValueConverter<DateTime, DateTime>(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

    public static PropertyBuilder<TProperty> IsUtc<TProperty>(this PropertyBuilder<TProperty> builder, bool isUtc = true) =>
        builder.HasAnnotation(IsUtcAnnotation, isUtc);

    public static bool IsUtc(this IMutableProperty property) {
        var attribute = property.PropertyInfo.GetCustomAttribute<IsUtcAttribute>();
        if (attribute is not null && attribute.IsUtc) {
            return true;
        }

        return ((bool?)property.FindAnnotation(IsUtcAnnotation)?.Value) ?? true;
    }

    /// <summary>
    /// Make sure this is called after configuring all your entities.
    /// </summary>
    public static void ApplyUtcDateTimeConverter(this ModelBuilder builder) {
        foreach (var entityType in builder.Model.GetEntityTypes()) {
            foreach (var property in entityType.GetProperties()) {
                if (!property.IsUtc()) {
                    continue;
                }

                if (property.ClrType == typeof(DateTime) ||
                    property.ClrType == typeof(DateTime?)) {
                    property.SetValueConverter(UtcConverter);
                }
            }
        }
    }
}

public class IsUtcAttribute : Attribute {
    public IsUtcAttribute(bool isUtc = true) => this.IsUtc = isUtc;
    public bool IsUtc { get; }
}

Something to note though, this snippet is not intended to "convert" the datetime into utc, but rather is used to specify the kind when reading the value from the database. You still need to be diligent and only ever pass in the correct type of DateTime. As far as I know it is not possible to write a converter that is smart enough to do the correct conversion logic since expressions are pretty limited. If it were possible though, DateTimeKind.Unspecified would probably be unhandled and should throw an exception since there is no way to know what timezone it is in.

DateTime functionality is weird (hence why NodaTime exists as @jeremycook pointed out)... The built in functionality makes a lot of assumptions that could be (and often are) wrong:

• DateTimeKind.Utc
  
  
  
  • ToUtc does nothing
  
  
  • ToLocalTime converts the datetime to the system's timezone
  
  
• DateTimeKind.Local
  
  
  
  • ToUtc converts the datetime to UTC timezone
  
  
  • ToLocalTime does nothing
  
  
• DateTimeKind.Unspecified (this is where things get weird)
  
  
  
  • ToUtc assumes the datetime is in the systems timezone and converts to UTC from the system timezone
  
  
  • ToLocalTime assumes the datetime is in UTC and converts to the systems timezone from UTC
  
  

Taking on a dependency like NodaTime can be tricky too however because you need to add support to it throughout the entire stack of your app (i.e. EF Postgresql provider supports it, but the rest do not as far as I know, XML/JSON serializers need to be configured to use it, ASP.NET needs configuration, etc). Unfortunately there is no silver bullet here. What I do is either use DateTimes and always deal with things in UTC and let the view convert to the timezone the user wants to see things in or use DateTimeOffset, which has its own pros/cons.

[peabnuts123]

That's top notch @ChristopherHaws. Thanks for the code and your dedication to this issue. That gets pretty much as close to an optimal solution as I think you can achieve without EF Core adopting it natively.

[acbaile]

Offered is the best solution. Attribute or fluent API call. It will be convenient. Time in DB can be stored by the most economic way, but when EF Core takes it from DB and puts into C# property, it creates DateTime with assigned DateTimeKind. If it is not yet, it must be added to future functionality of EF Core.

[HasDateTimeKind(DateTimeKind.Utc)]
public DateTime CreatedOn { get; set; }

I think, keep time Kind in DB is not good idea, because it takes place, and necessity of it is very low. If you need it, you can keep additional Byte column in DB. Usually, time Kind is the same for all rows. So, it's more rational to keep it in EF Core entities configuration.

In my case, I simply convert all time from Unspecified to UTC, even without annotation. It seems, for global databases it's the most common case.

[ChristopherHaws]

@acbaile I am also storing all my dates as UTC in the database. The problem occurs when you read the dates out of the database. The default behavior is to read the values as "unspecified" instead of as UTC how they were stored. This means that if I we to call .ToUniversalTime() on them, I would now end up with the incorrect date. This gets particularly interesting when you further serialize the values into json or xml (as is my use case since the values are returned from an ASP.NET Core WebAPI). In this scenario the values would be read from the database as unspecified and then serialized to JSON without a 'Z' at the end to specify that it is zulu (UTC) time which would in turn be translated incorrectly by front-end libraries such as momentjs. Instead of constantly having to specify the kind on every datetime I read from the database, I wrote that snippet to do the work for me. :)

Here is a short snippet to show the problem:

void Main() {
    var utcNow = DateTime.UtcNow;

    var json = JsonConvert.SerializeObject(new {
        UtcDateTime = utcNow,
        UnspecifiedDateTime = DateTime.SpecifyKind(utcNow, DateTimeKind.Unspecified),
        UnspecifiedDateTimeOffset = new DateTimeOffset(DateTime.SpecifyKind(utcNow, DateTimeKind.Unspecified))
    }, Newtonsoft.Json.Formatting.Indented);

    Console.WriteLine(json);

    /*
    Outputs:
    {
      "UtcDateTime": "2020-11-30T09:05:44.3006035Z",
      "UnspecifiedDateTime": "2020-11-30T09:05:44.3006035",
      "UnspecifiedDateTimeOffset": "2020-11-30T09:05:44.3006035-08:00"
    }
    */
}

Hope that helps clarify!