EF Core 2.2 - how to create SqlExpression with AT TIME ZONE SQL fragment

user_8172490 0
Viewed 117

I want to map method to custom SQL, which is

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

So I added HasDbFunction in my DbContext:

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

My question is how can I create GreenwichStandardTime and EXPRESSION?

entity-framework-core sql-server c#
1 Answers

From @Charlieface we can create expression from 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 });
        });

in case I want to replace 'Greenwich Standard Time' with function parameter as @Charlieface use

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] will not convert it to parameter on expression

user_81724900
Related