C# – Using Linq for ObjectDataSource: How to transform datetime using ToShortTimeString

c++linqobjectdatasource

I am accessing a business class using an ObjectDataSource and trying to produce output that makes sense to the user. The return values describe a Class (as in Classroom and teaching, not software). I would like to show the time of the class as a range like this: "9:00 AM – 10:00 AM".

This is the Linq Query I am using to pull the data:

return classQuery.Select(p => new SelectClassData
                              {
                                   ClassID = p.ClassID,
                                   Title = p.Title,
                                   StartDate = p.StartDate.ToShortDateString(),
                                   EndDate = p.EndDate.ToShortDateString(),
                                   TimeOfClass =
                                   p.StartDate.ToShortTimeString() + " - " +
                                                       p.EndDate.ToShortTimeString()
                               }).ToList();

As you can see, I encode the start and ending times in the starting and ending dates even though these could potentially be on different dates.

When I execute this code I get:

"Could not translate expression 'p.EndDate.ToShortTimeString()' into SQL and could not treat it as a local expression."

I know that I am projecting the results but, being new to Linq, I had assumed that the C# call to ToShortTimeString happened after the projection. Can anyone help me figure out how to get the string I'm looking for?

Best Solution

The reason is the query is being used in LINQ to SQL. LINQ to SQL treats queries as expression trees. It has mappings defined for some methods (for instance, Contains) but since it doesn't really execute them, it can't work on arbitrary methods. It parses the query and submits it to SQL server. The equivalent of the query will be executed as a SQL statement on the database server and the result will come back. The problem is ToShortTimeString() does not have an equivalent SQL translation in LINQ to SQL. The trick used here is to fetch data from SQL server and call the method on the client side (AsEnumerable will do this).

return classQuery.Select(p => new { p.ClassID, p.Title, p.StartDate, p.EndDate })
   .AsEnumerable()
   .Select(p => new SelectClassData { 
       ClassID = p.ClassID, 
       Title = p.Title, 
       StartDate = p.StartDate.ToShortDateString(), 
       EndDate = p.EndDate.ToShortDateString(), 
       TimeOfClass = p.StartDate.ToShortTimeString() + " - " + p.EndDate.ToShortTimeString() })
   .ToList();