Custom EF Core Function to Use Transact-SQL AT TIME ZONE

In this post, let's see how we can write a Custom EF Core Function to Use Transact-SQL AT TIME ZONE.

Consider the below example. I have the following Order entity in my DbContext.

public class Order
{
    public int Id { get; set; }
 
    public DateTimeOffset OrderDate { get; set; }
}

Now I have the following Extension method to Convert a given DateTimeOffset to a given TimeZone.

public static class DateTimeExtensions
{
    public static DateTimeOffset ConvertToTimeZone(this DateTimeOffset dateTimeOffset, string timeZone)
    {
        TimeZoneInfo timeZoneInfo = TimeZoneInfo.FindSystemTimeZoneById(timeZone);
        DateTime datetime = TimeZoneInfo.ConvertTimeFromUtc(dateTimeOffset.UtcDateTime, timeZoneInfo);
 
        return new DateTimeOffset(datetime, timeZoneInfo.GetUtcOffset(datetime));
    }
}

Now consider the below query.

var timeZone = "Eastern Standard Time";
 
var orders = await context.Orders
    .Select(x => new
    {
        OrderDate = x.OrderDate,
        ConvertedOrderDate = x.OrderDate.ConvertToTimeZone(timeZone)
    })
    .ToListAsync();

Note for ConvertedOrderDate, I am using the above CLR extension method. 

And when I run this query, the generated SQL statement is going to look like below.

SELECT [o].[OrderDate]
FROM [Orders] AS [o]

And with the below result.

OrderDate                     | ConvertedOrderDate
------------------------------| ------------------------------
20/02/2022 12:00:00 am +00:00 | 19/02/2022 7:00:00 pm -05:00

In the SQL statements EF generated, you can see it doesn't contain anything on ConvertedOrderDate or TimeZone conversion. That's because EF can't translate my CLR extension method into SQL statements. And if we have used this extension method in a Where statement (instead of Select), EF is going to loudly throw an error saying "Translation of method 'DateTimeExtensions.ConvertToTimeZone' failed". So here basically the TimeZone conversion was done In Memory.

And for these kinds of scenarios, we can use this feature in EF, where we can say how a method should get translated into SQL.

First, I am creating a static class with the following method.

public static class EfFunctions
{
    public static DateTimeOffset ConvertToTimeZone(DateTimeOffset dateTimeOffset, [NotParameterized] string timeZone)
        => throw new NotImplementedException("This method should be implemented in EF.");
}

We don't provide any implementation here.

Now we need to instruct EF on how to translate this method.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    MethodInfo methodInfo = typeof(EfFunctions).GetMethod(nameof(EfFunctions.ConvertToTimeZone));
    modelBuilder
        .HasDbFunction(methodInfo)
        .HasTranslation(x =>
        {
            ColumnExpression columnExpression = x.First() as ColumnExpression;
 
            SqlConstantExpression timeZoneExpression = x.Skip(1).First() as SqlConstantExpression;
 
            string timeZoneLiteralValue = timeZoneExpression.TypeMapping.GenerateSqlLiteral(timeZoneExpression.Value);
 
            SqlFragmentExpression valueExpression =
                new($"{columnExpression.Table.Alias}.{columnExpression.Name} AT TIME ZONE {timeZoneLiteralValue} AS DATETIMEOFFSET");
 
            return new SqlFunctionExpression(
                "CAST",
                new List<SqlExpression>() { valueExpression },
                false,
                new List<bool>() { false, false, false },
                typeof(DateTimeOffset),
                null
            );
    });
}

Here I am using Transact-SQL CAST and AT TIME ZONE to generate the proper SQL statements for TimeZone conversion.

And now I am changing my query as follows.

var timeZone = "Eastern Standard Time";
 
var orders = await context.Orders
    .Select(x => new
    {
        OrderDate = x.OrderDate,
        ConvertedOrderDate = EfFunctions.ConvertToTimeZone(x.OrderDate, timeZone)
    })
    .ToListAsync();

This time, for ConvertedOrderDate, I am using the EF Function we just wrote. 

And now when we execute the query, EF is creating the following SQL statement.

SELECT [o].[OrderDate], CAST(o.OrderDate AT TIME ZONE N'Eastern Standard Time' AS DATETIMEOFFSET) AS [ConvertedOrderDate]
FROM [Orders] AS [o]

With the following result as above.

OrderDate                     | ConvertedOrderDate
------------------------------| ------------------------------
20/02/2022 12:00:00 am +00:00 | 19/02/2022 7:00:00 pm -05:00

Hope this helps.

Read More: 

   User-defined function mapping

Happy Coding.

Regards,

Jaliya