C#- LINQ Grouping on a custom created date range

Question

So, I have this LINQ query:

var Result = from u in Users 
             group u by new {u.AccountType, u.Id,u.CreationDate} into usergroup
             select new {id=usergroup.Key.Id,CreationDate=usergroup.Key.CreationDate,AccountType=usergroup.Key.AccountType};

that returns the following data set:

I am able to get the individual group count like this:

var myresult=Result.GroupBy(n=>n.AccountType).Select(n=>new {AccountType=n.Key,TotalCount=n.Count()});

which gives me:

Now suppose, I define a custom date range of Months from January-December, how can I do a group on the first data-set to give me count of AccountType based on each month based on the CreationDate column into my custom date range?

is that [post: LINQ & Grouping data by a date range](https://stackoverflow.com/questions/27423512/linq-grouping-data-by-a-date-range) helpful? — Mong Zhu, Jan 21 '20 at 09:55
or maybe [this one: Group by date range , count and sort within each group LINQ](https://stackoverflow.com/questions/18852172/group-by-date-range-count-and-sort-within-each-group-linq) ? — Mong Zhu, Jan 21 '20 at 09:56
You can get a month from creation date and use it to group and filter — Pavel Anikhouski, Jan 21 '20 at 10:01
"Now suppose, I define a custom date range of Months from January-December" do you intend to filter before the grouping for this range? — Mong Zhu, Jan 21 '20 at 10:27
@MongZhu I do not intended to filter on the date range but the actual filter will be applied on the account type (which is shown on a doughnut graph). I just need the dataset from 1-12 month with count of each account type in that month. — Rahul Sharma, Jan 21 '20 at 10:29
@MongZhu The custom date range is only to group the data set into respective months. Once I have all the grouped data, then I can generate my graph on this. Once this is done, then I can filter them out on the account type. — Rahul Sharma, Jan 21 '20 at 10:53

score 1 · Accepted Answer · answered Jan 21 '20 at 10:17

1

I am trying to understand what type you would like your result to be in. If you want a list of Months, each of which would have a tally of Accounts per AccountType, then you could try something like this:

var myResult2 = result.GroupBy(o => o.CreationDate.Month).Select(monthGroup => new
    {
        Month = System.Globalization.DateTimeFormatInfo.CurrentInfo.GetMonthName(monthGroup.Key),
        Accounts = monthGroup.GroupBy(o => o.AccountType).ToDictionary(accountGroup => accountGroup.Key, accountGroup => accountGroup.Count())
    });

answered Jan 21 '20 at 10:17

Philip Atz

886
1
10
26

Thanks but this is only adding up to count of 13 whereas I have 40 elements in my data set. – Rahul Sharma Jan 21 '20 at 10:23
What do you mean when you say it "is only adding up to count 13"? There are 7 months in your dataset and each of them has multiple counts (one for each AccountType). The total of all of them should be 40... – Philip Atz Jan 21 '20 at 10:49
Sorry my bad. Yes you are right. Let me try it out. – Rahul Sharma Jan 21 '20 at 10:59
@RahulSharma if this is your accepted answer, then the "custom" date range does not play any role. I don't see any consideration of it in here – Mong Zhu Jan 21 '20 at 11:58

Florim Maxhuni · Answer 2 · 2020-01-21T10:08:34.493

0

Try this: I think this will do your job

.GroupBy(g => new { g.CreationDate.Date.Month, g.AccountType })
    .Select(t => new { t.Key.AccountType, t.Key.Month, Count = t.Count() });

edited Jan 21 '20 at 10:08

answered Jan 21 '20 at 10:03

Florim Maxhuni

1,421
1
17
35

How can I group on each month for all the account types? Can I have a dataset that only has 1-12 months with count of each account type? – Rahul Sharma Jan 21 '20 at 10:29
This does group your dataset base on month and account type.If you need also for different years then you must group by year also. – Florim Maxhuni Jan 21 '20 at 11:13
@RahulSharma " Can I have a dataset that only has 1-12 months" there are no more months... – Mong Zhu Jan 21 '20 at 11:59

score 0 · Answer 3 · edited Jan 21 '20 at 10:33

0

I Have Try Below Code:

var data = Users.GroupBy(x => new {x.column1 , x.column2 ,...})
.Select(y=> new className() { column1 = y.Key.column1 , column2 = y.Key.column2}).ToList<className>();

edited Jan 21 '20 at 10:33

Amin Golmahalleh

3,585
2
23
36

answered Jan 21 '20 at 10:16

Harish S

1
1

score 0 · Answer 4 · answered Jan 21 '20 at 10:22

If CreationDate is a string:

var group = result.GroupBy(g => new { DateTime.Parse(g.CreationDate).Date.Month, g.AccountType })
                  .Select(user => new { user.Key.AccountType, user.Key.Month, Count = user.Count() });

foreach (var g in group)
{
    Console.WriteLine($"Users with Month[{g.Month}] : {g.Count}, AccType: {g.AccountType};");
}

C#- LINQ Grouping on a custom created date range

4 Answers4