时间戳

首页 > 解决方案 > EF Core 2.2：将时区转换添加到 select、groupby 和/或 where 上的 datetime2 列（修改/丰富查询上的属性映射）

c# - EF Core 2.2：将时区转换添加到 select、groupby 和/或 where 上的 datetime2 列（修改/丰富查询上的属性映射）

问题描述

首先，我想要做的与这个问题中解释的非常相似，即使@jeremy-lakeman 的解决方案也适合，但似乎它只适用于 +EF Core 3.0。

我真的很感激使用方法扩展（即 x.AddedDate.ToTimeZone("(timezone id)") 时拦截为日期时间实体属性生成的 SQL 的方法，以便添加时区转换。

我见过 ModelBuilder.HasDbFunction().HasTranslation() 方法，但我不确定那里使用哪种类型的表达式（SqlFunctionExpression、SqlFragmentExpression 等）。将是一种截取生成的默认 SQL 以对其进行格式化的方法吗？

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

打开：

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

标签： c#sql-serverlinqentity-framework-coretimezone

解决方案

所以最后我设法得到了一个基于Jeremy 解决方案的初步解决方案。它可以解决问题，但必须进行很多改进。目前，它仅适用于要转换的预期日期时间是表的列（但可以扩展到常量或参数）。

这是在 OnModelCreating 事件期间实现扩展方法并在上下文的 ModelBuilder 上将它们注册为 DbFunction 的部分：

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

这是派生自 的自定义表达式Microsoft.EntityFrameworkCore.Query.Expressions.ColumnExpression。它只拦截 QuerySqlGenerator 以添加一些 sql 片段：

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

利用：

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

输出：

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

推荐阅读