Group and Count in Entity Framework

Question

I have a table with Logs and I am counting the Logs per day as follows:

// Count logs by day
IList<DataModel> models = _context.Logs
  .Where(x => x.Created >= dateMinimum && x.Created <= dateMaximum)
  .GroupBy(x => new { Year = x.Created.Year, Month = x.Created.Month, Day = x.Created.Day })
  .Select(x => new { Year = x.Key.Year, Month = x.Key.Month, Day = x.Key.Day, Count = x.Count() })
  .AsEnumerable()
  .Select(x => new DataModel { Date = new DateTime(x.Year, x.Month, x.Day), LogsCount = x.Count })
  .ToList();

// Fill empty days with dates which contains all days in range
models.AddRange(dates.Where(x => !models.Any(y => y.Date == x.Date)).Select(x => new DataModel { Date = x, LogsCount = 0 }));

This is working if I want to count all logs by day independently of the type.

But I would like to count logs by day and type (Error, Warn, Info, ...).

I tried to add x.Type to group but at the end I get only 3 items.

At the moment my DataModel is the following:

public class DataModel
{
    public DateTime Date { get; set; }
    public Int32 LogsCount { get; set; }
}

But maybe it should be something like:

public class DataModel
{
    public DateTime Date { get; set; }
    public KeyValuePair<String, Int32> LogsCount { get; set; }
}

Where LogsCount has a string which holds the Type and Int32 which contains the count.

How can I do this?

score 4 · Answer 1 · edited Jan 22 '18 at 23:21

4

Might want to consider using entity functions for grouping by date.

Example:

var results = query.GroupBy(r => new
{
    SentDate = System.Data.Objects.EntityFunctions.TruncateTime(r.Launch.EmailDeliveredDate),
    EventSubTypeID = r.EmailEventSubtypeID
})
.Select(x => new
{
    x.Key.SentDate,
    x.Key.EventSubTypeID,
    NumResults = x.Count()
})
.ToList();

edited Jan 22 '18 at 23:21

superjos

12,189
6
89
134

answered Dec 20 '13 at 03:43

John Zumbrum

2,786
8
36
60

score 1 · Answer 2 · answered Oct 22 '12 at 14:53

1

Did you try something like this?

IList<DataModel> models = Logs
  .Where(x => x.Created >= dateMinimum && x.Created <= dateMaximum)
  .GroupBy(x => new { Year = x.Created.Year, Month = x.Created.Month, Day = x.Created.Day, Type = x.Type })
  .Select(x => new { Year = x.Key.Year, Month = x.Key.Month, Day = x.Key.Day, Count = x.Count(), Type = x.Key.Type })
  .AsEnumerable()
  .Select(x => new DataModel { Date = new DateTime(x.Year, x.Month, x.Day), LogsCount = x.Count, Type = x.Type })
  .ToList()


public class DataModel
{
    public DateTime Date { get; set; }
    public Int32 LogsCount { get; set; }
    public string Type { get; set; }
}

answered Oct 22 '12 at 14:53

Bobson

13,498
5
55
80

Yes, that was my first approach. And it works. But it gets confusing on how to fill the missing dates in the three types ... I am just not sure if that is the best approach ... How would you fill the empty dates in each type and add count = 0. Just like I do for total. – Miguel Moura Oct 22 '12 at 20:42
I'd probably do it in a `foreach` loop, across a list of dates generated as per [this answer](http://stackoverflow.com/a/3738781/298754). For each date, if the value doesn't exist, add it. I'm sure it can be done in LINQ, but it's not the way *I'd* do it. I could try and come up with the LINQ form if you'd like. – Bobson Oct 22 '12 at 21:51

Group and Count in Entity Framework

2 Answers2

Linked