entity framework core t sql at time zone

1//https://stackoverflow.com/questions/63992874/ef-core-group-by-month-and-year-with-utc-dates
2public static ReturnType CustomMethod(ArgType arg) => throw new NotImplementedException();
3
4protected override void OnModelCreating(ModelBuilder builder){
5    builder.HasDbFunction(typeof(Context).GetMethod(nameof(CustomMethod)));
6}
7
8public static DateTimeOffset ToTimeZone(this DateTimeOffset value, string name) => throw new NotImplementedException();
9
10public class SqlFragmentListExpression : SqlExpression
11{
12    public SqlFragmentListExpression(Type type, RelationalTypeMapping typeMapping, params SqlExpression[] fragments) : base(type, typeMapping)
13    {
14        Fragments = fragments;
15    }
16
17    public SqlExpression[] Fragments { get; }
18
19    public override void Print(ExpressionPrinter expressionPrinter)
20    {
21        foreach (var f in Fragments)
22            f.Print(expressionPrinter);
23    }
24
25    protected override Expression VisitChildren(ExpressionVisitor visitor)
26    {
27        var frags = new SqlExpression[Fragments.Length];
28        var changed = false;
29        for(var i = 0; i < Fragments.Length; i++)
30        {
31            frags[i] = (SqlExpression)visitor.Visit(Fragments[i]);
32            changed |= frags[i] != Fragments[i];
33        }
34        return changed ? new SqlFragmentListExpression(Type, TypeMapping, frags) : this;
35    }
36}
37
38// OnModelCreating
39builder                
40    .HasDbFunction(typeof(Extensions).GetMethod(nameof(Extensions.ToTimeZone)))
41    .HasTranslation(args => {
42        var dto = args.ElementAt(0);
43        return new SqlFragmentListExpression(dto.Type, dto.TypeMapping,
44            dto,
45            new SqlFragmentExpression(" AT TIME ZONE "),
46            args.ElementAt(1));
47    });
48    
49// use it like
50_dbContext.Tickets
51    .Where(x => x.Date >= from && x.Date <= to)
52    .Select(x => new {
53        Date = x.Date.ToTimeZone("Central European Standard Time")
54    })
55    .GroupBy(x => new {
56        Year = x.Date.Year,
57        Month = x.Date.Month
58    },
59    (x, e) => new {
60        x.Year,
61        x.Month,
62        Count = e.Count()
63    })
64