Date format resulting from linq query

Question

The following linq to entities query yields the following result:

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 a date in the format "dd.MM.yyyy", for example

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

I have not found a way to change the format in the request, and I'm not sure what to do. I don't even understand why g.Key is null. Thanks for any input!

Answer 1

g.Key is null because the signature is EntityFunctions.TruncateTime . http://msdn.microsoft.com/en-us/library/dd395596.aspx .

To exit Linq into Entities, you can leave the request as is and design 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); 

This is not very, but what Linq for Entities is for you.

Answer 2

Assuming you are using JSON.NET as a 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 the 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 serialization (using JsonIgnoreAttribute ) and add another property of type String , which is converted to and from the desired format. Here's the 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

Something like this should work:

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

substr pull the string "/ Date (", parseInt pull out only an integer, and Date will provide you with a new date object.

EDIT:

Just found this question that supports this answer.

How do I set up Microsoft JSON date?