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 Query
- EF Core 2.2: Add timezone conversion to a datetime2 column on select, groupby and/or where (Modify/enrich property mapping on a query)
- Linq GroupBy Max and add column
- LINQ GroupBy and Filter On a Column and Select First for multiple rows
- How to select one column from datagrid and add data for the selected column from field text?
- Is it possible to combine a GroupBy and Select to get a proper named Key?
- ASP NET CORE Entity Framework Select with GroupBy Id
- Avoid extra loop and could not find implementation of query pattern for source type int Select not found
- LINQ and C#: How to add a field, not mapped to a table column
- linq groupby Months and add any missing months to the grouped data
- Code Example for Add DateTime and TimeSpan in EF query
- EF Core GroupBy with Select Distinct Count
- How to select last hour and groupby minute?
- LINQ GroupBy and Select on different property
- Linq to Entity Groupby and concatinate column
- GroupBy and Select extension method assistance
- LINQ Select rows with Max of one column and Distinct in another
- c# Dynamic.Linq - groupby and then select by condition
- How can i Join, GroupBy and Select with LINQ?
- EF core Linq groupby and having sum count - could not be translated and will be evaluated locally
- Select distinct items by combination of two columns and where Value of third column is max- LINQ
- Lodash Equivalent to C# LINQ GroupBy and Select in TypeScript
- How can I check the number of calls to the database in LINQ query when using .NET Core and Code First?
- MVC3. How to select additional column from joined table and send them all to View?
- EF Code first - add collection to a collection, appropriate usage of skip() and take()
- Remove rows with same column value from DataTable and add corresponding values
- Select object and add to list linq c#
- Using LINQ to select item in List<T> and add integer to populate new List<T>
- How to select max and min value of any column of datagridview
- Using Linq to GroupBy and Select
- Expression.Call GroupBy then Select and Count()?
More Query from same tag
- LINQ time subtraction in web api
- How can I get a divided value resulting from 2 summed fields in a LINQ query?
- Entity Framework Query to Load Hierarchical Data
- MinOrDefault extension is not supported
- possible null reference return c# linq
- Set All members of a Collection with LINQ to NULL
- LINQ Query to dynamically filter database where child table column equals optional input value
- How to return IEnumerable<T> for a single item
- C# WPF DataGrid setting multiple selected items programatically
- C# Linq Parent Child flatten
- SQL to Linq : Group by id but can't sum values
- Using LINQ to set up a login page from an access database
- Syntax to fill linq array?
- How to convert SQL query to LINQ lambda expression with Inner Join and subquery
- linq-to-sql orderby and select null entries
- How .First() calls will impact the performance?
- c# use string parameter to define what property to filter by in List of objects
- Why one loop is performing better than other memory wise as well as performance wise?
- Linq Query from textboxes that might be empty (Coalesce)
- Unable to cast object of type in below Linq method
- Iterating over LINQ entity results
- LINQ query should be based on criteria
- How can I test that my Linq IQueryable has executed
- LINQ - Group by with SUM IF
- Xelement adds element value to itself twice
- How to groupby Datatable by a column and sum numeric columns
- WinForms Data Binding Entity Framework 6
- Convert Linq to SqlCommand
- Change one field in an array using linq
- Usefulness of yield