Using MSSQL 2016 "AT TIMEZONE" feature from Entity Framework?

Question

How would one use the MSSQL 2016 AT TIMEZONE feature from Entity Framework?

In other words, how to generate

SELECT MyTimestamp AT TIME ZONE 'Central European Standard Time' FROM MyTable

by LINQ in entity framework ? Is it even supported yet ? Can one extend Entity Framework manually for this feature ?

There is always the option of creating a database-view with a time-zone column, but ideally I would like to avoid this extra view.

Reinhold

AT TIMEZONE is a blessing for T-SQL programmers who couldn't do this from application code before, at all. In .NET, there's excellent time zone support which sort of obviates the need for this, since you can always use TimeZoneInfo.ConvertTime to convert a DateTime or DateTimeOffset to any desired time zone. I'm not saying there's no need for this, but if you can do any sort of processing on the result, there's at least a workaround. – Blunger

If EF were to be extended, it could actually convert TimeZoneInfo.ConvertTime or (more likely) implement a new method in SqlFunctions. I don't think you can do this as a client of EF, though. You could write your own scalar-valued function for AT TIMEZONE and call that, but that's lame and inefficient as the optimizer hates scalar-valued functions. – Blunger

@JeroenMostert Regarding your second comment, I came to the same conclusion. Unfortunately not the answer I hoped for. – Reinhold

@JeroenMostert The reason I want to use the SQL timezone feature, is due to some grouping on the data, which should be performed after timezone conversion (group by day). Doing these (simple) aggregations on the database saves a lot of data-transfer overhead. – Reinhold

If EF has no support and you must do it in the database, it can still be done reasonably efficiently with a table-valued function. This is still lame, but at least it's fast(ish). CREATE FUNCTION dbo.AtTimeZone(@d DATETIMEOFFSET, @TimeZone SYSNAME) RETURNS TABLE AS RETURN SELECT @d AT TIME ZONE @TimeZone AS AtTimeZone; EF 5+ has TVF support, though I don't know if it can CROSS APPLY properly. Of course, a view is simpler (as you proposed), but only works for one particular query. Last but not least, you can of course open an issue. – Blunger

@JeroenMostert Thanks for the suggestion, however I think a few views is better for my scenario. – Reinhold

Answer 1

Couldn't you use a "model-defined function" in EF to create a functional expression that does at time zone thingy?

https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/ef/language-reference/how-to-call-model-defined-functions-in-queries

<Function Name="TZ" ReturnType="Edm.DateTime">
    <Parameter Name="date" Type="Edm.DateTime" />
    <DefiningExpression>
    date AT TIME ZONE 'Central European Standard Time'
    </DefiningExpression>
</Function>

[EdmFunction("YourModel", "TZ")]
public static int TZ(DateTime date)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

#C:

from m as Mytable select new {TZ(s.MyTimestamp)};

Penknife

Sadly, this does not seem to work. I get the exception "The query syntax is not valid. Near identifier 'AT', ...". It looks like the EF 6.2 query parser does not like the construct. But, I did like the idea. – Kurd