Entity Framework 4 / Linq: How to convert from DateTime to string in a query?

Question

I have the following query:

from a in Products select new ProductVM     {          id = a.id,          modified = a.modified.ToString()     } 

Which gives me an error of:

LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression. 

The modified in the Products table is DateTime. The modified in the ProductVM class is string.

Any ideas? This has to be a trivial issue.

Answer 1

Here's an alternative:

.Select( p -> SqlFunctions.StringConvert((double)                   SqlFunctions.DatePart("m", p.modified)).Trim() + "/" +               // SqlFunctions.DateName("mm", p.modified) + "/" + MS ERROR?               SqlFunctions.DateName("dd", p.modified) + "/" +               SqlFunctions.DateName("yyyy", p.modified) 

Apparently DateName("MM", ..) spells out the month name where DatePart("mm", ..) provides a numeric value, thus the StringConvert( ), but this left pads the result with spaces, thus the .Trim().

Answer 2

ToString() is not supported in Linq to Entities - there is a list of function helpers as part of SqlFunctions but this doesn't support Date to string conversion.

Easiest would be to first project to an anonymous type within the query and then cast to an IEnumerable by using AsEnumerable() - after that you can use ToString() because you are now using Linq to Objects for the remainder of the query expression (there's a lengthy article on this topic here).

   var results = Products.Select( p => new { a.id, a.modified })                          .AsEnumerable()                          .Select(p => new ProductVM()                                  { id = p.id,                                    modified = p.modified.ToString()                                  });