Entity Framework Core 7.0 (EF 7.0): In-Built Support for Transact-SQL AT TIME ZONE

In this post, let's have a look at the new AtTimeZone functions that got introduced in Entity Framework Core 7.0 (or Entity Framework 7.0) for DateTime and DateTimeOffset.

Prior to EF 7, in order to use Transact-SQL AT TIME ZONE, we had to do something like I have described in this post: Custom EF Core Function to Use Transact-SQL AT TIME ZONE. But we don't have to do that anymore.

Let's have a look.

Consider the following orders.

List<Order> orders = new()
{
    new()
    {
        OrderDate = new DateTimeOffset(new DateTime(2023, 6, 1, 8, 30, 0), TimeSpan.Zero)
    },
    new()
    {
        OrderDate = new DateTimeOffset(new DateTime(2023, 6, 1, 9, 0, 0), TimeSpan.Zero)
    }
};

Once these are in the database, I can use AtTimeZone functions as follows.

var query = await context.Orders
    .Select(x => new
    {
        UtcTime = EF.Functions.AtTimeZone(x.OrderDate, "UTC"),
        EasternTime = EF.Functions.AtTimeZone(x.OrderDate, "Eastern Standard Time"),
        NztTime = EF.Functions.AtTimeZone(x.OrderDate, "New Zealand Standard Time")
    })
    .ToListAsync();
 
foreach (var order in query)
{
    Console.WriteLine(order);
}

This is generating the following SQL.

SELECT  [o].[OrderDate] AT TIME ZONE 'UTC' AS [UtcTime], 
        [o].[OrderDate] AT TIME ZONE 'Eastern Standard Time' AS [EasternTime], 
        [o].[OrderDate] AT TIME ZONE 'New Zealand Standard Time' AS [NztTime]
FROM    [Orders] AS [o]

And the output is as follows.

{ UtcTime = 1/06/2023 8:30:00 am +00:00, EasternTime = 1/06/2023 4:30:00 am -04:00, NztTime = 1/06/2023 8:30:00 pm +12:00 }
{ UtcTime = 1/06/2023 9:00:00 am +00:00, EasternTime = 1/06/2023 5:00:00 am -04:00, NztTime = 1/06/2023 9:00:00 pm +12:00 }

Isn't it handy?

Hope this helps.

Happy Coding.

Regards,
Jaliya