How can I convert DateTime to String in Linq Query?

Question

I have to display date in MMM 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
                                 };

Here is error message that i am getting

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

When i apply ToString on pr.ReviewDate I get errors.

Kindly guide me with proper solution how can I accomplish this. I know in normal C# coding there are several options available but in Linq how can we do it.

Answer 1

This is happening because LINQ to Entities is trying to convert the expression tree into a SQL query, and while .ToString() can be translated into SQL, .ToString(string) can not. (SQL doesn't have the same concepts of string formatting.)

To resolve this, don't perform the formatting in the query, perform it in the display logic. Keep the query as simple as possible:

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

In this case PerformanceReviewsDTO.ReviewDate is still a DateTime value. It's not formatting the data, just carrying it. (Like a DTO should.)

Then when you display the value, perform the formatting. For example, is this being used in an MVC view?:

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

You might even just add a simple property to PerformanceReviewsDTO for the formatted display:

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

Then whatever is binding to properties on the DTO can just bind to that (assuming it's a one-way binding in this case).

Answer 2

The way I generally solve this problem is by first just getting the data and then selecting it in memory'.

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

By putting ToArray (or to List or whatever) it'll finish the sql query part and then do the rest from the collection in memory - which should be fine.