Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Can anyone could help me write this to linq using extension method??

For the resule, I only need the month and the count(CreatedDate) value

select YEAR(CreatedDate),MONTH(CreatedDate),count(CreatedDate) as number 
from Accounts
group by YEAR(CreatedDate),MONTH(CreatedDate)
Posted

I'm not sure if you mean you don't need the year in the result or if you also don't want to group by the year. So here's one version with the year and one without:
C#
var result1 = Accounts.GroupBy(a => new { Year = a.CreatedDate.Year, Month = a.CreatedDate.Month })
                      .Select(g => new { Year = g.Key.Year, Month = g.Key.Month, Count = g.Count() });
                    //.ToList();  //optionally

var result2 = Accounts.GroupBy(a => a.CreatedDate.Month)
                      .Select(g => new { Month = g.Key, Count = g.Count() });
                    //.ToList();  //optionally
 
Share this answer
 
Comments
Maciej Los 4-May-15 2:00am    
+5!
Sascha Lefèvre 4-May-15 3:08am    
Thank you, Maciej! :)
In addition to solution1 by Sascha[^], you can use something like this:

C#
var result = Accounts.GroupBy(a => a.CreatedDate.ToString("yyyy-MM"))
                      .Select(g => new { Month = g.Key, Count = g.Count() });
 
Share this answer
 
Comments
SandyZhang2014 4-May-15 2:36am    
Thanks, all finished. done.
Sascha Lefèvre 4-May-15 3:12am    
If you still need that 6-weeks-query:

DateTime sixWeeksAgo = DateTime.Now.Date - new TimeSpan(6 * 7 - 1, 0, 0, 0);
var result3 = Accounts.Where(a => a.CreatedDate >= sixWeeksAgo)
.GroupBy(a => (int)(a.CreatedDate - sixWeeksAgo).TotalDays / 7)
.Select(g => new { Week = g.Key, Count = g.Count() });

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900