UTC to local time using Linq to Entities

Question

I need to translate a field from UTC to local time in a LINQ to Entities query. But it does not recognize the method 'System.DateTime ToLocalTime' that I was intended to use.

My query was that way:

Select requests for which doesn't exist any other request in the same day in local not yet resolved. Having into account that IncommingDateTime is stored in UTC, this is the query.

from r in Requests
where Request.Count(x => x.IncommingDateTime.ToLocalTime().Date == r.IncommingDateTime.ToLocalTime().Date 
&& x.Resolved == false ) = 0
select r

I'll appreciate any help.

Answer 1

To be able to use functions that don't translate to SQL, you need to materialize the table.

var materializedRequests = Requests.Where(x => !x.Resolved).ToList(); //Materialize list with a ToList call

materializedRequests
    .Where(x =>
        !materializedRequests.Any(y => 
            y.IncommingDateTime.ToLocalTime().Day == x.IncommingDateTime.ToLocalTime().Day
        )
    )

Then you can use pretty much any functions you want. However, materializing the list is a VERY expensive call. Try to filter the list as much as you can before you materialize it.

Answer 2

What time zone would you expect that to use? The only one the database really knows about by default would probably be its own.

Note that DateTime.Day only represents the day of the month - do you want June 10th and July 10th to count as the same day?

If you can work out what time zone you're really interested in, that may suggest a potential solution - although I wouldn't like to guarantee it. It's possible that this is one of those cases where actually it makes sense to store the local date and time, as well as the time zone, rather than the UTC date/time. (This is appropriate if the local date/time is actually more important than the instant represented, compared to other values.)