EF Core，按 UTC 日期按月和年分组 [英] EF Core, group by month and year with utc dates

问题描述

我将所有日期存储为 UTC，我需要按月和年对实体进行分组，所以我正在做:

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

由于日期是 UTC，我在 1/09/2020 00:30 AM 创建了一张票，但由于它存储为 UTC(我是 +2)，因此将存储为 2020-08-31 22:25.现在，如果我按月分组，我会将这个实体分组在错误的月份.有没有什么简单的方法可以不在内存中做到这一点?

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?

推荐答案

有几种方法可以引入映射到 sql 函数或原始 sql 片段的 c# 函数.

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

对于直接映射到同名sql函数的函数，只需要定义方法即可；

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

或者在该方法上使用 [DbFunction] 属性.

Or use the [DbFunction] attribute on that method.

要生成像@date AT TIME ZONE @name"这样的 sql 片段，您不能只使用 SqlFunctionExpression 的实例，还有一些工作要做.

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

哪个应该翻译成sql；

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')

这篇关于EF Core，按 UTC 日期按月和年分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！