Linq-to-EF DateTime.ToLocalTime not supported

Question

DateTime.ToLocalTime is not supported in Linq to EF.

What's the alternative? I am running out of idea.

Linq to SQL and Entity Framework are not the same thing. Which are you using? — Randy Minder, May 03 '12 at 11:54
Linq2Sql and Linq2EF are two separate products. Which one is it? — spender, May 03 '12 at 11:54
How are you trying to use ToUniversalTime - in the where clause or the select? — Phil, May 03 '12 at 12:01
Sorry guys it should be ToLocalTime() method i am trying to use. — ove, May 03 '12 at 12:04
http://stackoverflow.com/questions/3857209/utc-to-local-time-using-linq-to-entities — Habib, May 03 '12 at 12:07
This is a good question, I want to group by `Day` on a UTC date field but I want it to be the local "day", not the UTC day. I definitely don't want to use a materialized view to solve this. — Dan Fitch, Jul 06 '15 at 18:34
@DanFitch - Your bounty expands the scope of this question well beyond what was originally asked. Phil's answer here correctly answers the question that the OP asked, but you are asking for something new. It makes much more sense to open a new question. Especially, consider that the correct answer for what you're asking will involve changing what is stored in the database to either a `datetimeoffset` field or multiple `datetime` fields. — Matt Johnson-Pint, Jul 09 '15 at 16:58
Matt: I see what you're saying... the question is vague, though, so its scope is quite vague. Phil's answer only works if you are trying to convert a parameter, *not* a database value that is being selected on or grouped by. I can open a new question making it explicit that I am looking for a solution for `ToLocalTime` on the DB query side, something like the functions in `DbFunctions`. Thoughts? — Dan Fitch, Jul 09 '15 at 17:08
@DanFitch - Best you could do would be custom functions like the ones in my [SQL Server Time Zone Support](https://github.com/mj1856/SqlServerTimeZoneSupport) project. Invoking from EF would be somewhat difficult. Still, best to ask in a new question so others can chime in with creative ideas. This particular question here is just about avoiding that, such as Phil wrote in his answer. — Matt Johnson-Pint, Jul 11 '15 at 06:44

score 8 · Answer 1 · answered May 03 '12 at 12:20

8

Instead of using .ToLocalTime() inside your Linq query, use the opposite conversion outside of the query on the parameters.

var dateUniversal = dateParam.ToUniversalTime();

var query = myTable.Where( t => t.DateTime > dateUniversal );

answered May 03 '12 at 12:20

Phil

42,255
9
100
100

this does not work. ToUniversalTime() has to be in the linq query because the time is from each database record. – ove May 03 '12 at 12:27
4

If UTC is stored in the database, then if your parameters are UTC it should work - surely? – Phil May 03 '12 at 12:33

score 0 · Answer 2 · answered Sep 04 '13 at 16:41

I used a combination of Extensions plus relying on the value passed from the front-end of my app to be in LocalTime. So, If I had two date times.. like.. a start date and end date parameter for a report, I'd put them up there.. let the user submit in LocalTime.. then on the back end part of my Controller.. I'd use the sdate and edate variables in my Linq to Entities Query. The static extension methods I threw into a static helper class. Sorry that I'm over a year late. : )

    DateTime sdate = CalcHelper.AbsoluteStart(model.StartDate);
    DateTime edate = CalcHelper.AbsoluteEnd(model.EndDate);

    public static DateTime AbsoluteStart(this DateTime dateTime)
    {
        return dateTime.Date.ToUniversalTime();
    }
    public static DateTime AbsoluteEnd(this DateTime dateTime)
    {
        return AbsoluteStart(dateTime).AddDays(1).AddTicks(-1).ToUniversalTime();
    }

score 0 · Answer 3 · answered Jul 06 '15 at 19:57

if you get the timezone offset from the server you might be able to use EntityFunctions to apply the offset in your linq query

var offset = TimeZoneInfo.Local.BaseUtcOffset.TotalMinutes;
var result = db.Dates.Where(a => 
             EntityFunctions.DiffDays(EntityFunctions.AddMinutes(a.Date, offset), DateTime.Now) == 0);

score 0 · Answer 4 · answered Jul 10 '15 at 04:34

You can try like this:

var promotions = _promotionService.GetAll(
            x => (DbFunctions.TruncateTime(x.CreatedDate.Value) >= viewModel.GTXFromDate.Date)
            && (DbFunctions.TruncateTime(x.CreatedDate.Value) <= viewModel.GTXToDate.Date));

score -1 · Answer 5 · answered May 03 '12 at 12:27

-1

As a rule, you should store dates and times in UTC in your database.

Convert any local date/times to UTC in your code before you store them in the database.

Similarly, if you need to show local time from the UTC in the database, then convert it in code after the database fetch.

answered May 03 '12 at 12:27

Nick Butler

24,045
4
49
70

1

I am aware of this. I need to do a special case and found whether I can run .ToUniversalTime() or .ToLocalTime() using linq – ove May 03 '12 at 12:29
As you have found, this is not a good idea. You might do better by fixing your "special case". HTH. – Nick Butler May 03 '12 at 12:33
@in_visible "Special case's" are typically also known as a "wtf" by other coders. – Will Jul 10 '15 at 07:03

score -2 · Answer 6 · edited May 23 '17 at 10:26

-2

Found the solution on this question

public partial class Person { 

  partial void OnLoaded() { 
    this._BirthDate = DateTime.SpecifyKind(this._BirthDate, DateTimeKind.Utc); 
  } 
}

edited May 23 '17 at 10:26

Community

1
1

answered May 03 '12 at 11:55

Julien

3,509
20
35

1

Got this error. LINQ to Entities does not recognize the method 'System.DateTime SpecifyKind(System.DateTime, System.DateTimeKind)' method, and this method cannot be translated into a store expression. – ove May 03 '12 at 12:08

score -2 · Answer 7 · answered Jul 07 '15 at 08:25

-2

Convert DateTime to String for comparison, for example:

const string DATE_FORMAT = "yyyy/MM/dd";
var query = sampleTable.Where(x => x.DateTime.ToString(DATE_FORMAT) > DateTime.Now.ToString(DATE_FORMAT));

answered Jul 07 '15 at 08:25

Afshin Aghazadeh

547
4
17

This kind of string comparison hack might work for a `Where` clause; how would you do a similar thing for `GroupBy` though? – Dan Fitch Jul 07 '15 at 14:40

Linq-to-EF DateTime.ToLocalTime not supported

7 Answers7

Linked