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.

Generally display formatting is performed in *display* logic, not when querying data. What is the type of `PerformanceReviewsDTO.ReviewDate`? What error does this code give you? — David, Oct 01 '14 at 17:04
@David I am aware of type if am storing string type value then definitly it should be string and it is string here is error 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 — Husrat Mehmood, Oct 01 '14 at 17:06
Why someone downvoted,kindly don't forget to give comments while downvoting. — Husrat Mehmood, Oct 01 '14 at 17:17

score 14 · Accepted Answer · answered Oct 01 '14 at 17:12

14

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).

answered Oct 01 '14 at 17:12

David

208,112
36
198
279

Thank you David for answering I have returing data from a Web Api and in Web Api I have only this place to convert it to proper format needed in my view for a mobile application.You can see I am using DTO(Data Transfer Objects) already I am looking for Haedrian solution figuring out how to do it. – Husrat Mehmood Oct 01 '14 at 17:15
@ProgrammingNinja: My last suggestion of adding a property to the DTO should satisfy that requirement. When the WebAPI serialization occurs, it should serialize the `FormattedReviewDate` property as well as the `ReviewDate` property. (If the naming is important, you can change them to something like `ReviewDate` and `PersistedReviewDate` respectively, so consuming code doesn't have to change.) As for the other posted answer, pay attention to the comments. It could "work" but could also have drastic side-effects. – David Oct 01 '14 at 17:18
Great Answer Thank you for taking time to address my problem. – Husrat Mehmood Oct 01 '14 at 17:25
however, there is an issue if need to filter by a string from the client, e.g. by 'Oct' )) – Nikita Jan 02 '22 at 17:22

score 2 · Answer 2 · edited Oct 01 '14 at 17:05

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.

edited Oct 01 '14 at 17:05

crthompson

15,653
6
58
80

answered Oct 01 '14 at 17:03

Haedrian

4,240
2
32
53

6

Keep in mind that materializing the entire collection into memory could easily have negative performance impacts, if the system isn't *currently* materializing the whole thing (which we can't tell from the posted code). – David Oct 01 '14 at 17:12
2

This results in none of the other work done in the `Select` being performed on the database, in particular the limitations as to what columns are returned. – Servy Oct 01 '14 at 17:15
Have an up vote, because sometimes this IS A SENSIBLE APPROACH, simply because you might not have control of the display logic, or your specs may be forcing you to do it this way (As I have just had to do). I didn't like doing it, but I had little choice in the matter. – shawty Jul 18 '17 at 19:07
For the record however, if you can do it in the Display Logic, I'm totally for that, but there's no reason why your answer should have been in Negative Figures IMHO. – shawty Jul 18 '17 at 19:08

How can I convert DateTime to String in Linq Query?

2 Answers2

Linked