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

IQueryable < TestObjectViewModel > yourModel = from p in db.Table1
select new TestObjectViewModel
{
    Id = p.Id,
        Text = p.Text,
        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()
};

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

IQueryable < TestObjectViewModel > yourModel = from p in db.Table1
select new TestObjectViewModel
{
    Id = p.Id,
        Text = p.Text,
        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()
};

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.