How to convert DateTime to String in Linq Query?

Question

I need to display the 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 the error message I get

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

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

Please give me the right decision how I can do this. I know that in plain C # code there are several options available, but in Linq, how can we do this.

Answer 1

This is because LINQ to Entities is trying to convert the expression tree to an SQL query, and although .ToString() can be translated into SQL, .ToString(string) cannot. (SQL does not have the same concept of formatting strings.)

To solve this problem, do not format in the request, execute it in the display logic. Make the request 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 remains the DateTime value. It does not format the data, just transfers it. (As the DTO should.)

Then, when you display the value, format. For example, is this used in the MVC view ?:

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

You can even add a simple property to PerformanceReviewsDTO for a formatted display:

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

Then any property binding on the DTO can simply be bound to this (if in this case it is a one-way binding).

Answer 2

As I usually solve this problem, first I just get the data and then select it in memory.

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

By placing ToArray (or in a list or something else), it will complete the sql query part and then do the rest from the collection in memory - this should be fine.