Linq，分组查询

Linq求助，分组查询
需求：实现按月统计，SQL语句已实现如下

SQL code

select sum(UserConsumeMoney) as totalMoney, convert(nvarchar(7), ConsumeDay,120) as monthConsume from T_UserConsumeDaywhere DATEDIFF(MONTH,ConsumeDay,GETDATE())<12group by convert(nvarchar(7), ConsumeDay,120)

写了一个Linq：

C# code

from u in UserConsumeDay group u by u.ConsumeDay into con select new { con.Key, monthConsume = con.Sum(u => u.UserConsumeMoney) }

但是这具体到了天，在这里请教如何按月统计？

[解决办法]
ls 肯定没试过。直接在 EF 没法 ToString

参考下面：用 System.Data.Object.SqlFunctions 里的方法转换。

C# code

  var result = Database.Set<User>().Where(x => x.CreatedAt >= utcFrom && x.CreatedAt <= utcTo)        .GroupBy(x => new {             SqlFunctions.DateAdd("hh", offset, x.CreatedAt).Value.Year,             SqlFunctions.DateAdd("hh", offset, x.CreatedAt).Value.Month,             SqlFunctions.DateAdd("hh", offset, x.CreatedAt).Value.Day })        .Select(x => new { Date = x.Key, Count = x.Count() }); 

 

查看更多 下一篇