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

The premise for this is pretty simple, but I just can't seem to nail down how to describe this in linq. I am using EF, and I should be able to pull the data in a single query.

I have a user object with the following (simplified):

public class User
{
  public DateTime addDate;
  public int status;
  bool isActive
}

I want to create a report that takes a date range (i.e. startDate/endDate) that displays the following columns:

(the numbers don't add up, just for show)

The first column is the date, which the data is grouped by. The second is a running total for the number of user that had been added to the system at that time. The rest is a break down of the users first by the isActive bool, and then by the status.

Can I appeal to one of the linq gurus on SO for some help on this? I could just pull the data in and do it in code, but that's not optimal. I'd like to learn how this could be done in linq.

==================================

EDIT: I realized there was one piece I was not clear on. The numbers are not reflective of the number of members that joined that day, but rather the total to date. I believe I might be looking for an aggregate, but I'm not sure. Maybe a sum could work too.

So in the example, as of 1/13/14 there were 297 with an addDate of the 13th or earlier. On 1/14/14 no members were added, and finally on 1/15/14 three more users were added totaling 300 as of that date.

Answers


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
}

Need Your Help

Dropdown Filter for Polymorphic association nested_attribute

drop-down-menu ruby-on-rails-3.2 polymorphic-associations

I'm building an application in which the user can create a Company and add Investments made to that company. The investments can come from two sources user's Funds or companies' Coinvestors. Funds ...