How to count rows of a table grouped by shortdatestring?

Question

We have an database with an this structure:

public partial class ChartData
{
    public int Id { get; set; }
    public DateTime Timestamp { get; set; }
    public string Function { get; set; }
    public int Duration { get; set; }
    public bool IsError { get; set; }
}

Now we want to group the entries of this database by Timestamp.ToShortDateString() and then count the entries belonging to this date.

So for example we have:

2019-06-04 11:54:02,135,someFunction,30,False,
2019-06-04 11:55:03,135,someFunction,230,False,
2019-06-04 11:56:03,150,someFunction,4,True,
2019-06-05 11:54:03,230,someFunction,46,False,
2019-06-05 11:55:03,230,someFunction,46,False,

And I want this result:

{date: 2019-06-04, rows: 3}
{date: 2019-06-05, rows: 2}

public List <LogFileDTO> GetLogFilesData() 
{
    var items = db.ChartDatas.GroupBy(x = > new {
        x.Timestamp.ToShortDateString
    }).Select(x = > new LogFileDTO {
            date = x.Timestamp.First(),
            rows = x.Count ?
        }).ToList();
}

So I do not really know how to group this input by date and then count the rows of each group.

Answer 1

You simply need to group on the Date property of the TimeStamp and then project using Select on the Key of each IGropuing and Count like below :

var items = db.ChartDatas.GroupBy(x =>  x.Timestamp.Date)  // group only on Date
                       .Select(x => new LogFileDTO 
                                   {
                                     Date = x.Key.ToShortDateString(),
                                     Rows = x.Count()
                                   }).ToList();

Key will contain only date part of DateTime object and Count() will tell the number of rows for that date in the group.

Hope it helps!