Entity Framework DateTime and UTC

Question

Is it possible to have Entity Framework (I am using the Code First Approach with CTP5 currently) store all DateTime values as UTC in the database?

Or is there maybe a way to specify it in the mapping, for example in this one for the last_login column:

modelBuilder.Entity<User>().Property(x => x.Id).HasColumnName("id");
modelBuilder.Entity<User>().Property(x => x.IsAdmin).HasColumnName("admin");
modelBuilder.Entity<User>().Property(x => x.IsEnabled).HasColumnName("enabled");
modelBuilder.Entity<User>().Property(x => x.PasswordHash).HasColumnName("password_hash");
modelBuilder.Entity<User>().Property(x => x.LastLogin).HasColumnName("last_login");

Answer 1

Here is one approach you might consider:

First, define this following attribute:

[AttributeUsage(AttributeTargets.Property)]
public class DateTimeKindAttribute : Attribute
{
    private readonly DateTimeKind _kind;

    public DateTimeKindAttribute(DateTimeKind kind)
    {
        _kind = kind;
    }

    public DateTimeKind Kind
    {
        get { return _kind; }
    }

    public static void Apply(object entity)
    {
        if (entity == null)
            return;

        var properties = entity.GetType().GetProperties()
            .Where(x => x.PropertyType == typeof(DateTime) || x.PropertyType == typeof(DateTime?));

        foreach (var property in properties)
        {
            var attr = property.GetCustomAttribute<DateTimeKindAttribute>();
            if (attr == null)
                continue;

            var dt = property.PropertyType == typeof(DateTime?)
                ? (DateTime?) property.GetValue(entity)
                : (DateTime) property.GetValue(entity);

            if (dt == null)
                continue;

            property.SetValue(entity, DateTime.SpecifyKind(dt.Value, attr.Kind));
        }
    }
}

Now hook that attribute up to your EF context:

