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