LINQ to Entities does not recognize the DateTime.ToLocalTime() method

Question

I'm developing a MVC website. In one of the Views I need to use:

return View(db.Logs
    .OrderByDescending(l => l.Date)
    .Where(l => ((DateTime)l.Date).ToLocalTime().ToString().Contains(search) 
            || l.Name.Contains(search))
    .ToList()
    .ToPagedList(pageNumber, pageSize));

But I get this error:

LINQ to Entities does not recognize the method 'System.DateTime ToLocalTime()' method, and this method cannot be translated into a store expression.

I need transform l.Date toLocalTime and then compare with search

How can I do it?

as the error says ToLocaltime() doesnt support in Linq to entities. you need to perform that cast after your linq to Entities operation — user9405863, Apr 18 '18 at 14:41
have you tried (((DateTime)l.Date).ToLocalTime().ToString()).Contains(search)? — Ole Albers, Apr 18 '18 at 14:43
You are not able to use cast with Linq, try declare a variable before your operation with the same type of your property — Samuel, Apr 18 '18 at 14:45
Instead of trying to convert the table row value to LocalTime, try to convert the search criteria to UTC datetime. This might be [similar](https://stackoverflow.com/a/10431265/5246145) — 3615, Apr 18 '18 at 14:47
You should materialize the Logs into a list: var orderedLogs = db.Logs.OrderByDescending(l => l.Date).ToList(). And then filter orderedLogs using LINQ. Less efficient, but should work. — Alexandru Popa, Apr 18 '18 at 14:48
@Samuel how can i do it? I need l.Date that it is inside lambda expresion. — Jmyebenes, Apr 18 '18 at 14:48
@Jmyebenes, Have you tried? I'm pretty sure it should work. You can't use .ToLocalTime() on an entity because it can't convert it to SQL code, but it should work on an in-memory list. — Alexandru Popa, Apr 18 '18 at 14:53
You can change it to return View(db.Logs.OrderByDescending(l => l.Date).ToArray() .Where(l => ((DateTime)l.Date).ToLocalTime().ToString().Contains(search) || l.Name.Contains(search)).ToList().ToPagedList(pageNumber, pageSize)); but only for testing purposes. This thing get all data from server and do filtering in-memory — Evgeny Gorbovoy, Apr 18 '18 at 14:54
What are you actually trying to do? Converting dates to strings (without even specifying a format) and then checking them for a substring seems like a _really_ bizarre way to implement a date search. — Joe Farrell, Apr 18 '18 at 14:57
@JoeFarrell that is because i have searchbox, i can search part of name or part of date or whatever you want, so i need convert to string without format because it can be a name, not a date. — Jmyebenes, Apr 18 '18 at 15:01

Rush Frisby · Answer 1 · 2018-04-18T15:22:18.573

1) Normalize your Date column data so that all rows in Logs are of the same time zone. Best option because you don't have do a conversion in a query which will make the query faster, plus for other reasons like making reports easier to read.

2) Use a sproc or parameterized query in which you convert Date using the SQL function TODATETIMEOFFSET(<date>,<offset>)

3) If you want to convert it post-query you should call ToList() on the query so that it pulls back the data then convert the date to the local time zone:

var data = db.Logs.OrderByDescending(l => l.Date)
.Where(l => ((DateTime)l.Date).ToString().Contains(search) 
|| l.Name.Contains(search))
.Skip((pageNumber-1)*pageSize).Take(pageSize)
.ToList(); //ToList() causes the query to execute

//now you can convert the Date
foreach (var item in data)
{
    item.Date = item.Date.ToLocalTime();
}

return View(data);

You should also use Skip and Take in the query instead of ToPagedList so that you're not pulling back ALL of the data.

4) In your View (not the Controller) you can call ToLocalTime when displaying the variable... (assuming Razor) @Model.Date.ToLocalTime()

You must remember though that ToLocalTime is going to convert the Date to the local time that Your Server is set to, not the local time of the user viewing the web page. If you want to do that then you need to either get their time zone from the request headers or through javascript and pass it with the request, then use that value to format the Date before it's passed back to the View - or just do that conversion client-side using javascript.

I want save utc date in sql but show this date in local date format, so first point is descarted. How work the second option? — Jmyebenes, Apr 18 '18 at 14:54
Displaying the date in local format should be handled on the front-end not in the database query. — Rush Frisby, Apr 18 '18 at 14:56

score 1 · Answer 2 · answered Apr 18 '18 at 14:59

Everyone has already pointed out the obvious error but think about what your asking your code to do. Iterate through every record, operate on it to convert to local time, convert it to a string then do a search on the string. This sounds like a horrible performance plan if you get any significant records in your database.

As Rush Frisby pointed out, if you went down this path then you really REALLY need to consider a stored procedure.

I think the real answer to your problem is you need better search logic overall. If you could pre-define if a search was a datetime before the query then you could already implement DT offset before you even hit the database and search by actual times not to mention avoiding the text search on name. Otherwise if it is just a free text search then you can juts hit the name column.

I'll probably be downvoted because this doesn't really answer the direct question BUT I think the best answer to your question is changing the overall search logic architecture.

score 0 · Answer 3 · answered Apr 18 '18 at 14:47

You can not call ToLocalTime method inside expression as EF does not know how to convert it to SQL statement. Check SqlFunctions when you get this sort of exception and try to find any appropriate method for your purposes.

Also consider storing that dates in local time. Or instead of comparing to search compare it to something like var dateSearchCriteroa = searchCriteria.ToNotLocal()

score 0 · Answer 4 · answered Apr 18 '18 at 14:49

LINQ to Entities will not translate most .NET Date methods (including the casting and datetime operation you used) into SQL since there is no equivalent of these methods that exists in SQL.

Alternatively You can convert your search filter to datatime format coming from database, rather than trying to convert it to LocalTime.

LINQ to Entities does not recognize the DateTime.ToLocalTime() method

4 Answers4

Linked