Write a comparable LINQ query for an aggregated standalone account in sql?

Question

I want to get an invoice for each month, but the invoice should be no more than one per day, even if there are several cases. I have an SQL query that works correctly, but it has problems converting to 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) 

Can someone help me translate the above query into LINQ. Thanks!

Answer 1

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 do not know if L2S will be able to correctly convert the internal g.Select(act => act.Date.Day).Distinct.Count() .

Answer 2

 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() })