none
Group and Count RRS feed

  • Question

  • Hello,

    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; }
    } // DataModel
    But maybe it should be something like:
    public class DataModel {
      public DateTime Date { get; set; }
      public KeyValuePair<String, Int32> LogsCount { get; set; }
    } // DataModel

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

    I am notsure if this is the best format for my DataModel ...

    How can I do this?

    Thank You,

    Miguel

    Monday, October 22, 2012 1:37 PM

Answers

  • I did try the following:

    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, Type = x.Type })
      .Select(x => new { Year = x.Key.Year, Month = x.Key.Month, Day = x.Key.Day, Type = x.Type, Count = x.Count() })
      .AsEnumerable()
      .Select(x => new DataModel { Date = new DateTime(x.Year, x.Month, x.Day), Type = x.Type, Count = x.Count }).ToList();

    Where DataModel is the following:

      public class DataModel {
        public DateTime Date { get; set; }
        public String Level { get; set; }
        public Int32 Count { get; set; }
      } // DataModel

    This seems to work but then I am having problems in filling the missing Dates for each type.

    And I am not sure if this is the best model format so I can later find the Count timeseries for only Error Type.

    I think in each DateModel I should have the Counts for each type.

    Thank You,

    Miguel

    • Proposed as answer by Alexander Sun Thursday, October 25, 2012 6:58 AM
    • Marked as answer by Alexander Sun Monday, November 5, 2012 9:31 AM
    Monday, October 22, 2012 8:48 PM

All replies

  • Hi MDMoura;

    I have modified your query to get the results you want. I changes the data type of LogsCount from KeyValuePair to a Dictionary because Linq needs a collection that has a default constructor.

    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, Errors = x.Where( y => y.Type == "Error").Count(), Warn = x.Where( y => y.Type == "Warn").Count(), Info = x.Where( y => y.Type == "Info").Count(), Total = x.Count() }).AsEnumerable()
    		.Select(x => new DataModel { 
                     Date = new DateTime(x.Year, x.Month, x.Day),
                     LogsCount = new Dictionary<string, int>() {
                         { "Error", x.Errors },
                         { "Warn", x.Warn },
                         { "Info", x.Info },
                         { "Total", x.Total }
                    }}
        ).ToList();
    		
    public class DataModel {                                  		
      public DateTime Date { get; set; }                      
      public Dictionary<String, Int32> LogsCount { get; set; }
    }

    Not sure of the data type of Type in the database so I used string. You may have to modify it to match you data type.       

      


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".


    Monday, October 22, 2012 3:20 PM
  • There is a problem with your query:

    I am not sure that Log types are only Error, Warn and Info ... There might be more.

    I do not want to restrict to those ...

    And how to fill the empty dates?

    Thank You,

    Miguel

    Monday, October 22, 2012 3:25 PM
  • _contextLogs 
       .Where(x => x.Created >= dateMinimum && x.Created <= dateMaximum) 
       .GroupBy(x => x.Type, (logtype, logs) => new { Key = logtype, Year = x.Created.Year, Month = x.Created.Month, Day = x.Created.Day}) 
       .GroupBy(y => y.Key, y.logs.Year, logs.Month, logs.Day)
       .Select(y => new {LogType = y.Key, ....., Count = y.logs.Count())
    

    Monday, October 22, 2012 3:26 PM
  • _contextLogs 
       .Where(x => x.Created >= dateMinimum && x.Created <= dateMaximum) 
       .GroupBy(x => x.Type, (logtype, logs) => new { Key = logtype, Year = x.Created.Year, Month = x.Created.Month, Day = x.Created.Day}) 
       .GroupBy(y => y.Key, y.logs.Year, logs.Month, logs.Day)
       .Select(y => new {LogType = y.Key, ....., Count = y.logs.Count())

    I get the error:

      Cannot convert lambda expression to type 'System.Collections.Generic.IEqualityComparer<string>' because it is not a delegate type

    On (logtype, logs) ...

    Any idea why?

    Thank You,

    Miguel

    Monday, October 22, 2012 8:30 PM
  • I did try the following:

    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, Type = x.Type })
      .Select(x => new { Year = x.Key.Year, Month = x.Key.Month, Day = x.Key.Day, Type = x.Type, Count = x.Count() })
      .AsEnumerable()
      .Select(x => new DataModel { Date = new DateTime(x.Year, x.Month, x.Day), Type = x.Type, Count = x.Count }).ToList();

    Where DataModel is the following:

      public class DataModel {
        public DateTime Date { get; set; }
        public String Level { get; set; }
        public Int32 Count { get; set; }
      } // DataModel

    This seems to work but then I am having problems in filling the missing Dates for each type.

    And I am not sure if this is the best model format so I can later find the Count timeseries for only Error Type.

    I think in each DateModel I should have the Counts for each type.

    Thank You,

    Miguel

    • Proposed as answer by Alexander Sun Thursday, October 25, 2012 6:58 AM
    • Marked as answer by Alexander Sun Monday, November 5, 2012 9:31 AM
    Monday, October 22, 2012 8:48 PM