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
– David, Commented Oct 1, 2014 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
– Husrat Mehmood, Commented Oct 1, 2014 at 17:06
Why someone downvoted,kindly don't forget to give comments while downvoting. — Husrat Mehmood
– Husrat Mehmood, Commented Oct 1, 2014 at 17:17

David · Accepted Answer · 2014-10-01 17:12:11Z

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 1, 2014 at 17:12

David

221k42 gold badges245 silver badges337 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Husrat Mehmood Over a year ago

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.

David Over a year ago

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

Husrat Mehmood Over a year ago

Great Answer Thank you for taking time to address my problem.

Nikita Over a year ago

however, there is an issue if need to filter by a string from the client, e.g. by 'Oct' ))

crthompson · Accepted Answer · 2014-10-01 17:05:02Z

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 1, 2014 at 17:05

crthompson

15.9k6 gold badges64 silver badges87 bronze badges

answered Oct 1, 2014 at 17:03

Haedrian

4,3202 gold badges36 silver badges54 bronze badges

4 Comments

David Over a year ago

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

Servy Over a year ago

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.

shawty Over a year ago

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 Over a year ago

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.

Collectives™ on Stack Overflow

How can I convert DateTime to String in Linq Query?

2 Answers 2

4 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related