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.

Answer 2

Assuming you're using JSON.NET as the JSON serializer, you can apply the JsonConverterAttribute to the date property to specify a custom converter.

[JsonConverter(typeof(MyDateConverter))]
public DateTime? date { get; set; }

You can use the DateTimeConverterBase class as a base class for your converter.

Here's a possible implementation for MyDateConverter:

class CustomDateTimeConverter : DateTimeConverterBase
{
    private const string Format = "dd.MM.yyyy";

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        DateTime d = (DateTime)value;
        string s = d.ToString(Format);
        writer.WriteValue(s);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        if (s == null)
            return null;
        string s = (string)reader.Value;
        return DateTime.ParseExact(s, Format, null);
    }
}

---

Another option is to exclude the date property from the serialization (using the JsonIgnoreAttribute), and add another property of type String that converts to and from the desired format. Here's an implementation of this solution:

public class UserCountResult
{
    [JsonIgnore]
    public DateTime? date { get; set; }
    [JsonProperty("date")]
    public string DateAsString
    {
        get
        {
            return date != null ? date.Value.ToString("dd.MM.yyyy") : null;
        }
        set
        {
            date = string.IsNullOrEmpty(value) ? default(DateTime?) : DateTime.ParseExact(value, "dd.MM.yyyy", null);
        }
    }

    public int users { get; set; }
    public int visits { get; set; }
}

Answer 3

If this is using SQL Server, you can concat the result of SQL functions

using System.Data.Entity.SqlServer;    

...

date = SqlFunctions.DatePart("year",g.Key)
    +"-"+SqlFunctions.DatePart("month",g.Key)
    +"-"+SqlFunctions.DatePart("day",g.Key)

Answer 4

Something like this should work:

date = new Date(parseInt(g.Key.substr(6)));

The substr will pull off the "/Date(" string, parseInt will pull just the integer and Date will give you a new date object.

EDIT:

Just found this SO question that supports this answer.

How do I format a Microsoft JSON date?