In this article, you will learn how to convert DateTime to string (yyyy/mm/dd) format in ViewModel/Whatever class with IQueryable.
Consider this scenario: You have an entity model and one of the properties is DateTime type. In the IQueryable query, you want to select that property as string with the format 2017/01/09 into a ViewModel class.
A quick thought is to employ the ToString("yyyy-MM-dd") method but during run time, the code throws the error "LINQ to entities that do not recognize the method 'System.String ToString(System.String)' method and this method cannot be translated into a store expression.
After Googling around, I found the problem could be solved by using the combination of SqlFunctions.Replicate, SqlFunctions.StringConvert and SqlFunctions.DateName. Refer to the listing 1.
Listing 1
What if the property is nullable datetime type? The codes are about the same, except a little longer and the month is accessible through the Value property. The code in listing 2 will display the UpdatedDateString value in yyyy/mm/dd format and empty string, if the property value is null.
Listing 2
Conclusion
There are other alternative ways to approach this issue and I am not suggesting this is the only solution. I just wanted to share my findings with the community.
Reference
https://msdn.microsoft.com/en-us/library/ms174395.aspx.
Pro WPF: Windows Presentation Foundation in .NET 3.0