Entity Framework always save DateTimeOffset as UTC

Question

Is there a way that I can instruct Entity Framework to always store DateTimeOffset as the UTC value. Technically there is no reason for this but I prefer it if all the data in my database is consistent.

Currently it is storing the DateTimeOffset as received from the client which could be anything depending on the users locale.

I was thinking of perhaps intercepting all the SaveChanges method, looping through the changes, looking for DateTimeOffset types and doing the conversion explicitly, but this seems like a lot of work which could be mitigated if EntityFramework has something built in.

Any insight or suggestions would be greatly appreciated.

EDIT: The difference between my post and others dealing with this topic is that my C# property is of type DateTimeOffset and my database field is also DateTimeOffset(0). There is not technical reason to convert other than consistency. If I store a value as "17:00 +5:00" or "10:00 -2:00" it doesn't matter, those are both identical moments in time. However, I would like to have all my dates stored as "12:00 +0:00" so that it is easier to read in SSMS or other DB tools.

EDIT 2: Seeing as there doesn't seem to be "simple" way to do this I am looking to now tackle the problem in the DbContext.SaveChanges() function. The problem is that no matter what I do, the value won't change? Here's the code so far:

public new void SaveChanges()
{
    foreach (var entry in this.ChangeTracker.Entries())
    {
        foreach (var propertyInfo in entry.Entity.GetType().GetProperties().Where(p => p.CanWrite && p.PropertyType == typeof(DateTimeOffset)))
        {
            var value = entry.CurrentValues.GetValue<DateTimeOffset>(propertyInfo.Name);

            var universalTime = value.ToUniversalTime();
            entry.CurrentValues[propertyInfo.Name] = universalTime;
            // entry.Property(propertyInfo.Name).CurrentValue = universalTime;

            // Adding a watch to "entry.CurrentValues[propertyInfo.Name]" reveals that it has not changed??
        }

        // TODO: DateTimeOffset?
    }

    base.SaveChanges();
}

EDIT 3 I finally managed to solve this, see answer below

Chercherbourg

Do you mean store all as UTC+0? How will you convert it back when you need to display the users locale format? Consider also timezone adjustments like BST – Helmer

@Helmer That is exactly the reason why the storing in UTC makes sense. Yes, you would need the local format/timezone, but it would work globally, instead of only in the timezone where the datetime was entered. – Burks

Possible duplicate of Can Entity Framework automatically convert datetime fields to UTC on save? – Lynettelynn

@Lynettelynn that post is helpful, but it's deals with datetime vs datetimeoffset. I am universaally dealing with datetimeoffset in both code and db – Chercherbourg

@Burks there is no technical reason to convert. Whether the database has "17:00 +2:00" or "14:00 -1:00" makes no difference. Both of them are the same. For me its about consistency. – Chercherbourg

Database tools have no problem dealing with datetimeoffset. Why do you think you need to convert the value and lose the original offset information? There is no consistency to maintain here, just data loss. – Hasan

@PanagiotisKanavos The original timezone has no value to me. My issue is when looking at the database, it makes it more difficult to read (from a human perspective, not machine). – Chercherbourg

I'm repeating my comment here since my answer does not apply and I've deleted it: @Chercherbourg have you tried setting the actual property of the entity (using something like: propertyInfo.SetValue(entry.Entity, universalTime))?. I don't think I've ever tried mangling with CurrentValues – Jacobin

What's the point of using datetimeoffset if you discard the offset then? Furthermore, from a human/developer perspective, something that magically changes values and discards data is not appreciated. It's far cleaner to change the offset when setting the property, not behind scenes – Hasan

BTW, EF may discard the new value simply because the two values are equal. A property is marked as change only if the new value is different. You'd have to actually change the value, eg by adding a millisecond – Hasan

@PanagiotisKanavos Check this out prnt.sc/csffv0 , that is what I want to avoid. The TimeZone is stored to accommodate systems in different timezones. I have managed to solve my issue. Thanks so much for taking time to help! – Chercherbourg

Not, the offset is not there to accommodate systems with different timezones. It's there to allow you to store your departure and arrival time from one coast to the other, without altering the airline's posted times. That's why the request is very, very counterintuitive – Hasan

I have to agree with @PanagiotisKanavos on this. I don't see the point of storing all your DateTimeOffset in the UTC timezone other than "it looks prettier on SSMS". If you need to show them in a specific timezone, convert them for display (in your application), not for storing. – Jacobin

@PanagiotisKanavos if I used DateTime instead, the TimeZone would be assumed to be that of the server making the request to the database. Articles come in from around the world, the publish date is set by the journalist in their own timezone. The server then does a "getNews where publishedDate < now". Without a configured Offset, that publishedDate becomes ambiguous. Of course I could make sure that I always save the DateTime as UtcTime and pass it off as a system rule. But by using DateTimeOffset I eliminate the need for that rule as there is no ambiguity. – Chercherbourg

