score:1
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))
score:1
In EF Core 5
In EF Core 5 the below code worked for me: function definition
public static class QueryHelper
{
public static DateTimeOffset? ToTimeZone(this DateTime? source, string timeZone)
{
if (!source.HasValue) return null;
var tz = TimeZoneInfo.FindSystemTimeZoneById(timeZone);
var date = TimeZoneInfo.ConvertTimeFromUtc(source.Value, tz);
return new DateTimeOffset(date, tz.GetUtcOffset(date));
}
public static DateTimeOffset ToTimeZone(this DateTime source, string timeZone)
{
return AtTimeZoneSql((DateTime?)source, timeZone).Value;
}}
custom expression builder
public class AtTimeZoneExpression5 : SqlFunctionExpression
{
private readonly IReadOnlyCollection<SqlExpression> _params;
public AtTimeZoneExpression5(IReadOnlyCollection<SqlExpression> parameters) : base("notimportant", true, typeof(DateTimeOffset), RelationalTypeMapping.NullMapping)
{
_params = parameters;
}
protected override Expression Accept(ExpressionVisitor visitor)
{
if (!(visitor is QuerySqlGenerator))
return base.Accept(visitor);
if (_params.First().TypeMapping.DbType == System.Data.DbType.Date)
visitor.Visit(new SqlFragmentExpression("CONVERT(datetime2, "));
visitor.Visit(_params.First()); //First paramenter
if (_params.First().TypeMapping.DbType == System.Data.DbType.Date)
visitor.Visit(new SqlFragmentExpression(")"));
visitor.Visit(new SqlFragmentExpression(" AT TIME ZONE "));
visitor.Visit(_params.Skip(1).First()); //2nd parameter
return this;
}
protected override void Print([NotNullAttribute] ExpressionPrinter expressionPrinter)
{
Console.WriteLine(expressionPrinter);
}
}
then in on model creating
we should use
builder.HasDbFunction(typeof(QueryHelper).GetMethod(nameof(ToTimeZone), new[] { typeof(DateTime), typeof(string) }))
.HasTranslation(args =>
{
return new AtTimeZoneExpression5(args);
}
builder.HasDbFunction(typeof(QueryHelper).GetMethod(nameof(ToTimeZone), new[] { typeof(DateTime?), typeof(string) }))
.HasTranslation(args =>
{
return new AtTimeZoneExpression5(args);
}
Source: stackoverflow.com
Related Articles
- EF Core 2.2: Add timezone conversion to a datetime2 column on select, groupby and/or where (Modify/enrich property mapping on a query)
- ASP NET CORE Entity Framework Select with GroupBy Id
- EF Core GroupBy with Select Distinct Count
- LINQ GroupBy and Filter On a Column and Select First for multiple rows
- In C# Groupby Select using a ForEach or other function to aggregate one column into a array of results
- EF Core LINQ GROUPBY Then Select to get more than one properties of the entity
- How to select entire row by distinct column MVC code first
- How to select a column after GroupBy in LINQ?
- Linq code to select one item
- Select All distinct values in a column using LINQ
- Select only a single column in LINQ
- Linq : select value in a datatable column
- Select single column from dataset with LINQ
- How to select top N rows in a Linq GroupBy
- EF Core nested Linq select results in N + 1 SQL queries
- How to select last record in a LINQ GroupBy clause
- Exclude a column from a select using LINQ
- EF Core queries all columns in SQL when mapping to object in Select
- Linq on DataTable: select specific column into datatable, not whole table
- How do I select the distinct row count of a column in a data table?
- EF Core how select entity with many-to-many relationship
- How to select specific column in LINQ?
- C# DataTable select columns where column name like
- Select all rows with distinct column value using LINQ
- How to select Dynamic column from List
- I need to select particular column based on check box list
- LINQ Source Code Available
- Linq :DataTable select does not work if column name has space in it?
- Creating a LINQ Select expression dynamically from string column names
- linq after groupby unable to get column values
- Strange appearance of a null entry in the list of tasks
- Sorting User control in a flow panel based on date in text box
- Linq Complex OrderBy by Props attributes
- IronPython on ASP.NET MVC
- Select exclusive list of list of objects based on object properties
- Convert list of one type to list of another type in C# 3.5
- ASP.NET convert IQueryable to List
- Remove Element From String Array Using LINQ Contains Value
- How can I format a column from a tab separated file?
- Dictionary ForAll / ForEach method
- Convert data collection into nested hierarchical object list
- LINQ to Object collection of objects constraint on property
- Converting SQL query to LINQ -- Operator '&&' cannot be applied to operands of type 'int' and 'bool'
- Group by and MIN() in LINQ
- Using LINQ expressions in Visual Studio's Watch window
- Group by Linq . Count of Key field
- Lambda Expression +IEnumerable+ C#+ Where+Contains
- Create Generic Type with Generic Interface at Run Time
- SqlDataReader - How to convert the current row to a dictionary
- How to walk through a SelectListItem Array with Linq - techniques?