locked
Group And Count. Simplify Code RRS feed

  • Question

  • Hello,

    I am counting the number of users created per day as follows:

        IEnumerable<DateTime> dates = GetAllDatesForPeriod();
    
        var rows = context.Users
          .Where(x => x.Created >= minDate && x.Created <= max.Date)
          .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() }).ToList()
          .Select(x => new DataModel { Date = new DateTime(x.Year, x.Month, x.Day), Users = x.Count }).ToList();
            
        rows.AddRange(dates.Where(x => !rows.Any(y => y.Date == x)).Select(x => new DataModel { Date = x, Users = 0 }));

    The second Select is because I am not able to create a DateTime in the Ef query.

    The AddRange is to fill the missing dates with a Count = 0.

    This code is working. But is there a way to simplify this?

    Thank You,
    Miguel

    Wednesday, October 10, 2012 1:45 PM

Answers

  • Hi MDMoura;

    The following will give you what you want.

    var rows = context.Users
          .Where(x => x.Created >= minDate && x.Created <= max.Date)
          .GroupBy(x => new { Year = x.Created.Year, Month = x.Created.Month, Day = x.Created.Day })
          .Select(x => new 
              { 
                  Year = x.Select( d => d.Created ).FirstOrDefault() , 
                  Count = x.Count() 
              }).ToList()
    
    foreach (var row in rows)
    {
        row.Date = row.Date.Date;
    }
    

      


    Fernando (MCSD)

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

    Saturday, October 13, 2012 3:41 AM

All replies

  • Hi Miguel;

    Have you tried something like this?

    IEnumerable<DateTime> dates = GetAllDatesForPeriod();
    
    var rows = context.Users
          .Where(x => x.Created >= minDate && x.Created <= max.Date)
          .GroupBy(x => x.Created)
          .Select(x => new DataModel { Year = x.Key, Count = x.Count() }).ToList();
            
    rows.AddRange(dates.Where(x => !rows.Any(y => y.Date == x)).Select(x => new DataModel { Date = x, Users = 0 }));

      


    Fernando (MCSD)

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

    Thursday, October 11, 2012 3:10 AM
  • Correct me if I am wrong but in that case you are grouping by date including hour, minute, second ...

    So two users on the same day but different time would be placed in different groups.

    Thursday, October 11, 2012 9:09 AM
  • Hi MDMoura;

    Yes if time is set when saving records to the database having different times will cause different groups because of the time. I assumed that the time was not set and therefore the time is set to 12:00:00 AM by default and therefore would not have that problem. 

    If you are setting the time then I will try to come up with another way. I am leaving the office now and will be back in about 5 hours and will have a look at it then.

      


    Fernando (MCSD)

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

    Thursday, October 11, 2012 12:16 PM
  • Hi MDMoura;

    The following will give you what you want.

    var rows = context.Users
          .Where(x => x.Created >= minDate && x.Created <= max.Date)
          .GroupBy(x => new { Year = x.Created.Year, Month = x.Created.Month, Day = x.Created.Day })
          .Select(x => new 
              { 
                  Year = x.Select( d => d.Created ).FirstOrDefault() , 
                  Count = x.Count() 
              }).ToList()
    
    foreach (var row in rows)
    {
        row.Date = row.Date.Date;
    }
    

      


    Fernando (MCSD)

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

    Saturday, October 13, 2012 3:41 AM