CODEWITHSUNDEEP
c#linqdatetimetimezone
arvind
arvind

Reputation: 189

How to convert a DateTime object to Local/UTC Timezone in Linq?

I would like to know if it is possible to convert a DateTime object's timezone in a Linq Query. For example

var seats = await _seat.GetAll()
                    .Where( 
                       DbFunctions.AddHours( x.StartTime, -1 ) >= x.CreationTime.ToUniversalTime());

In the above code, x.StartTime is in UTC timezone whereas x.Creation time is in local timezone. if I call ToUniversalTime() on x.CreationTime I get an exception. Is it possible to do the conversion inside the Linq query? I know it is possible to extract CreationTime before the query and convert it, but it would be great to know the possibility to convert it inside the Linq quert exists.

Upvotes: 2

Views: 3396

Answers (1)

arvind
arvind

Reputation: 189

As @MartinLiversage explained in the columns the best solution would be as follows:

You can easily solve your problem by pulling all the rows to the client side and then perform the filtering using your predicate (putting aside the problems created by using "local time".) However, I assume that you want to filter the records on the database server. EF will have to translate your predicate to corresponding SQL and that limits what you can do in the Where clause. The DbFunctions is a way to declare certain simple computations that EF can translate to SQL but there is no way to perform time zone conversions. SQL Server has no concept of time zones.

And as @Martine mentioned the following could be taken into considertion:

SQL Server 2016 just added time zone support. However, there aren't EF DbFunctions for them yet, AFAIK. Still, probably not a good usage here.

Upvotes: 1

Related Questions