EF Core, group by month and year with utc dates

Question

I'm storing all the dates as UTC and I need to group an entity by month and year so I'm doing:

_dbContext.Tickets.Where(x => x.Date >= from && x.Date <= to).GroupBy(x=> new {
                Year = x.Date.Year,
                Month = x.Date.Month
            }).Select(x=> new {x.Key, Count = x.Count()})

Since the dates are utc, I have a ticket created on 1/09/2020 00:30 AM but since it is stored as UTC (I'm +2) it will be stored as 2020-08-31 22:25. Now if I Group by month I will get this entity grouped in the wrong month. Is there any simple way to do this not on memory?

First decide what is wrong. Do you want to group by UTC or local time? You can use : x.Date.ToLocalTime >= from && x.Date.ToLocalTime <= to) — jdweng, Sep 21 '20 at 13:20
@jdweng I think `ToLocalTime` won't be [regonized](https://stackoverflow.com/questions/49902534/linq-to-entities-does-not-recognize-the-datetime-tolocaltime-method) by Linq to Entities. — Peter Csala, Sep 21 '20 at 13:23
The filter can be ignored but since from e to are both UTC, the same as the dates in the db the filter is fine and it does not require .ToLocalTime(). If I could set ToLocalTime in the grouping part like "Month = x.Date.ToLocalTime().Month it would be fine, but I can't since it will be evaluated locally. @jdweng — Davide Quaglio, Sep 21 '20 at 13:23
Sql Server? So you want to generate the sql `date AT TIME ZONE @name`? There's probably a way to define a custom database function.... — Jeremy Lakeman, Sep 22 '20 at 06:12
Or define `[DbFunction] public static DateTimeOffset SWITCHOFFSET (DateTimeOffset value, string timezone)` ? (https://learn.microsoft.com/en-us/ef/core/what-is-new/ef-core-2.0/#database-scalar-function-mapping, https://learn.microsoft.com/en-us/sql/t-sql/functions/switchoffset-transact-sql?view=sql-server-ver15) — Jeremy Lakeman, Sep 22 '20 at 06:28
@JeremyLakeman yes SqlServer, now I'm looking at your link.. but how would you convert the datetime to the correct timezone? Using normal .ToLocalTime or is there any EF function that do so? — Davide Quaglio, Sep 22 '20 at 07:58
@DavideQuaglio I have a same problem yesterday in ef core 5 I Solve it and send you may be help to you too — Mehdi Haghshenas, Oct 09 '21 at 06:48

score 2 · Answer 1 · answered Sep 23 '20 at 01:33

There are a couple ways to introduce c# functions that map to sql functions or raw sql fragments.

For functions that map directly to an sql function with the same name, you only need to define the method;

public static ReturnType CustomMethod(ArgType arg) => throw new NotImplementedException();

protected override void OnModelCreating(ModelBuilder builder){
    builder.HasDbFunction(typeof(Context).GetMethod(nameof(CustomMethod)));
}

Or use the [DbFunction] attribute on that method.

For generating an sql fragment like "@date AT TIME ZONE @name", where you can't just use an instance of SqlFunctionExpression, there's a little more work to do.

public static DateTimeOffset ToTimeZone(this DateTimeOffset value, string name) => throw new NotImplementedException();

public class SqlFragmentListExpression : SqlExpression
{
    public SqlFragmentListExpression(Type type, RelationalTypeMapping typeMapping, params SqlExpression[] fragments) : base(type, typeMapping)
    {
        Fragments = fragments;
    }

    public SqlExpression[] Fragments { get; }

    public override void Print(ExpressionPrinter expressionPrinter)
    {
        foreach (var f in Fragments)
            f.Print(expressionPrinter);
    }

    protected override Expression VisitChildren(ExpressionVisitor visitor)
    {
        var frags = new SqlExpression[Fragments.Length];
        var changed = false;
        for(var i = 0; i < Fragments.Length; i++)
        {
            frags[i] = (SqlExpression)visitor.Visit(Fragments[i]);
            changed |= frags[i] != Fragments[i];
        }
        return changed ? new SqlFragmentListExpression(Type, TypeMapping, frags) : this;
    }
}

// OnModelCreating
builder                
    .HasDbFunction(typeof(Extensions).GetMethod(nameof(Extensions.ToTimeZone)))
    .HasTranslation(args => {
        var dto = args.ElementAt(0);
        return new SqlFragmentListExpression(dto.Type, dto.TypeMapping,
            dto,
            new SqlFragmentExpression(" AT TIME ZONE "),
            args.ElementAt(1));
    });

Then you can use that method in a query like;

_dbContext.Tickets
    .Where(x => x.Date >= from && x.Date <= to)
    .Select(x => new {
        Date = x.Date.ToTimeZone("Central European Standard Time")
    })
    .GroupBy(x => new {
        Year = x.Date.Year,
        Month = x.Date.Month
    },
    (x, e) => new {
        x.Year,
        x.Month,
        Count = e.Count()
    })

Which should be translated into sql;

SELECT DATEPART(year, [t].[Date] AT TIME ZONE N'Central European Standard Time') AS [Year],
    DATEPART(month, [t].[Date] AT TIME ZONE N'Central European Standard Time') AS [Month], 
    COUNT(*) AS [Count]
FROM [Tickets] AS [t]
GROUP BY DATEPART(year, [t].[Date] AT TIME ZONE N'Central European Standard Time'),
    DATEPART(month, [t].[Date] AT TIME ZONE N'Central European Standard Time')

I will be trying this in the next days and I will be back with the feedback. Thanks for the explanation, seems like a good approach.! — Davide Quaglio, Sep 23 '20 at 11:29
I tried this solutions, and it has a problem, if you convert using AT TIME ZONE, it adds only the hours at the ends, but if you try to select the month it will still select the wrong month. This can be tested using 2020-08-01 00:30:00.0000000 as date that should be translated as 2020-07-31 22:30:00.0000000 but it AT TIME ZONE returns 2020-08-01 00:30:00.0000000 +02:00. I had to modify ToTimeZone method to use a Datetime and not a DateTimeOffset since thats the type I'm using now — Davide Quaglio, Oct 05 '20 at 12:21

EF Core, group by month and year with utc dates

1 Answers1

Linked