format date in linq query result

Question

The following linq to entities query gives the result below:

public class UserCountResult
{
    public DateTime? date { get; set; } // **should this be string instead?**
    public int users { get; set; }
    public int visits { get; set; }
}

public JsonResult getActiveUserCount2(string from = "", string to = "")
{

    var query = from s in db.UserActions
                    group s by EntityFunctions.TruncateTime(s.Date) into g
                    select new UserCountResult
                    {
                        date = g.Key, // can't use .toString("dd.MM.yyyy") here
                        users = g.Select(x => x.User).Distinct().Count(),
                        visits = g.Where(x => x.Category == "online").Select(x => x.Category).Count()
                    };

    return Json(query, JsonRequestBehavior.AllowGet);

}

Result:

[{"date":"\/Date(1383433200000)\/","users":21,"visits":47},{"date":"\/Date(1383519600000)\/","users":91,"visits":236}]

Instead of something like /Date(1383433200000)/, I need the date in format "dd.MM.yyyy", e.g.

[{"date":"29.11.2013","users":21,"visits":47},{"date":"30.11.2013","users":91,"visits":236}]

I found no way as to how to change the format in the query and I'm not sure what to do.. I don't even understand why g.Key is a nullable .. Thanks for any input!

Answer 1

g.Key is nullable because that's the signature of EntityFunctions.TruncateTime. http://msdn.microsoft.com/en-us/library/dd395596.aspx.

To exit from Linq to Entities, you can leave the query as is, and project it after the fact:

return Json(query.AsEnumerable().Select(r => new 
    {
        date = r.date.GetValueOrDefault().ToString("dd.MM.yyyy"),
        users = r.users,
        visits = r.visits
    }), JsonRequestBehavior.AllowGet);

It's not pretty, but that's Linq to Entities for you.