CODEWITHSUNDEEP
c#linqdatetimeexceptionlinq-to-entities
Steven
Steven

Reputation: 18859

LINQ to Entities does not recognize the method 'System.DateTime ConvertTimeFromUtc(System.DateTime, System.TimeZoneInfo)' method

I have a collection of Errors that I'm pulling in from my database. The time is stored as UTC, but I want to convert it to CST:

var errors = _errorsRepository.Errors.
    Select(e => new ErrorViewModel
    {
        ErrorId = e.ErrorId,
        Application = e.Application,
        Host = e.Host,
        Type = e.Type,
        Source = e.Source,
        Message = e.Message,
        User = e.User,
        StatusCode = e.StatusCode,
        TimeUtc = TimeZoneInfo.ConvertTimeFromUtc(
            e.TimeUtc, TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time")),
        Sequence = e.Sequence,
        AllXml = e.AllXml
     });

But I'm getting this error:

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

Anyone know what I can do to get around this?

Upvotes: 4

Views: 3113

Answers (1)

Olaf Keijsers
Olaf Keijsers

Reputation: 566

LINQ to Entities will try to convert your LINQ query into a SQL query. Because part of what you wrote cannot be translated to SQL, you are getting this error.

You can get around this by first converting the query to an in-memory object by using ToList(), and then using LINQ to Objects to get the desired result:

var errors = _errorsRepository.Errors.ToList().
Select(e => new ErrorViewModel
{
    ErrorId = e.ErrorId,
    Application = e.Application,
    Host = e.Host,
    Type = e.Type,
    Source = e.Source,
    Message = e.Message,
    User = e.User,
    StatusCode = e.StatusCode,
    TimeUtc = TimeZoneInfo.ConvertTimeFromUtc(
        e.TimeUtc, TimeZoneInfo.FindSystemTimeZoneById("Central Standard Time")),
    Sequence = e.Sequence,
    AllXml = e.AllXml
 });

Note that this will get ALL the errors from _errorsRepository into memory first. In this case it looks like that doesn't matter to you though, since you were going to get all of them anyway.

Upvotes: 6

Related Questions