@Jacobin if there was only one system connecting to this database then we could store as DateTime and the conversions could be done by the code assuming a specific TimeZone (UTC). But to future proof the system, make it portable (move to a new server in another timezone) and allow for other applications to connect to the database, we need to make the data completely unambiguous. – Chercherbourg

@Chercherbourg the time offset in the datetimeoffset fields in SQL Server at least, is stored, indexed, and compared in UTC always. The offset is just there for retrieval (so that you know which timeoffset the data was entered in). Comparisons are always made in UTC – Jacobin

Actually the MSDN puts it in the same terms: The data is stored in the database and processed, compared, sorted, and indexed in the server as in UTC. The time zone offset will be preserved in the database for retrieval. . Basically, it doesn't matter for anything other than "display" – Jacobin

Again, depending on your design decisions, if you are playing with multiple time zones (and time zones != time offsets), I'd probably just store the user's time zone separately (from a date and time zone you can retrieve the offset: from an offset you can't retrieve the time zone) – Jacobin

@Jacobin apologies, I have used TimeZone and Offset interchangeably so this may be the confusion. The Offset is what I am referring too. So I want everything in the database converted to +0 for readability. the original Offset has no value to the system once it has been converted to +0 – Chercherbourg

Ok, if the offset is of no use in your application, and you want a "cleaner" look... that's ok, but I'd not happily disregard it unless you have made sure you know there are no consequences and there won't be in the future (which is always hard to predict :-) ). Specially if you are modifying potential user input on entities before saving. Again: those are design decisions you need to make :-) – Jacobin

Let us continue this discussion in chat. – Chercherbourg

Answer 1

Thanks to the help I have received. I finally managed to get to the answer thanks to Jcl

public new void SaveChanges()
{
    foreach (var entry in this.ChangeTracker.Entries().Where(p => p.State == EntityState.Added || p.State == EntityState.Modified))
        {
        foreach (var propertyInfo in entry.Entity.GetType().GetTypeProperties().Where(p => p.PropertyType == typeof(DateTimeOffset)))
        {
            propertyInfo.SetValue(entry.Entity, entry.CurrentValues.GetValue<DateTimeOffset>(propertyInfo.Name).ToUniversalTime());
        }
        foreach (var propertyInfo in entry.Entity.GetType().GetTypeProperties().Where(p => p.PropertyType == typeof(DateTimeOffset?)))
        {
            var dateTimeOffset = entry.CurrentValues.GetValue<DateTimeOffset?>(propertyInfo.Name);
            if (dateTimeOffset != null) propertyInfo.SetValue(entry.Entity, dateTimeOffset.Value.ToUniversalTime());
        }
    }

     base.SaveChanges();
}

EDIT:

At the advice of Jcl, I wrote a small helper to cache the reflection:

public static class TypeReflectionExtension
{
    public static Dictionary<Type, PropertyInfo[]> PropertyInfoCache;

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

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

EDIT 2: To clean up the code a bit I created a generic function which you can use to intercept and modify any Data Type:

public static class DbEntityEntryExtension
{
    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)));
        }
    }
}

The usage of this is (Inside your DbContext):

public new void SaveChanges()
{
    foreach (var entry in this.ChangeTracker.Entries().Where(p => p.State == EntityState.Added || p.State == EntityState.Modified))
    {
        entry.ModifyTypes<DateTimeOffset>(ConvertToUtc);
        entry.ModifyTypes<DateTimeOffset?>(ConvertToUtc);
    }

    base.SaveChanges();
}

private static DateTimeOffset ConvertToUtc(DateTimeOffset dateTimeOffset)
{
    return dateTimeOffset.ToUniversalTime();
}

private static DateTimeOffset? ConvertToUtc(DateTimeOffset? dateTimeOffset)
{
    return dateTimeOffset?.ToUniversalTime();
}

Disclaimer This does solve my problem but is not recommended. I don't even know if I am going to use this as something about it just feels wrong now (Thanks Jcl ;). So please do use this with caution. It may probably be better that you follow the answer by Adam Benson

Chercherbourg

Make sure you cache all those calls to GetType().GetProperties(): they are VERY slow (you can make any kind of caching solution that correlates Type and PropertyName with the actual PropertyInfo) – Jacobin

@Jacobin I will look into that right now, all this trouble just for readability is making me have second thoughts, but in the name of experience I'm going to persist. – Chercherbourg

in all honesty, if I was to do this (which I wouldn't), I'd also store the timezone the user put the datetime in (and I mean the actual timezone, which would be better if you don't need performance than just storing the offset): I was just trying to help you with your question; your design decisions are a whole different story :-) Good luck! – Jacobin

@Jacobin you have me concerned about the performance now. I need to chat to my team to see if we can come up with alternatives or if we should just suck up the readability and leave the offset alone. – Chercherbourg

A simple Dictionary<Type, Dictionary<string, PropertyInfo>> would work as a simple cache for those reflection calls unless you have more advanced requirements (memory pressure cleaning, lazy evaluation, and whatnot). PropertyInfo.SetValue is not extremely fast, but it's not all that slow either – Jacobin