public class MyContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }

    public MyContext()
    {
        ((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized +=
            (sender, e) => DateTimeKindAttribute.Apply(e.Entity);
    }
}

Now on any DateTime or DateTime? properties, you can apply this attribute:

public class Foo
{
    public int Id { get; set; }

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

With this in place, whenever Entity Framework loads an entity from the database, it will set the DateTimeKind that you specify, such as UTC.

Note that this doesn't do anything when saving. You'll still have to have the value properly converted to UTC before you try to save it. But it does allow you to set the kind when retrieving, which allows it to be serialized as UTC, or converted to other time zones with TimeZoneInfo.

Answer 2

For EF Core, there is a great discussion on this topic on GitHub: https://github.com/dotnet/efcore/issues/4711

A solution (credit to Christopher Haws) that will result in treating all dates when storing them to/retrieving them from database as UTC is to add the following to the OnModelCreating method of your DbContext class:

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

var nullableDateTimeConverter = new ValueConverter<DateTime?, DateTime?>(
    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.IsKeyless)
    {
        continue;
    }

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

Also, check this link if you want to exclude some properties of some entities from being treated as UTC.

Answer 3

I really like Matt Johnson's approach, but in my model ALL of my DateTime members are UTC and I don't want to have to decorate all of them with an attribute. So I generalized Matt's approach to allow the event handler to apply a default Kind value unless a member is explicitly decorated with the attribute.

The constructor for the ApplicationDbContext class includes this code:

/// <summary> Constructor: Initializes a new ApplicationDbContext instance. </summary>
public ApplicationDbContext()
        : base(MyApp.ConnectionString, throwIfV1Schema: false)
{
    // Set the Kind property on DateTime variables retrieved from the database
    ((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized +=
      (sender, e) => DateTimeKindAttribute.Apply(e.Entity, DateTimeKind.Utc);
}

DateTimeKindAttribute looks like this:

/// <summary> Sets the DateTime.Kind value on DateTime and DateTime? members retrieved by Entity Framework. Sets Kind to DateTimeKind.Utc by default. </summary>
[AttributeUsage(AttributeTargets.Property)]
public class DateTimeKindAttribute : Attribute
{
    /// <summary> The DateTime.Kind value to set into the returned value. </summary>
    public readonly DateTimeKind Kind;

    /// <summary> Specifies the DateTime.Kind value to set on the returned DateTime value. </summary>
    /// <param name="kind"> The DateTime.Kind value to set on the returned DateTime value. </param>
    public DateTimeKindAttribute(DateTimeKind kind)
    {
        Kind = kind;
    }

    /// <summary> Event handler to connect to the ObjectContext.ObjectMaterialized event. </summary>
    /// <param name="entity"> The entity (POCO class) being materialized. </param>
    /// <param name="defaultKind"> [Optional] The Kind property to set on all DateTime objects by default. </param>
    public static void Apply(object entity, DateTimeKind? defaultKind = null)
    {
        if (entity == null) return;

        // Get the PropertyInfos for all of the DateTime and DateTime? properties on the entity
        var properties = entity.GetType().GetProperties()
            .Where(x => x.PropertyType == typeof(DateTime) || x.PropertyType == typeof(DateTime?));

        // For each DateTime or DateTime? property on the entity...
        foreach (var propInfo in properties) {
            // Initialization
            var kind = defaultKind;

            // Get the kind value from the [DateTimekind] attribute if it's present
            var kindAttr = propInfo.GetCustomAttribute<DateTimeKindAttribute>();
            if (kindAttr != null) kind = kindAttr.Kind;

            // Set the Kind property
            if (kind != null) {
                var dt = (propInfo.PropertyType == typeof(DateTime?))
                    ? (DateTime?)propInfo.GetValue(entity)
                    : (DateTime)propInfo.GetValue(entity);

                if (dt != null) propInfo.SetValue(entity, DateTime.SpecifyKind(dt.Value, kind.Value));
            }
        }
    }
}

Answer 4

This answer works with Entity Framework 6

The accepted answer does not work for Projected or Anonymous object. Performance could be a problem too.

To achieve this, we need to use a DbCommandInterceptor, an object provided by EntityFramework.

Create Interceptor:

public class UtcInterceptor : DbCommandInterceptor
{
    public override void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        base.ReaderExecuted(command, interceptionContext);

        if (interceptionContext?.Result != null && !(interceptionContext.Result is UtcDbDataReader))
        {
            interceptionContext.Result = new UtcDbDataReader(interceptionContext.Result);
        }
    }
}

interceptionContext.Result is DbDataReader, which we replace by ours

public class UtcDbDataReader : DbDataReader
{
    private readonly DbDataReader source;

    public UtcDbDataReader(DbDataReader source)
    {
        this.source = source;
    }

    public override DateTime GetDateTime(int ordinal)
    {
        return DateTime.SpecifyKind(source.GetDateTime(ordinal), DateTimeKind.Utc);
    }        

    // you need to fill all overrides. Just call the same method on source in all cases

    public new void Dispose()
    {
        source.Dispose();
    }

    public new IDataReader GetData(int ordinal)
    {
        return source.GetData(ordinal);
    }
}

Register the interceptor in your DbConfiguration

internal class MyDbConfiguration : DbConfiguration
{
    protected internal MyDbConfiguration ()
    {           
        AddInterceptor(new UtcInterceptor());
    }
}

Finally, register the configuration for on your DbContext

[DbConfigurationType(typeof(MyDbConfiguration ))]
internal class MyDbContext : DbContext
{
    // ...
}

That's it. Cheers.

For simplicity, here is the entire implementation of DbReader:

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.IO;
using System.Threading;
using System.Threading.Tasks;

namespace MyNameSpace
{
    /// <inheritdoc />
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1010:CollectionsShouldImplementGenericInterface")]
    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA1710:IdentifiersShouldHaveCorrectSuffix")]
    public class UtcDbDataReader : DbDataReader
    {
        private readonly DbDataReader source;

        public UtcDbDataReader(DbDataReader source)
        {
            this.source = source;
        }

        /// <inheritdoc />
        public override int VisibleFieldCount => source.VisibleFieldCount;

        /// <inheritdoc />
        public override int Depth => source.Depth;

        /// <inheritdoc />
        public override int FieldCount => source.FieldCount;

        /// <inheritdoc />
        public override bool HasRows => source.HasRows;

        /// <inheritdoc />
        public override bool IsClosed => source.IsClosed;

        /// <inheritdoc />
        public override int RecordsAffected => source.RecordsAffected;

        /// <inheritdoc />
        public override object this[string name] => source[name];

        /// <inheritdoc />
        public override object this[int ordinal] => source[ordinal];

        /// <inheritdoc />
        public override bool GetBoolean(int ordinal)
        {
            return source.GetBoolean(ordinal);
        }

        /// <inheritdoc />
        public override byte GetByte(int ordinal)
        {
            return source.GetByte(ordinal);
        }

        /// <inheritdoc />
        public override long GetBytes(int ordinal, long dataOffset, byte[] buffer, int bufferOffset, int length)
        {
            return source.GetBytes(ordinal, dataOffset, buffer, bufferOffset, length);
        }

        /// <inheritdoc />
        public override char GetChar(int ordinal)
        {
            return source.GetChar(ordinal);
        }

        /// <inheritdoc />
        public override long GetChars(int ordinal, long dataOffset, char[] buffer, int bufferOffset, int length)
        {
            return source.GetChars(ordinal, dataOffset, buffer, bufferOffset, length);
        }

        /// <inheritdoc />
        public override string GetDataTypeName(int ordinal)
        {
            return source.GetDataTypeName(ordinal);
        }

        /// <summary>
        /// Returns datetime with Utc kind
        /// </summary>
        public override DateTime GetDateTime(int ordinal)
        {
            return DateTime.SpecifyKind(source.GetDateTime(ordinal), DateTimeKind.Utc);
        }

        /// <inheritdoc />
        public override decimal GetDecimal(int ordinal)
        {
            return source.GetDecimal(ordinal);
        }

        /// <inheritdoc />
        public override double GetDouble(int ordinal)
        {
            return source.GetDouble(ordinal);
        }

        /// <inheritdoc />
        public override IEnumerator GetEnumerator()
        {
            return source.GetEnumerator();
        }

        /// <inheritdoc />
        public override Type GetFieldType(int ordinal)
        {
            return source.GetFieldType(ordinal);
        }

        /// <inheritdoc />
        public override float GetFloat(int ordinal)
        {
            return source.GetFloat(ordinal);
        }

        /// <inheritdoc />
        public override Guid GetGuid(int ordinal)
        {
            return source.GetGuid(ordinal);
        }

        /// <inheritdoc />
        public override short GetInt16(int ordinal)
        {
            return source.GetInt16(ordinal);
        }

        /// <inheritdoc />
        public override int GetInt32(int ordinal)
        {
            return source.GetInt32(ordinal);
        }

        /// <inheritdoc />
        public override long GetInt64(int ordinal)
        {
            return source.GetInt64(ordinal);
        }

        /// <inheritdoc />
        public override string GetName(int ordinal)
        {
            return source.GetName(ordinal);
        }

        /// <inheritdoc />
        public override int GetOrdinal(string name)
        {
            return source.GetOrdinal(name);
        }

        /// <inheritdoc />
        public override string GetString(int ordinal)
        {
            return source.GetString(ordinal);
        }

        /// <inheritdoc />
        public override object GetValue(int ordinal)
        {
            return source.GetValue(ordinal);
        }

        /// <inheritdoc />
        public override int GetValues(object[] values)
        {
            return source.GetValues(values);
        }

        /// <inheritdoc />
        public override bool IsDBNull(int ordinal)
        {
            return source.IsDBNull(ordinal);
        }

        /// <inheritdoc />
        public override bool NextResult()
        {
            return source.NextResult();
        }

        /// <inheritdoc />
        public override bool Read()
        {
            return source.Read();
        }

        /// <inheritdoc />
        public override void Close()
        {
            source.Close();
        }

        /// <inheritdoc />
        public override T GetFieldValue<T>(int ordinal)
        {
            return source.GetFieldValue<T>(ordinal);
        }

        /// <inheritdoc />
        public override Task<T> GetFieldValueAsync<T>(int ordinal, CancellationToken cancellationToken)
        {
            return source.GetFieldValueAsync<T>(ordinal, cancellationToken);
        }

        /// <inheritdoc />
        public override Type GetProviderSpecificFieldType(int ordinal)
        {
            return source.GetProviderSpecificFieldType(ordinal);
        }

        /// <inheritdoc />
        public override object GetProviderSpecificValue(int ordinal)
        {
            return source.GetProviderSpecificValue(ordinal);
        }

        /// <inheritdoc />
        public override int GetProviderSpecificValues(object[] values)
        {
            return source.GetProviderSpecificValues(values);
        }

        /// <inheritdoc />
        public override DataTable GetSchemaTable()
        {
            return source.GetSchemaTable();
        }

        /// <inheritdoc />
        public override Stream GetStream(int ordinal)
        {
            return source.GetStream(ordinal);
        }

        /// <inheritdoc />
        public override TextReader GetTextReader(int ordinal)
        {
            return source.GetTextReader(ordinal);
        }

        /// <inheritdoc />
        public override Task<bool> IsDBNullAsync(int ordinal, CancellationToken cancellationToken)
        {
            return source.IsDBNullAsync(ordinal, cancellationToken);
        }

        /// <inheritdoc />
        public override Task<bool> ReadAsync(CancellationToken cancellationToken)
        {
            return source.ReadAsync(cancellationToken);
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1063:ImplementIDisposableCorrectly")]
        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA1816:CallGCSuppressFinalizeCorrectly")]
        public new void Dispose()
        {
            source.Dispose();
        }

        public new IDataReader GetData(int ordinal)
        {
            return source.GetData(ordinal);
        }
    }
}

Answer 5

In EF Core 6.0 DbContext.ConfigureConventions() is added that can register a conversion for all properties of a specific type. (https://learn.microsoft.com/en-us/ef/core/modeling/bulk-configuration#pre-convention-configuration)

Here's the solution using ConfigureConventions:

protected override void ConfigureConventions(ModelConfigurationBuilder configurationBuilder)
{
    configurationBuilder
        .Properties<DateTime>()
        .HaveConversion(typeof(UtcValueConverter));
}

class UtcValueConverter : ValueConverter<DateTime, DateTime>
{
    public UtcValueConverter()
        : base(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc))
    {
    }
}

provided by https://github.com/dotnet/efcore/issues/4711#issuecomment-1048572602

Answer 6

Another year, another solution! This is for EF Core.

I have a lot of DATETIME2(7) columns that map to DateTime, and always store UTC. I don't want to store an offset because if my code is correct then the offset will always be zero.

Meanwhile I have other colums that store basic date-time values of unknown offset (provided by users), so they are just stored/displayed "as is", and not compared with anything.

Therefore I need a solution that I can apply to specific columns.

Define an extension method UsesUtc:

private static DateTime FromCodeToData(DateTime fromCode, string name)
    => fromCode.Kind == DateTimeKind.Utc ? fromCode : throw new InvalidOperationException($"Column {name} only accepts UTC date-time values");

private static DateTime FromDataToCode(DateTime fromData) 
    => fromData.Kind == DateTimeKind.Unspecified ? DateTime.SpecifyKind(fromData, DateTimeKind.Utc) : fromData.ToUniversalTime();

public static PropertyBuilder<DateTime?> UsesUtc(this PropertyBuilder<DateTime?> property)
{
    var name = property.Metadata.Name;
    return property.HasConversion<DateTime?>(
        fromCode => fromCode != null ? FromCodeToData(fromCode.Value, name) : default,
        fromData => fromData != null ? FromDataToCode(fromData.Value) : default
    );
}

public static PropertyBuilder<DateTime> UsesUtc(this PropertyBuilder<DateTime> property)
{
    var name = property.Metadata.Name;
    return property.HasConversion(fromCode => FromCodeToData(fromCode, name), fromData => FromDataToCode(fromData));
}

This can then be used on properties in model setup:

modelBuilder.Entity<CustomerProcessingJob>().Property(x => x.Started).UsesUtc();

It has the minor advantage over attributes that you can only apply it to properties of the correct type.

Note that it assumes values from the DB are in UTC but just have the wrong Kind. Therefore it polices the values you try to store in the DB, throwing a descriptive exception if they are not UTC.

Answer 7

I believe I've found a solution that doesn't require any custom UTC checking or DateTime manipulation.

Basically you need to change your EF entities to use DateTimeOffset (NOT DateTime) datatype. This will store the time zone with the date value in the database (SQL Server 2015 in my case).

When EF Core requests the data from the DB it will receive the timezone info as well. When you pass this data to a web application (Angular2 in my case) the date is automatically converted to the local timezone of the browser which is what I expect.

And when it is passed back to my server it is converted to UTC again automatically, also as expected.

Answer 8

I'm researching this right now, and most of these answers aren't exactly great. From what I can see, there's no way to tell EF6 that the dates coming out of the database are in UTC format. If that is the case, the simplest way to make sure your model's DateTime properties are in UTC would be to verify and convert in the setter.

Here's some c# like pseudocode which describes the algorithm

public DateTime MyUtcDateTime 
{    
    get 
    {        
        return _myUtcDateTime;        
    }
    set
    {   
        if(value.Kind == DateTimeKind.Utc)      
            _myUtcDateTime = value;            
        else if (value.Kind == DateTimeKind.Local)         
            _myUtcDateTime = value.ToUniversalTime();
        else 
            _myUtcDateTime = DateTime.SpecifyKind(value, DateTimeKind.Utc);        
    }    
}

The first two branches are obvious. The last holds the secret sauce.

When EF6 creates a model from data loaded from the database, DateTimes are DateTimeKind.Unspecified. If you know your dates are all UTC in the db, then the last branch will work great for you.

DateTime.Now is always DateTimeKind.Local, so the above algorithm works fine for dates generated in code. Most of the time.

You have to be cautious, however, as there are other ways DateTimeKind.Unspecified can sneak into your code. For example, you might deserialize your models from JSON data, and your deserializer flavor defaults to this kind. It's up to you to guard against localized dates marked DateTimeKind.Unspecified from getting to that setter from anybody but EF.

Answer 9

Credits to @ajcvickers. Starting in EF Core 2.1, this would be one way to deal with DateTime.Kind:

modelBuilder
    .Entity<Foo>()
    .Property(e => e.SomeDate)
    .HasConversion(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

This will ensure every time the date is read from the database it is specified as Utc automatically.

Source:

https://github.com/dotnet/efcore/issues/4711#issuecomment-358695190

Answer 10

There is no way to specify the DataTimeKind in the Entity Framework. You may decide to convert the date time values to utc before storing to db and always assume the data retrived from db as UTC. But the DateTime objects materalized during query will always be "Unspecified". You could also evalualte using DateTimeOffset object instead of DateTime.

Answer 11

If you are careful to properly pass in UTC dates when you set the values and all you care about is making sure the DateTimeKind is set properly when the entities are retrieved from the database, see my answer here: https://stackoverflow.com/a/9386364/279590

Answer 12

Matt Johnson-Pint's solution works, but if all your DateTimes are supposed to be UTC, creating an attribute would be too circuitous. Here is how I simplified it:

public class MyContext : DbContext
{
    public DbSet<Foo> Foos { get; set; }

    public MyContext()
    {
        ((IObjectContextAdapter)this).ObjectContext.ObjectMaterialized +=
            (sender, e) => SetDateTimesToUtc(e.Entity);
    }

    private static void SetDateTimesToUtc(object entity)
    {
        if (entity == null)
        {
            return;
        }

        var properties = entity.GetType().GetProperties();
        foreach (var property in properties)
        {
            if (property.PropertyType == typeof(DateTime))
            {
                property.SetValue(entity, DateTime.SpecifyKind((DateTime)property.GetValue(entity), DateTimeKind.Utc));
            }
            else if (property.PropertyType == typeof(DateTime?))
            {
                var value = (DateTime?)property.GetValue(entity);
                if (value.HasValue)
                {
                    property.SetValue(entity, DateTime.SpecifyKind(value.Value, DateTimeKind.Utc));
                }
            }
        }
    }
}

Answer 13

For those who need to achieve @MattJohnson solution with .net framework 4 like me, with reflection syntax/method limitation , it require a little bit modification as listed below:

     foreach (var property in properties)
        {     

            DateTimeKindAttribute attr  = (DateTimeKindAttribute) Attribute.GetCustomAttribute(property, typeof(DateTimeKindAttribute));

            if (attr == null)
                continue;

            var dt = property.PropertyType == typeof(DateTime?)
                ? (DateTime?)property.GetValue(entity,null)
                : (DateTime)property.GetValue(entity, null);

            if (dt == null)
                continue;

            //If the value is not null set the appropriate DateTimeKind;
            property.SetValue(entity, DateTime.SpecifyKind(dt.Value, attr.Kind) ,null);
        }  

Answer 14

The solutions here are useful, but I expect many are coming to this with the problem that they want all of their datetimes available in local timezone, but they want it translated so that the persisted version is saved UTC.

There are 3 challenges to implement this:

1. Reading the data as UTC and converting to Local
2. Adjusting query parameters e.g. SELECT * From PRODUCT where SALEDATE< @1
3. Storing data which is LocalTime as UTC

1. Reading the data as UTC and converting to Local

In this case, the above solution based on the work of Ivan Stoev DateTime.Kind set to unspecified, not UTC, upon loading from database will do what you need.

2. Adjusting query parameters

Similarly to Ivan's solution for the interceptor, you can utilise the ReaderExecuting interceptor. Bonus is that this is far easier to implement than ReaderExecuted.

    public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        foreach (DbParameter dbParameter in command.Parameters)
        {
            if (dbParameter.Value is DateTime dtLocal)
            {
                if (dtLocal.Kind != DateTimeKind.Utc)
                {
                    dbParameter.Value = dtLocal.ToUniversalTime();
                }
            }
        }
        base.ReaderExecuting(command, interceptionContext);
    }

3. Storing data which is LocalTime as UTC

While there are query interceptors that appear like they would assist here, they are called multiple times and created unexpected results. The best solution I came up with was to override SaveChanges

    public override int SaveChanges()
    {
        UpdateCommonProperties();
        UpdateDatesToUtc();
        bool saveFailed;
        do
        {
            saveFailed = false;
            try
            {
                var result = base.SaveChanges();
                return result;
            }
            catch (DbUpdateConcurrencyException ex)
            {
                saveFailed = ConcurrencyExceptionHandler(ex);
            }

        } while (saveFailed);
        return 0;
    }

    private void UpdateDatesToUtc()
    {
        if (!ChangeTracker.HasChanges()) return;

        var modifiedEntries = ChangeTracker.Entries().Where(x => (x.State == EntityState.Added || x.State == EntityState.Modified));

        foreach (var entry in modifiedEntries)
        {
            entry.ModifyTypes<DateTime>(ConvertToUtc);
            entry.ModifyTypes<DateTime?>(ConvertToUtc);
        }
    }

    private static DateTime ConvertToUtc(DateTime dt)
    {
        if (dt.Kind == DateTimeKind.Utc) return dt;
        return dt.ToUniversalTime();
    }

    private static DateTime? ConvertToUtc(DateTime? dt)
    {
        if (dt?.Kind == DateTimeKind.Utc) return dt;
        return dt?.ToUniversalTime();
    }

And the extension is (based on response by Talon https://stackoverflow.com/a/39974362/618660

public static class TypeReflectionExtension
{
    static Dictionary<Type, PropertyInfo[]> PropertyInfoCache = new Dictionary<Type, PropertyInfo[]>();

    static void TypeReflectionHelper()
    {
        PropertyInfoCache = new Dictionary<Type, PropertyInfo[]>();
    }

    public static PropertyInfo[] GetTypeProperties(this Type type)
    {
        if (!PropertyInfoCache.ContainsKey(type))
        {
            PropertyInfoCache[type] = type.GetProperties();
        }
        return PropertyInfoCache[type];
    }

    public static void ModifyTypes<T>(this DbEntityEntry dbEntityEntry, Func<T, T> method)
    {
        foreach (var propertyInfo in dbEntityEntry.Entity.GetType().GetTypeProperties().Where(p => p.PropertyType == typeof(T) && p.CanWrite))
        {
            propertyInfo.SetValue(dbEntityEntry.Entity, method(dbEntityEntry.CurrentValues.GetValue<T>(propertyInfo.Name)));
        }
    }
}

Answer 15

To define DateTimeKind for entity properties in the Entity Framework Core, I propose the following class:

/// <summary>
/// Класс для преобразования DateTimeKind у сущностей в EF
/// </summary>
public static class DateTimeKindAnnotation
{
    private const string DateTimeKindAnnotation = "DateTimeKind";

    private static readonly ValueConverter<DateTime, DateTime> UtcConverter =
        new ValueConverter<DateTime, DateTime>(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Utc));

    private static readonly ValueConverter<DateTime, DateTime> LocalConverter =
        new ValueConverter<DateTime, DateTime>(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Local));

    private static readonly ValueConverter<DateTime, DateTime> UnspecifiedConverter =
        new ValueConverter<DateTime, DateTime>(v => v, v => DateTime.SpecifyKind(v, DateTimeKind.Unspecified));

    /// <summary>
    /// Данное свойство будет иметь заданный DateTimeKind в EF
    /// </summary>
    /// <typeparam name="TProperty"></typeparam>
    /// <param name="builder"></param>
    /// <param name="kind"></param>
    public static PropertyBuilder<DateTime> HasDateTimeKind(this PropertyBuilder<DateTime> builder, DateTimeKind kind) =>
        builder.HasAnnotation(DateTimeKindAnnotation, kind);

    /// <summary>
    /// Данное свойство будет иметь заданный DateTimeKind в EF
    /// </summary>
    /// <typeparam name="TProperty"></typeparam>
    /// <param name="builder"></param>
    /// <param name="kind"></param>
    public static PropertyBuilder<DateTime?> HasDateTimeKind(this PropertyBuilder<DateTime?> builder, DateTimeKind kind) =>
        builder.HasAnnotation(DateTimeKindAnnotation, kind);

    public static DateTimeKind? FindDateTimeKind(this IMutableProperty property)
    {
        var attribute = property.PropertyInfo.GetCustomAttribute<DateTimeKindAttribute>();
        if (attribute is not null)
        {
            return attribute.Kind;
        }
        return (DateTimeKind?)property.FindAnnotation(DateTimeKindAnnotation)?.Value;
    }

    /// <summary>
    /// Преобразует DateTimeKind у всех сущностей в EF к значению по умолчанию, заданному через атрибут или анотацию.
    /// </summary>
    /// <remarks>Убедитесь, что это вызывается после настройки всех ваших сущностей.</remarks>
    /// <param name="builder"></param>
    /// <param name="defaultKind">DateTimeKind, который надо использовать по умолчанию.</param>
    public static void ApplyDateTimeKindConverter(this ModelBuilder builder, DateTimeKind defaultKind = DateTimeKind.Utc)
    {
        foreach (var entityType in builder.Model.GetEntityTypes())
        {
            foreach (var property in entityType.GetProperties())
            {
                if (property.ClrType == typeof(DateTime) || property.ClrType == typeof(DateTime?))
                {
                    DateTimeKind kind = property.FindDateTimeKind() ?? defaultKind;
                    switch (kind)
                    {
                        case DateTimeKind.Utc:
                            property.SetValueConverter(UtcConverter);
                            break;
                        case DateTimeKind.Local:
                            property.SetValueConverter(LocalConverter);
                            break;
                        case DateTimeKind.Unspecified:
                            property.SetValueConverter(UnspecifiedConverter);
                            break;
                        default:
                            throw new NotSupportedException($"Kind \"{kind}\" неподдерживается");
                    }
                }
            }
        }
    }
}

/// <summary>
/// Задает тот DateTimeKind, который будет применяться EF для поля сущности.
/// </summary>
public class DateTimeKindAttribute : Attribute
{
    public DateTimeKindAttribute(DateTimeKind kind) => Kind = kind;
    public DateTimeKind Kind { get; }
}

For its simplest application, you need to add a call to ApplyDateTimeKindConverter () at the end of OnModelCreating. By default, all Datetime fields are assigned a single kind Utc. Exceptions can be implemented via the DateTimeKindAttribute attribute or the fluentapi method HasDateTimeKind (DateTimeKind.)

Answer 16

Expanded on the previous solution for EF Dotnet Core put code that works for me.

 public partial class XxxxxxDataContext {
    partial void CustomizeMapping(ref ModelBuilder modelBuilder) {
      var dateTimeConverter = new ValueConverter<DateTime, DateTime>(
        v => v.ToUniversalTime(),
        (v) => v.Kind == DateTimeKind.Utc?DateTime.SpecifyKind(v, DateTimeKind.Utc):TimeZoneInfo.ConvertTimeToUtc(v, TimeZoneInfo.Local));

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

      foreach (var entityType in modelBuilder.Model.GetEntityTypes()) {
        if (entityType.IsKeyless) {
          continue;
        }

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

Answer 17

Another approach would be to create an interface with the datetime properties, implement them on the partial entity classes. And then use the SavingChanges event to check if the object is of the interface type, set those datetime values to whatever you want. In fact, if these are createdon/modifiedon kind of dates, you can use that event to populate them.

Answer 18

In case you cannot convert the DataBase time from Local time to UTC, there is a workaround using AutoMapper and instead of SpecifyKind():

            CreateMap<DateTime, Google.Protobuf.WellKnownTypes.Timestamp>()
            .ConvertUsing(x => 
            Google.Protobuf.WellKnownTypes.Timestamp
            .FromDateTime(DateTime.SpecifyKind(x, DateTimeKind.Utc)));

Use ToUniversalTime():

            CreateMap<DateTime, Google.Protobuf.WellKnownTypes.Timestamp>()
                .ConvertUsing(x => 
                Google.Protobuf.WellKnownTypes.Timestamp.FromDateTime(
                    (x).ToUniversalTime()));

Or vice-versa is case if one doesn't work for you

Answer 19

In my case, I had only one table with UTC datetimes. Here's what I did:

public partial class MyEntity
{
    protected override void OnPropertyChanged(string property)
    {
        base.OnPropertyChanged(property);            

        // ensure that values coming from database are set as UTC
        // watch out for property name changes!
        switch (property)
        {
            case "TransferDeadlineUTC":
                if (TransferDeadlineUTC.Kind == DateTimeKind.Unspecified)
                    TransferDeadlineUTC = DateTime.SpecifyKind(TransferDeadlineUTC, DateTimeKind.Utc);
                break;
            case "ProcessingDeadlineUTC":
                if (ProcessingDeadlineUTC.Kind == DateTimeKind.Unspecified)
                    ProcessingDeadlineUTC = DateTime.SpecifyKind(ProcessingDeadlineUTC, DateTimeKind.Utc);
            default:
                break;
        }
    }
}