C# 在 linq 查询结果中格式化日期

format date in linq query result

提问by peter

The following linq to entities query gives the result below:

以下 linq to entity 查询给出了以下结果：

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（1383433200000）/这样的东西，我需要格式为“dd.MM.yyyy”的日期，例如

[{"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!

我找不到如何更改查询中的格式的方法，我不知道该怎么做.. 我什至不明白为什么 g.Key 是可空的.. 感谢您的任何输入！

采纳答案by Rob Lyndon

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

g.Key可以为空，因为这是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:

要从 Linq 退出到实体，您可以保留查询原样，并在事后对其进行投影：

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.

它并不漂亮，但它是适合您的 Linq to Entities。

回答by paqogomez

Something like this should work:

这样的事情应该工作：

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

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

The substrwill pull off "/Date(" string, parseIntwill pull only 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?

如何格式化 Microsoft JSON 日期？

回答by Thomas Levesque

Assuming you're using JSON.NET as the JSON serializer, you can apply the JsonConverterAttributeto the dateproperty to specify a custom converter.

假设你正在使用JSON.NET作为JSON序列，可以应用JsonConverterAttribute到date属性来指定一个自定义的转换器。

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

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

您可以将该DateTimeConverterBase类用作转换器的基类。

Here's a possible implementation for MyDateConverter:

这是一个可能的实现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 dateproperty from the serialization (using the JsonIgnoreAttribute), and add another property of type Stringthat converts to and from the desired format. Here's an implementation of this solution:

另一种选择是date从序列化中排除该属性（使用JsonIgnoreAttribute），并添加另一个String与所需格式相互转换的类型的属性。这是此解决方案的实现：

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; }
}

回答by Carter Medlin

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

如果这是使用 SQL Server，则可以连接 SQL 函数的结果

using System.Data.Entity.SqlServer;    

...

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