Entity Framework query using TimeZoneInfo cannot be translated to SQL Server query

Kiks455 0
Viewed 61

I am working with time zones in my .NET application. I followed the best practices regarding time zones according to Microsoft (link below):

https://learn.microsoft.com/en-us/previous-versions/dotnet/articles/ms973825(v=msdn.10)

Here you can read that the best option is to store the time zone in the database with the time in that time zone and perform calculations transforming it to UTC. I was trying to filter the records based on the date using the following piece of code:

var result = ContextClassObject.Entity
                               .Where(e => TimeZoneInfo.ConvertTimeToUtc(e.Date) > DateTime.UtcNow)
                               .ToList();

I get the following error message:

System.InvalidOperationException: „The LINQ expression 'DbSet() .Where(a => TimeZoneInfo.ConvertTimeToUtc(a.Date) > DateTime.UtcNow)' could not be translated. Additional information: Translation of method 'System.TimeZoneInfo.ConvertTimeToUtc' failed. If this method can be mapped to your custom function, see https://go.microsoft.com/fwlink/?linkid=2132413 for more information. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'

I think it means that my query cannot be translated to a SQL query so I have to tell it directly how to translate it or materialize the data and then filter it in C#, which I don't want to do because it's gonna be slower than SQL Server could do it and premature materialization isn't a good thing to do.

Is there a way to make it work without mapping the function to a SQL query directly which would be quite complicated for a simple operation? Should I just store any DateTime in UTC, which is supposed to be a good method too and save myself the trouble?

Below is the example row of a database. The date is in the time zone, which id is stored right next to it:

Example of a row in the database

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

Since (according to your comments on the question) the field is the date in which a job offer expires, then consider the following approach.

First calculate the date in the time zone where the job offer is relevant. In this case, likely the time zone of the candidate.

TimeZoneInfo timeZone = TimeZoneInfo.FindSystemTimeZoneById("America/New_York"); // for example
DateTime today = TimeZoneInfo.ConvertTime(DateTime.UtcNow, timeZone).Date;

Then use that date in the query. For example:

var expiredOffers = dbContext.JobOffers.Where(e => e.ExpirationDate > today);

or perhaps the inverse:

var currentOffers = dbContext.JobOffers.Where(e => e.ExpirationDate <= today);

This approach avoids several problems:

  • It keeps the expression translatable to a query, avoiding the error you showed in the question.
  • It keeps the query sargable, so an index can be used in the database to keep it performant.
  • It keeps the query simple, comparing whole dates to whole dates, aligning with the business rule you described.
Matt Johnson-Pint0
Related