How we survive using a local time zone with Breeze

Question

I'm writing this to gather comments on our approaches and hopefully help someone else (and my memory).

Scenario

• All of our databases use DateTime data types with no time zone information.
• Internally we know that all of the dates/times in our databases are in local (New Zealand) time, not UTC. For a web application this is not ideal but we don't control the design of all of these databases as they support other systems (accounting, payroll, etc).
• We are using Entity Framework (model first) for data access.

Our problem

• Without specific time zone information the Breeze / Web Api / Entity Framework stack seems to favour the assumption that times are UTC, not local, which is probably for the best but doesn't suit our application(s).
• Breeze likes to pass dates back to the server in standard UTC format, particularly in query strings (eg where clauses). Imagine a Breeze controller that directly exposes a table from the database as an IQueryable. The Breeze client will pass any date filter (where) clauses to the server in UTC format. Entity Framework will faithfully use those dates to create a SQL query, completely unaware that the database table dates are in our local time zone. For us that means that the results are somewhere between 12 to 13 hours offset from the ones we want (depending on daylight savings).

Our objective is to ensure that our server side code (and the database) consistently uses dates in our local time zone, and that all queries return the desired results.

Answer 1

Our solution part 1: Entity Framework

When Entity Framework gets DateTime values from the database it sets them to DateTimeKind.Unspecified. In other words, neither local or UTC. We specifically wanted to mark our dates as DateTimeKind.Local.

To achieve this we decided to tweak Entity Framework's template that generates the entity classes. Instead of our dates being a simple property, we introduced a backing-store date and used a property setter to make the date Local if it was Unspecified.

In the template (.tt file) we replaced...

public string Property(EdmProperty edmProperty)
{
    return string.Format(
        CultureInfo.InvariantCulture,
        "{0} {1} {2} {{ {3}get; {4}set; }}",
        Accessibility.ForProperty(edmProperty),
        _typeMapper.GetTypeName(edmProperty.TypeUsage),
        _code.Escape(edmProperty),
        _code.SpaceAfter(Accessibility.ForGetter(edmProperty)),
        _code.SpaceAfter(Accessibility.ForSetter(edmProperty)));
}

... with ...

public string Property(EdmProperty edmProperty)
{
    // Customised DateTime property handler to default DateKind to local time
    if (_typeMapper.GetTypeName(edmProperty.TypeUsage).Contains("DateTime")) {
        return string.Format(
            CultureInfo.InvariantCulture,
            "private {1} _{2}; {0} {1} {2} {{ {3}get {{ return _{2}; }} {4}set {{ _{2} = DateKindHelper.DefaultToLocal(value); }}}}",
            Accessibility.ForProperty(edmProperty),
            _typeMapper.GetTypeName(edmProperty.TypeUsage),
            _code.Escape(edmProperty),
            _code.SpaceAfter(Accessibility.ForGetter(edmProperty)),
            _code.SpaceAfter(Accessibility.ForSetter(edmProperty)));
    } else {
        return string.Format(
            CultureInfo.InvariantCulture,
            "{0} {1} {2} {{ {3}get; {4}set; }}",
            Accessibility.ForProperty(edmProperty),
            _typeMapper.GetTypeName(edmProperty.TypeUsage),
            _code.Escape(edmProperty),
            _code.SpaceAfter(Accessibility.ForGetter(edmProperty)),
            _code.SpaceAfter(Accessibility.ForSetter(edmProperty)));
    }
}

That creates a rather ugly one-line setter but it gets the job done. It does use a helper function to Default the date to a Local which looks like this:

public class DateKindHelper
{
    public static DateTime DefaultToLocal(DateTime date)
    {
        return date.Kind == DateTimeKind.Unspecified ? DateTime.SpecifyKind(date, DateTimeKind.Local) : date;
    }

    public static DateTime? DefaultToLocal(DateTime? date)
    {
        return date.HasValue && date.Value.Kind == DateTimeKind.Unspecified ? DateTime.SpecifyKind(date.Value, DateTimeKind.Local) : date;
    }
}

---

Our solution part 2: IQueryable filters

