Use linq to provide a running total of how many users existed by date

c#, entity-framework, linq

Solution

Well, the basic would be something like that, I would say...

var result = repository.Users
             .GroupBy(m => EntityFunctions.TruncateTime(addDate))
             .Select(g => new {
                 Date = g.Key,
                 TotalMemberShip = g.Count(),
                 IsActiveWithStatus1 = g.Count(x => x.isActive && x.status == 1),
                 IsActiveWithStatus2 = g.Count(x => x.isActive && x.status == 2),
                 IsInactiveWithStatus1 = g.Count(x => !x.isActive && x.status == 1),
                 IsInactivewithStatus2 = g.Count(x => !x.isActive && x.status == 2)
             });

EDIT (from comment)

hmmm, I would try this, but absolutely not sure that this will work

same as before, just change the line

TotalMemberShip = repository.Users.Count(x => EntityFunctions.TruncateTime(addDate) <= g.Key)

EDIT 2

Well, maybe rather this one (I use this syntax, which has the `let` keyword, if you need the "aggregation" for all your results).

from item in repository.Users
group item by EntityFunctions.TruncateTime(addDate) into g
let datesBefore = repository.Users.Where(x => EntityFunctions.TruncateTime(addDate) <= g.Key)
select new {
  Date = g.Key,
  TotalMemberShip = datesBefore.Count(),
  IsActiveWithStatus1 = datesBefore.Count(x => x.isActive && x.status == 1),
  //etc.
  //if you need datas BEFORE (and equal) use datesBefore.Count
  //if you need datas "at the exact day" use g.Count
}

Problem

Original source