How can I convert DateTime to String in Linq Query?

c# entity-framework linq linq-to-entities

Question

I must display the date inMMM dd,YYYY format.

var performancereviews = from pr in db.PerformanceReviews
                                         .Include(a => a.ReviewedByEmployee)
                                 select new PerformanceReviewsDTO
                                 {
                                     ReviewDate=pr.ReviewDate.ToString("MMM dd,yyyy"),
                                     EmployeeName=pr.ReviewedByEmployee.Name,
                                     JobTitle=pr.ReviewedByEmployee.JobTitle,
                                     ReviewerComments=pr.CommentsByReviewer,
                                     EmployeeComments=pr.CommentsByEmployee
                                 };

This is the error message that I'm receiving.

ExceptionMessage: LINQ to Entities does not recognize the method 'System.String ToString(System.String)' method, and this method cannot be translated into a store expression. ExceptionType: System.NotSupportedException

If I applyToString on pr.ReviewDate Errors appear.

Please assist me with the right solution. how do I go about doing this. I am aware that there are many alternatives when programming in regular C#, but how can we accomplish it with Linq?

1
7
10/1/2014 5:08:59 PM

Accepted Answer

As LINQ to Entities attempts to turn the expression tree into a SQL query, this is taking place..ToString() If can were to be converted to SQL,.ToString(string) I cannot. (SQL doesn't use the same string formatting concepts.)

To fix this, perform the formatting in the display logic rather than the query. Ask as straightforward a question as you can:

select new PerformanceReviewsDTO
{
    ReviewDate=pr.ReviewDate,
    EmployeeName=pr.ReviewedByEmployee.Name,
    JobTitle=pr.ReviewedByEmployee.JobTitle,
    ReviewerComments=pr.CommentsByReviewer,
    EmployeeComments=pr.CommentsByEmployee
}

Given this,PerformanceReviewsDTO.ReviewDate remains aDateTime value. It simply transports the data without formatting it. (As a DTO ought to.)

Apply the formatting after you display the value. Is this being used, for instance, in an MVC view?

@Model.ReviewDate.ToString("MMM dd,yyyy")

Even adding a straightforward attribute toPerformanceReviewsDTO to format the display

public string FormattedReviewDate
{
    get { return ReviewDate.ToString("MMM dd,yyyy"); }
}

If it's a one-way binding in this situation, whatever is binding to properties on the DTO can just bind to that.

13
10/1/2014 5:12:11 PM

Popular Answer

I typically approach this challenge by first simply gathering the facts and then choosing it from memory.

var performancereviews = from pr in db.PerformanceReviews
                                      .Include(a => a.ReviewedByEmployee)
                                      .ToArray()
                                      .Select( ....);

By utilizingToArray It will complete the SQL query portion and then do the remaining operations from the collection in memory, which should be acceptable.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow