Write a comparable LINQ query for aggregate distinct count in sql?

Question

I want to get a count for each month but count should be only at most one per day even if there are multiple occurences . I have the SQL query which works right but having trouble to convert it into LINQ -

select 
    count(DISTINCT DAY(date)) as Monthly_Count,
    MONTH(date) as Month,
    YEAR(date)
from 
    activity
where 
    id=@id
group by
    YEAR(date),
    MONTH(date)

Could anyone help me translating the above query to LINQ. Thanks!

Ah k sorry about that. I even checked the edit history to see if you had :P. Didnt show anything. — Gage, Jul 26 '11 at 20:29
Related information here: http://stackoverflow.com/questions/448203/linq-to-sql-using-group-by-and-countdistinct (Yes, it's VB instead of C#, but the concepts are applicable) — Rick Liddle, Jul 26 '11 at 20:45

score 8 · Accepted Answer · edited May 23 '17 at 11:52

Per LINQ to SQL using GROUP BY and COUNT(DISTINCT) given by @Rick, this should work:

var query = from act in db.Activity
            where act.Id == id
            group act by new { act.Date.Year, act.Date.Month } into g
            select new
            {
                MonthlyCount = g.Select(act => act.Date.Day).Distinct().Count(),
                Month = g.Key.Month,
                Year = g.Key.Year
            };

~~I don't know if L2S can convert the inner g.Select(act => act.Date.Day).Distinct.Count() properly.~~

score 0 · Answer 2 · answered Jul 26 '11 at 20:48

var results = db.activities.Where(a => a.id == myID)
                           .GroupBy(a => new 
                                         {
                                             Month = a.date.Month, 
                                             Year = a.date.Year
                                         })
                           .Select(g => new
                                        {
                                            Month = g.Key.Month,
                                            Year = g.Key.Year,
                                            Monthly_Count = g.Select(d => d.date.Day)
                                                             .Distinct()
                                                             .Count()
                                        })

Write a comparable LINQ query for aggregate distinct count in sql?

2 Answers2