c# - 为 sql 中的聚合非重复计数编写一个可比较的 LINQ 查询？

Question

我想获得每个月的计数，但即使出现多次，每天最多只能计数一次。我的 SQL 查询可以正常工作，但无法将其转换为 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) 

谁能帮我把上面的查询翻译成 LINQ。谢谢!

Answer 1

根据 LINQ to SQL using GROUP BY and COUNT(DISTINCT) @Rick 给出的，这应该有效:

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
            };

不知道L2S能否正确转换内部 g.Select(act => act.Date.Day).Distinct.Count() /p>