[Source Code]-EF Core 2.2: Add timezone conversion to a datetime2 column on select, groupby and/or where (Modify/enrich property mapping on a query)-LINQ,C#

So in the end I've managed to get a preliminary solution based on Jeremy's solution. It does the trick but must be improved a lot. Currently it only works if expected datetime to be converted is a column of a table (but can be extended to constants or parameters).

This is the part that implements the extensions methods and register them on the context's ModelBuilder as DbFunctions during the OnModelCreating event:

public static partial class CustomDbFunctions
{
    public static DateTime? ToTimeZone(this DateTime? source, string timeZone)
    {
        if (!source.HasValue) return null;
        return DateTimeHelper.UtcDateToLocal(source.Value, timeZone);
    }

    public static DateTime ToTimeZone(this DateTime source, string timeZone)
    {
        return ToTimeZone((DateTime?)source, timeZone).Value;
    }

    public static ModelBuilder AddCustomFunctions(this ModelBuilder builder)
    {
        builder.HasDbFunction(typeof(CustomDbFunctions).GetMethod(nameof(ToTimeZone), new[] { typeof(DateTime), typeof(string) }))
            .HasTranslation(args =>
            {
                var dateTimeExpression = args.ElementAt(0);
                if (dateTimeExpression is ColumnExpression column) 
                {
                    return new TimeZoneColumnExpression(column.Name, column.Property, column.Table, args.ElementAt(1));
                }
                return dateTimeExpression;
            });
        builder.HasDbFunction(typeof(CustomDbFunctions).GetMethod(nameof(ToTimeZone), new[] { typeof(DateTime?), typeof(string) }))
            .HasTranslation(args =>
            {
                var dateTimeExpression = args.ElementAt(0);
                if (dateTimeExpression is ColumnExpression column)
                {
                    return new TimeZoneColumnExpression(column.Name, column.Property, column.Table, args.ElementAt(1));
                }
                return dateTimeExpression;
            });
        return builder;
    }
}

And this is the custom expression derived from Microsoft.EntityFrameworkCore.Query.Expressions.ColumnExpression. It only intercepts the QuerySqlGenerator in order to add some sql fragments:

public class TimeZoneColumnExpression : ColumnExpression
{
    private readonly Expression timeZoneId;

    public TimeZoneColumnExpression(string name, IProperty property, TableExpressionBase tableExpression, Expression timeZoneId) : base(name, property, tableExpression)
    {
        this.timeZoneId = timeZoneId ?? throw new ArgumentNullException(nameof(timeZoneId));
    }

    protected override Expression Accept(ExpressionVisitor visitor)
    {
        if (!(visitor is IQuerySqlGenerator))
            return base.Accept(visitor);

        visitor.Visit(new SqlFragmentExpression("CONVERT(datetime2, "));
        base.Accept(visitor);
        visitor.Visit(new SqlFragmentExpression($" AT TIME ZONE 'UTC' AT TIME ZONE "));
        visitor.Visit(timeZoneId);
        visitor.Visit(new SqlFragmentExpression(")"));
        return this;
    }
}

Use:

            var timeZone = TimeZoneInfo.FindSystemTimeZoneById(TimeZoneConverter.TZConvert.IanaToWindows("Europe/Madrid"));
            var groups = await repository.AsQueryable<User>().Where(x => x.Id > 0)
                .GroupBy(x => new { x.BeginDateUtc.ToTimeZone(timeZone.Id).Date })
                .Select(x => 
                new 
                {
                    Date = x.Key,
                    Count = x.Count()
                }).ToListAsync();

Output:

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (80ms) [Parameters=[@__timeZone_Id_0='Romance Standard Time' (Size = 4000)], CommandType='Text', CommandTimeout='120']
      SELECT CONVERT(date, CONVERT(datetime2, [x].[BeginDateUtc] AT TIME ZONE 'UTC' AT TIME ZONE @__timeZone_Id_0)) AS [Date], COUNT(*) AS [Count]
      FROM [dbo].[Users] AS [x]
      WHERE [x].[Id] > CAST(0 AS bigint)
      GROUP BY CONVERT(date, CONVERT(datetime2, [x].[BeginDateUtc] AT TIME ZONE 'UTC' AT TIME ZONE @__timeZone_Id_0))

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);
}