Implementation of UTC DateTime

Topics: EF Runtime, General
Nov 8, 2013 at 8:51 AM
Hi,

It could be interresting to force the storage of DateTime in UTC format in Database, specificly for Azure. So the entity datetime property always use the local datetime.

This will avoid going through this method with all datetime properties : Convert value when mapping
Developer
Nov 8, 2013 at 5:24 PM
DateTime type is problematic because it can be of Unspecified kind which can be interpreted as local or UTC if you decide to convert it (or not interpreted at all if you don't convert it). Moreover the SQL Server types DateTime maps to are not time-zone aware too which again results in materializing values to DateTime instances of Unspecified so that the user can interpret them the way they think is correct. In general I would advise using DateTimeOffset type instead of DateTime since it is aware of time zone concepts so there is no ambiguity about how to interpret the value - for more details see notes in the msdn documentation on DateType type and my thoughts on this. Also, note that changing the current EF behavior would be a huge breaking change which would break many existing apps that currently use DateTime type. Applications that currently convert the values to local/utc time zone and that use the raw value would be both most likely broken.
Nov 13, 2013 at 12:19 AM
It would be useful to be able to specify a default DateTime.Kind for materializing objects...
// e.g. 
someEfConfig.DefaultDateTimeKind = DateTimeKind.Utc;
If this is "DateTimeKind.Unspecified" by default then the backwards compatibility issue is covered (well, I think so as a non-EF dev!)
Developer
Nov 13, 2013 at 1:45 AM
Maybe. I think you could also achieve this by listening to the ObjectContext.ObjectMaterialized event and fixing up the entity accordingly.
Nov 13, 2013 at 3:40 AM
moozzyk wrote:
Maybe. I think you could also achieve this by listening to the ObjectContext.ObjectMaterialized event and fixing up the entity accordingly.
Yes - that's what we are looking at. In short reflecting over properties and applying the Kind as Utc... Just feels a bit wrong as it's just been hydrated!!

:)
May 26 at 1:39 PM
moozzyk wrote:
Maybe. I think you could also achieve this by listening to the ObjectContext.ObjectMaterialized event and fixing up the entity accordingly.
When you access the IObjectContextAdapter.ObjectContext property, EF calls an internal ForceOSpaceLoadingForKnownEntityTypes method, which is very slow to execute. Wiring up the event in your context constructor turns your context from light-weight into super-heavy, which is very undesirable.
Jun 19 at 1:32 PM
How is DateTimeOffset handled on database engines other than newer SQL Server versions?

I have worked around this issue using ObjectMaterialized and SavingChanges and I'm sure the performance impact is big.

I understand why this is not an easy thing to solve, with DateTimeKind.Unspecified in the mix.

I suggest making it possible to configure EF to run in 2 modes, one being the way it work now, the other, a more globalization friendly way. Let me explain.

Let's assume all dates in the database are stored as UTC, when EF retrives the dates, they will be created as DateTimeKind.Utc. That leaves the challenge of actually storing dates as UTC. Storing DateTime objects marked as DateTimeKind.Utc is easy, DateTimeKind.Local too, they just required a call to the ToUniversalTime method. That leaves DateTimeKind.Unspecified DateTime objects. Why not let the developer make sure none of these exists, and if they do, that's just too bad, they will be handled like DateTimeKind.Local. It will be a known defect.

Your application will need to create local DateTime objects, and any custom SQL you might have running, will need to store UTC dates too.

This will at least make it possible to work with time-zones in our applications.
Jun 24 at 3:22 PM
Using ObjectMaterialized works fine, but I too am struggling with the expense of the IObjectContextAdapter.ObjectContext getter. Is there something we can do to fix that or some other workaround?
Developer
Jul 14 at 5:41 PM
@brantheman DbContext is lazy in that when you create an instance it does very little work. It is when you use it the first time to add an entity, perform a query, etc., that it actually does its initialization. The cost of that initialization is something that we have been working to improve. Accessing the ObjectContext causes that initialization to happen, and it is the same initialization that would happen if you used the context in any other way. This means that if the context is going to be used for something then the overall cost does not increase by using IObjectContextAdapter.ObjectContext. But if the context does not ultimately get used (which can be common depending on architecture) then using IObjectContextAdapter.ObjectContext every time can add significant cost. So, the way to avoid this is to either not create a context instance until you know it will be used, or to not use IObjectContextAdapter.ObjectContext on the context until you know the context is about to be used.

Thanks,
Arthur
Coordinator
Jul 14 at 8:32 PM
@brantheman: Just to add to what Arthur said, an idea that we have discussed is to have a first-class ObjectMaterialized-like event on DbContext itself. Subscribing to such event would not trigger initialization but once initialization happens the event would get triggered in the same way ObjectMaterialized is triggered on ObjectContext. At this point we don't have plans to prioritize this feature in our team but if someone from the community wanted to contribute it we would be happy to consider it.

Hope this helps,
Diego