Post
Ask Question

Convert DateTime To String Format Using 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

  1. IQueryable < TestObjectViewModel > yourModel = from p in db.Table1  
  2. select new TestObjectViewModel  
  3. {  
  4.     Id = p.Id,  
  5.         Text = p.Text,  
  6.         CreatedDateString = SqlFunctions.DateName("year", p.CreatedDate) + "/" + SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double) p.CreatedDate.Month).TrimStart().Length) + SqlFunctions.StringConvert((double) p.CreatedDate.Month).TrimStart() + "/" + SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", p.CreatedDate).Trim().Length) + SqlFunctions.DateName("dd", p.CreatedDate).Trim()  
  7. };   

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

  1. IQueryable < TestObjectViewModel > yourModel = from p in db.Table1  
  2. select new TestObjectViewModel   
  3. {  
  4.     Id = p.Id,  
  5.         Text = p.Text,  
  6.         UpdatedDateString = SqlFunctions.DateName("year", p.UpdatedDate) + SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double) p.UpdatedDate.Value.Month).TrimStart().Length) + SqlFunctions.Replicate("0", 2 - SqlFunctions.StringConvert((double) p.UpdatedDate.Value.Month).TrimStart().Length) + SqlFunctions.StringConvert((double) p.UpdatedDate.Value.Month).TrimStart() + SqlFunctions.Replicate("/", 2 - SqlFunctions.StringConvert((double) p.UpdatedDate.Value.Month).TrimStart().Length) + SqlFunctions.Replicate("0", 2 - SqlFunctions.DateName("dd", p.UpdatedDate).Trim().Length) + SqlFunctions.DateName("dd", p.UpdatedDate).Trim()  
  7. };   

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.


Similar Articles