LINQ：在日期时间字段内按月份和年份分组 [英] LINQ: Group by month and year within a datetime field

问题描述

我有一个带有日期时间字段的表格。我想检索按月份/年份组合分组的结果集以及该月/年内出现的记录数。如何在LINQ中完成这项工作？

我能够找到的最接近TSQL的是：

<从
中选择子字符串（mo，charindex（mo，'/'），50）select mo = convert（varchar（2），month（created））+' /'+ convert（varchar（4），year（created））
，qty = count（convert（varchar（2），month（created））+'/'+ convert（varchar（4），year创建）））
从
组转换（varchar（2），month（创建））+'/'+ convert（varchar（4），year（创建））
） a
order by substring（mo，charindex（mo，'/'）+ 1,50）

但我不会说这样做......

解决方案

var分组= from post in
group p by new {month = p.Create.Month，year = p.Create.Year} into d
select new {dt = string.Format（{0} / {1}，d.Key.month，d.Key.year），count = d.Count（）};


以下是 LINQ 。为此，您还需要了解多列分组

有序降序

 var groupped =（从post中的p 
 group p by new {month = p.Create.Month，year = p.Create.Year} into d 
 select new {dt = string.Format（{0} / {1}，d.Key.month，d.Key.year），count = d.Count（）}）。OrderByDescending（g => g.dt） ; 
  

I have a table with a datetime field. I want to retrieve a result set grouped by the month/year combination and the number of records that appear within that month/year. How can this be done in LINQ?

The closest I've been able to figure out is in TSQL:

select substring(mo,charindex(mo,'/'),50) from (
select mo=convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created)) 
 ,qty=count(convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created)))
from posts 
group by convert(varchar(2),month(created)) + '/' + convert(varchar(4), year(created))
) a
order by substring(mo,charindex(mo,'/')+1,50)

But I wouldn't say that works...

解决方案

var grouped = from p in posts
     group p by new { month = p.Create.Month,year= p.Create.Year } into d
     select new { dt = string.Format("{0}/{1}",d.Key.month,d.Key.year), count = d.Count() };

Here's the list of DateTime functions available in LINQ. For this to work you'll also need to understand multi-column grouping

ordered descending

var grouped = (from p in posts 
  group p by new { month = p.Create.Month,year= p.Create.Year } into d 
  select new { dt = string.Format("{0}/{1}",d.Key.month,d.Key.year), count = d.Count()}).OrderByDescending (g => g.dt);

这篇关于LINQ：在日期时间字段内按月份和年份分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！