Using Linq for ObjectDataSource: How to transform datetime using ToShortTimeString?

Question

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?

Answer 1

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();

Answer 2

I like Mehrdad's answer a lot. It not only solves the problem, it also teaches me something about Linq as well. Thank you!

I've kept banging away at the problem, though, and did come up with a different approach that I'll describe here in case someone else stumbling on this question wants to consider. My Linq to SQL code now reads:

return classQuery.Select(p => new SelectClassData
                              {
                                   ClassID = p.ClassID,
                                   Title = p.Title,
                                   sDate = p.StartDate,
                                   eDate = p.EndDate
                              }).ToList();

Note that sDate and eDate are now DateTime objects instead of strings. In the "SelectClassData" object, I simply changed the declaration so that access to the StartDate, EndDate and TimeOfClass variables go through a property getter:

public class SelectClassData
{
    public int ClassID { get; set; }
    public string Title { get; set; }
    public DateTime sDate { get; set; }
    public DateTime eDate { get; set; }
    public string StartDate { get { return GetSDate(); } }
    public string EndDate { get { return GetEDate(); } }
    public string TimeOfClass { get { return GetTimeOfClass(); } }

    protected string GetSDate()
    {
        return sDate.ToShortDateString();
    }

    protected string GetEDate()
    {
        return eDate.ToShortDateString();
    }

    protected string GetTimeOfClass()
    {
        return sDate.ToShortTimeString() + " - " + eDate.ToShortTimeString();
    }
}

That is, I set the sDate and eDate via the LinqToSql but do the "ToShortTimeString" and "ToShortDateString" transformations after the Linq retrieval by implementing it in the target data class.