Jaylen Jaylen - 3 months ago 34
C# Question

Can Entity Framework automatically convert datetime fields to UTC on save?

I am writing an application using ASP.NET MVC 5. All of my datetime that I am going to store in the database must be converted from local time zone to UTC timezone first.

I am not sure where is the best place to do during the request cycle.

I can convert each field to UTC in the controller after it passes the ViewModel rules. But I feel this is too much code/step that I have to do.

Is there a way in Entity Framework that will allow me to add a rule that automatically converts datetime to UTC before saving the changes?

This is the method that I wrote that will convert a giving datetime to UTC

public static DateTime? ConvertToUTC(DateTime? val, TimeZoneInfo destinationTimeZone = null)
{
if (val != null)
{
var dateTimeToConvert = ConvertToDateTime(val);

if (destinationTimeZone == null)
{
if (ConfigurationManager.AppSettings["LocalTimeZone"] != null)
{
destinationTimeZone = TimeZoneInfo.FindSystemTimeZoneById(ConfigurationManager.AppSettings["LocalTimeZone"]);
}
else
{
destinationTimeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZone.CurrentTimeZone.StandardName);
}
}
return TimeZoneInfo.ConvertTimeFromUtc(dateTimeToConvert, destinationTimeZone);

}

return null;
}

Answer

Is there a way in Entity Framework that will allow me to add a rule that automatically converts datetime to UTC before saving the changes?

You can override the SaveChanges / SaveChangesAsync methods and execute conversion before those get called on the DbContext.

// on your DbContext type

public override int SaveChanges(){
    DateTimeUtcHelper.SetDatesToUtc(this.ChangeTracker.Entries());
    return base.SaveChanges();
}

class DateTimeUtcHelper{
    internal static void SetDatesToUtc(IEnumerable<DbEntityEntry> changes) {
        foreach (var dbEntry in changes.Where(x => ChangesToTrackList.Contains(new[] {EntityState.Added, EntityState.Modified}))){
            foreach (var propertyName in dbEntry.CurrentValues.PropertyNames){

                // using reflection add logic to determine if its a DateTime or nullable DateTime
                // && if its kind = DateTimeKind.Local or Unspecified
                // and then convert set the Utc value
                // and write it back to the entry using dbEntry.CurrentValues[propertyName] = utcvalue;

            }
        }
    }
}

You would need to add your own logic to ensure there are not instances where you are changing the DateTime on a change that did not come from a user (ie. make sure that by default your materialized DateTimes have the DateTimeKind.Utc flag set or .Local if you change it for the user).


Edit

The DbContext would also need access to the timezone on the users profile for that Request context that it is running in. Perhaps an overload of SaveChanges would be better suited so you can pass it in OR if you are using DI perhaps you could inject it into the created DbContext.