Group and Count in Entity Framework

c# count entity-framework linq-to-entities

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?

1
4
10/22/2012 1:34:01 PM

Popular Answer

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();
4
1/22/2018 11:21:49 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow