EF Core 2.2: Add timezone conversion to a datetime2 column on select, groupby and/or where (Modify/enrich property mapping on a query)

Question

First things first, what I'm trying to do is quite similar to what is explained in this question, even the solution by @jeremy-lakeman would fit but seems it only works on +EF Core 3.0.

I would really appreciate a way to intercept the SQL generated for a datetime entity property when using a method extension (i.e x.AddedDate.ToTimeZone("(timezone id)"), in order to add a timezone conversion.

I've seen ModelBuilder.HasDbFunction().HasTranslation() approach but I'm not sure which type of expression use there (SqlFunctionExpression, SqlFragmentExpression, other). Would be a way of just intercept the default SQL generated to format it?

var result = context.DbSet<Ticket>().Select(x => new 
{ 
    LocalDate = x.TicketDateUtc.ToTimeZone("Romance Standard Time")
}).First();

Turns on:

SELECT [x].TicketDateUtc AT TIMEZONE 'Romance Standard Time' FROM Tickets AS [x]

Answer 1

In EF Core 5

In EF Core 5 the below code worked for me: function definition

public static class QueryHelper
{
    public static DateTimeOffset? ToTimeZone(this DateTime? source, string timeZone)
    {
        if (!source.HasValue) return null;
        var tz = TimeZoneInfo.FindSystemTimeZoneById(timeZone);
        var date = TimeZoneInfo.ConvertTimeFromUtc(source.Value, tz);
        return new DateTimeOffset(date, tz.GetUtcOffset(date));
    }
    public static DateTimeOffset ToTimeZone(this DateTime source, string timeZone)
    {
        return AtTimeZoneSql((DateTime?)source, timeZone).Value;
    }}

custom expression builder

        public class AtTimeZoneExpression5 : SqlFunctionExpression
        {
            private readonly IReadOnlyCollection<SqlExpression> _params;

            public AtTimeZoneExpression5(IReadOnlyCollection<SqlExpression> parameters) : base("notimportant", true, typeof(DateTimeOffset), RelationalTypeMapping.NullMapping)
            {
                _params = parameters;
            }
            protected override Expression Accept(ExpressionVisitor visitor)
            {
                if (!(visitor is QuerySqlGenerator))
                    return base.Accept(visitor);
                if (_params.First().TypeMapping.DbType == System.Data.DbType.Date)
                    visitor.Visit(new SqlFragmentExpression("CONVERT(datetime2, "));
                visitor.Visit(_params.First());                         //First paramenter
                if (_params.First().TypeMapping.DbType == System.Data.DbType.Date)
                    visitor.Visit(new SqlFragmentExpression(")"));
                visitor.Visit(new SqlFragmentExpression(" AT TIME ZONE "));
                visitor.Visit(_params.Skip(1).First());                 //2nd parameter
                return this;
            }
            protected override void Print([NotNullAttribute] ExpressionPrinter expressionPrinter)
            {
                Console.WriteLine(expressionPrinter);
            }
        }

then in on model creating we should use

      builder.HasDbFunction(typeof(QueryHelper).GetMethod(nameof(ToTimeZone), new[] { typeof(DateTime), typeof(string) }))
.HasTranslation(args =>
{
     return new AtTimeZoneExpression5(args);
}
      builder.HasDbFunction(typeof(QueryHelper).GetMethod(nameof(ToTimeZone), new[] { typeof(DateTime?), typeof(string) }))
.HasTranslation(args =>
{
     return new AtTimeZoneExpression5(args);
}