What is the correct way to get a sortable string from a DateTime in a LINQ object?

Question

// EmployeeService: [WebMethod] public List<Employee> GetEmployees() { return ( from p in db.Persons where p.Type == 'E' select new Employee { Name = p.FullName, //HireDate = p.CreationDate.ToString(), // works, but not in the format I need //HireDate = p.CreationDate.ToString("s"), // throws a NotSupportedException //HireDate = Wrapper(p.CreationDate), // works, but makes me worry about performance // and feel dead inside } ).ToList<Employee>(); } private String Wrapper(DateTime date) { return date.ToString("s"); } // Elsewhere: public class Employee { public String Name; public String HireDate; } 

I am using a JavaScript framework that needs dates in the ISO 8601 style format, and this is exactly what the .ToString("s") call to the DateTime object will return.

Is there a cleaner / more efficient way to do this in LINQ to SQL?

Answer 1

I believe the Wrapper trick is as good as you can get in this situation. Unfortunately.

Update: It seems that here it was suggested again: Linq to Sql - DateTime format - YYYY-MMM (2009-Mar) . The answer was pretty much “sorry” there; considering who participated in this issue, I am really sure that you cannot do better.

Answer 2

The problem is that when using IQueryable provider tries to translate all LINQ expressions into something that it can send to the database. He does not know what to do with ToString("s") , so a NotSupported exception is NotSupported .

If you must add .AsEnumerable() before calling Select , then it should work. The difference is that the Person object will be fully loaded into memory, then the Select method will be launched, and all this will be executed as a compiled .NET method, and not as SQL. Thus, essentially, anything after AsEnumerable() will be executed in memory, and not in the database, so it is usually not recommended to do this until you trim the number of rows as much as possible (i.e., after all Where and OrderBy with )