The next problem was Breeze passing UTC dates when applying where clauses to our IQueryable controller actions. After reviewing the code for Breeze, Web API and Entity Framework, we decided the best option was to intercept calls to our controller actions and swap out the UTC dates in the QueryString with local dates.

We chose to do this using a custom attribute that we could apply to our controller actions such as:

[UseLocalTime]
public IQueryable<Product> Products()
{
    return _dc.Context.Products;
}

The class that implemented this attribute is:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Http;
using System.Web.Http.Filters;
using System.Text.RegularExpressions;
using System.Xml;

namespace TestBreeze.Controllers.api
{
    public class UseLocalTimeAttribute : ActionFilterAttribute
    {
        Regex isoRegex = new Regex(@"((?:-?(?:[1-9][0-9]*)?[0-9]{4})-(?:1[0-2]|0[1-9])-(?:3[0-1]|0[1-9]|[1-2][0-9])T(?:2[0-3]|[0-1][0-9]):(?:[0-5][0-9]):(?:[0-5][0-9])(?:\.[0-9]+)?Z)", RegexOptions.IgnoreCase);

        public override void OnActionExecuting(System.Web.Http.Controllers.HttpActionContext actionContext)
        {
            // replace all ISO (UTC) dates in the query string with local dates
            var uriString = HttpUtility.UrlDecode(actionContext.Request.RequestUri.OriginalString);
            var matches = isoRegex.Matches(uriString);
            if (matches.Count > 0)
            {
                foreach (Match match in matches)
                {
                    var localTime = XmlConvert.ToDateTime(match.Value, XmlDateTimeSerializationMode.Local);
                    var localString = XmlConvert.ToString(localTime, XmlDateTimeSerializationMode.Local);
                    var encoded = HttpUtility.UrlEncode(localString);
                    uriString = uriString.Replace(match.Value, encoded);
                }
                actionContext.Request.RequestUri = new Uri(uriString);
            }

            base.OnActionExecuting(actionContext);
        }
    }
}

---

Our solution part 3: Json

This might be more controversial but our web app audience are entirely local too :).

We wanted Json sent to the client to contain dates/times in our local timezone by default. Also we wanted any dates in Json received from the client to be converted to our local timezone. To do this we created a custom JsonLocalDateTimeConverter and swapped out the Json converter Breeze installs.

The converter looks like this:

public class JsonLocalDateTimeConverter : IsoDateTimeConverter
{
    public JsonLocalDateTimeConverter () : base() 
    {
        // Hack is for the issue described in this post (copied from BreezeConfig.cs):
        // http://stackoverflow.com/questions/11789114/internet-explorer-json-net-javascript-date-and-milliseconds-issue
        DateTimeFormat = "yyyy-MM-dd\\THH:mm:ss.fffK";
    }


    // Ensure that all dates go out over the wire in full LOCAL time format (unless date has been specifically set to DateTimeKind.Utc)
    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        if (value is DateTime)
        {
            // if datetime kind is unspecified then treat is as local time
            DateTime dateTime = (DateTime)value;
            if (dateTime.Kind == DateTimeKind.Unspecified)
            {
                dateTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Local);
            }

            base.WriteJson(writer, dateTime, serializer);
        }
        else
        {
            base.WriteJson(writer, value, serializer);
        }
    }


    // Ensure that all dates arriving over the wire get parsed into LOCAL time
    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        var result = base.ReadJson(reader, objectType, existingValue, serializer);

        if (result is DateTime)
        {
            DateTime dateTime = (DateTime)result;
            if (dateTime.Kind != DateTimeKind.Local)
            {
                result = dateTime.ToLocalTime();
            }
        }

        return result;
    }
}

And finally to get the above converter installed we created a CustomBreezeConfig class:

public class CustomBreezeConfig : Breeze.WebApi.BreezeConfig
{

    protected override JsonSerializerSettings CreateJsonSerializerSettings()
    {
        var baseSettings = base.CreateJsonSerializerSettings();

        // swap out the standard IsoDateTimeConverter that breeze installed with our own
        var timeConverter = baseSettings.Converters.OfType<IsoDateTimeConverter>().SingleOrDefault();
        if (timeConverter != null)
        {
            baseSettings.Converters.Remove(timeConverter);
        }
        baseSettings.Converters.Add(new JsonLocalDateTimeConverter());

        return baseSettings;
    }
}

