I have been working on a solution to upgrade our current application to support timezones properly. The original idea of "everything is in our own timezone" is not panning out as well as expected for an app that has things that start and end at specific times for people all over the world. I need to add that our system consists of a few Azure Web App instances distributed in different timezones.
Our application is written in .Net Core 6 LTS and we use EFCore 6.0.3. Most of our entities have multiple nested levels so a solution of 'just change them out upon load' wont work. The objects are very complicated.
I've already written a decent ISaveChangesInterceptor that looks for changed entries of type DateTime / Datetime? and converts them to UTC to store in the db. This is acceptable, since it is our 'admin' section and its ok to look up the user's stored TimeZoneInfo.Id upon save, since generally it happens once per button press.
So now, I'm working on the part that converts back from UTC to the user's local timezone. I've started writing a DbCommandInterceptor with an override for GetDateTime but I'm finding that this code runs many times for each object and it is not a good idea to attempt to pull the user's data that many times.
I have some ideas to fix this but they all feel like bandaids...
- Store the users data in memory somehow
- Somehow pass the user into the
CommandExecutedEventDatabut I have no idea how - Load user once, and use memorycache for all followup queries
- some other idea that i haven't thought up / i'm not even sure the concept of "user" exists all the way down in a Data Reader
Most of the solutions on SO so far explain how to do this but they are either outdated (EntityFramework 6) or they are relying on the server local datetime which again, wont work for us since someone in Saudi Arabia might be connecting to an Azure server in who knows where.
Edit: Also I'd like to minimize changes to the DB, and currently all 'time' fields are datetime2.
My code is based heavily on what I learned here:
- DateTime.Kind set to unspecified, not UTC, upon loading from database
- Entity Framework DateTime and UTC
Obligatory Code. Notice where I suspect I need to convert back to local where I commented Get user info somehow here
public class UtcInterceptor : DbCommandInterceptor
{
public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result)
{
if (eventData.Context != null)
{
if (result is not UtcDateTimeConvertingDbDataReader)
{
result = new UtcDateTimeConvertingDbDataReader(result);
}
}
return base.ReaderExecuted(command, eventData, result);
}
public override ValueTask<DbDataReader> ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result,
CancellationToken cancellationToken = new CancellationToken())
{
if (eventData.Context != null)
{
if (result is not UtcDateTimeConvertingDbDataReader)
{
result = new UtcDateTimeConvertingDbDataReader(result);
}
}
return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);
}
}
class UtcDateTimeConvertingDbDataReader : DelegatingDbDataReader
{
public UtcDateTimeConvertingDbDataReader(DbDataReader source) : base(source) { }
public override DateTime GetDateTime(int ordinal)
{
// Get user info somehow here
return DateTime.SpecifyKind(base.GetDateTime(ordinal), DateTimeKind.Utc);
}
}
public class DelegatingDbDataReader : DbDataReader
{
// Since DbDataReader is an irritating override of an abstract class,
// I have to do something like this below to actually create a way to specify datetime kind is utc
private readonly DbDataReader source;
public DelegatingDbDataReader(DbDataReader source)
{
this.source = source;
}
public override object this[string name] { get { return source[name]; } }
public override object this[int ordinal] { get { return source[ordinal]; } }
/// truncated for brevity
