EF Core 2.2 -如何使用AT TIME ZONE SQL片段创建sqexpression

Question

我想将方法映射到自定义SQL，这是

CONVERT(datetime, 
SWITCHOFFSET(GETUTCDATE(), DATEPART(TZOFFSET, GETUTCDATE() AT TIME ZONE 'Greenwich Standard Time')))

我在DbContext中加入了HasDbFunction

modelBuilder.HasDbFunction(typeof(DbContext).GetMethod(nameof(DbContext.GetDateTime)))
.HasTranslation(e =>
{
var GETUTCDATE = new SqlFunctionExpression("GETUTCDATE", typeof(DateTime));

var TZOFFSET = new SqlFragmentExpression("TZOFFSET");
var ATTIMEZONE = new SqlFragmentExpression(" AT TIME ZONE ");
var GreenwichStandardTime = new Expression("Greenwich Standard Time"); // expression with constant?
var EXPRESSION = new Expression(GETUTCDATE, ATTIMEZONE, GreenwichStandardTime); // expression with combine multiple exprssions?
var DATEPART = new SqlFunctionExpression("DATEPART", typeof(int), new[] { TZOFFSET , EXPRESSION  });  // cannot create
var SWITCHOFFSET = new SqlFunctionExpression("SWITCHOFFSET", typeof(DateTimeOffset), new[] { GETUTCDATE, DATEPART });
var DATETIME = new SqlFragmentExpression("DATETIME");

new SqlFunctionExpression("CONVERT", typeof(DateTime), new[] { DATETIME, SWITCHOFFSET });
});

我的问题是如何创建格林尼治标准时间和表达式?

Answer 1

从@Charlieface我们可以从SqlFragmentExpression创建表达式

modelBuilder.HasDbFunction(typeof(DbContext).GetMethod(nameof(DbContext.GetDateTime)))
.HasTranslation(e =>
{
var DATETIME = new SqlFragmentExpression("DATETIME");
var SWITCHOFFSET= new SqlFragmentExpression(
$@" SWITCHOFFSET(GETUTCDATE(), 
DATEPART(TZOFFSET, GETUTCDATE() 
AT TIME ZONE 'Greenwich Standard Time')) ");
return new SqlFunctionExpression("CONVERT", typeof(string), new[] { DATETIME , SWITCHOFFSET });
});

如果我想将'格林威治标准时间'替换为函数参数@Charlieface使用

modelBuilder.HasDbFunction(typeof(DbContext).GetMethod(nameof(DbContext.GetDateTime), new[] {typeof(string)}))
.HasTranslation(e =>
{
var constantExpression = e.First() as ConstantExpression;
var DATETIME = new SqlFragmentExpression("DATETIME");
var SWITCHOFFSET= new SqlFragmentExpression(
$@" SWITCHOFFSET(GETUTCDATE(), 
DATEPART(TZOFFSET, GETUTCDATE() 
AT TIME ZONE '{constantExpression.Value}')) ");
return new SqlFunctionExpression("CONVERT", typeof(string), new[] { DATETIME , SWITCHOFFSET });
});

public DateTime GetDateTime([NotParameterized] string timeZone){
throw new NotImplementedException()
}

[NotParameterized]不会将其转换为表达式参数