---

That's about it. All comments and suggestions are welcome.

Answer 2

Although I realize you may not be able to control this in your scenario, I believe another solution to this problem is to use the type DateTimeOffset rather than DateTime to represent date/time in your entity model.

Answer 3

I reached your article and wanted to pass some information. A colleague implemented your solution and it worked well for any users in the server's time zone. Unfortunately, for users outside of the server timezone, it didn't work.

I have modified your converter class to make usage of TimeZoneInfo. Here's the code:

public class JsonLocalDateTimeConverter : IsoDateTimeConverter
{
    public JsonLocalDateTimeConverter()
        : base()
    {
        // Hack is for the issue described in this post (copied from BreezeConfig.cs):
        // http://stackoverflow.com/questions/11789114/internet-explorer-json-net-javascript-date-and-milliseconds-issue
        DateTimeFormat = "yyyy-MM-dd\\THH:mm:ss.fffK";
    }


    // Ensure that all dates go out over the wire in full LOCAL time format (unless date has been specifically set to DateTimeKind.Utc)
    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        if (value is DateTime)
        {
            // if datetime kind is unspecified - coming from DB, then treat is as UTC - user's UTC Offset. All our dates are saved in user's proper timezone. Breeze will Re-add the offset back
            var userdateTime = (DateTime)value;
            if (userdateTime.Kind == DateTimeKind.Unspecified)
            {
                userdateTime = DateTime.SpecifyKind(userdateTime, DateTimeKind.Local);
                var timeZoneInfo = ApplicationContext.Current.TimeZoneInfo;
                var utcOffset = timeZoneInfo.GetUtcOffset(userdateTime);
                userdateTime = DateTime.SpecifyKind(userdateTime.Subtract(utcOffset), DateTimeKind.Utc);
            }

            base.WriteJson(writer, userdateTime, serializer);
        }
        else
        {
            base.WriteJson(writer, value, serializer);
        }
    }


    // Ensure that all dates arriving over the wire get parsed into LOCAL time
    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        var result = base.ReadJson(reader, objectType, existingValue, serializer);

        if (result is DateTime)
        {
            var utcDateTime = (DateTime)result;
            if (utcDateTime.Kind != DateTimeKind.Local)
            {
                // date is UTC, convert it to USER's local time
                var timeZoneInfo = ApplicationContext.Current.TimeZoneInfo;
                var utcOffset = timeZoneInfo.GetUtcOffset(utcDateTime);
                result = DateTime.SpecifyKind(utcDateTime.Add(utcOffset), DateTimeKind.Local);
            }
        }

        return result;
    }
}

The key here is:

var timeZoneInfo = ApplicationContext.Current.TimeZoneInfo;

This variable is set in our user context upon login. When a user logins, we pass the results of jsTimezoneDetect on the login request and we put that information in the user's context on the server. Because we have a Windows server and jsTimezoneDetect will spit a IANA timezone and we need a windows timezone, i've imported noda-time nuget in our solution and with the following code, we can convert a IANA timezone to a Windows timezone:

// This will return the Windows zone that matches the IANA zone, if one exists.
public static string IanaToWindows(string ianaZoneId)
{
    var utcZones = new[] { "Etc/UTC", "Etc/UCT" };
    if (utcZones.Contains(ianaZoneId, StringComparer.OrdinalIgnoreCase))
        return "UTC";

    var tzdbSource = NodaTime.TimeZones.TzdbDateTimeZoneSource.Default;

    // resolve any link, since the CLDR doesn't necessarily use canonical IDs
    var links = tzdbSource.CanonicalIdMap
      .Where(x => x.Value.Equals(ianaZoneId, StringComparison.OrdinalIgnoreCase))
      .Select(x => x.Key);

    var mappings = tzdbSource.WindowsMapping.MapZones;
    var item = mappings.FirstOrDefault(x => x.TzdbIds.Any(links.Contains));
    if (item == null) return null;
    return item.WindowsId;
}