LINQ:在日期时间字段内按月份和年份分组
问题描述:
我有一个带有日期时间字段的表。我想检索按月份/年份组合分组的结果集以及该月/年内出现的记录数。这怎么可以在LINQ中完成?LINQ:在日期时间字段内按月份和年份分组
最近我已经能够找出在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)
但我不会说,工程...
答
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() };
下面是日期时间函数LINQ提供的列表。对于这个工作,你还需要了解multi-column grouping
勒令降
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);
答
你也可以这样来做
from o in yg
group o by o.OrderDate.ToString("MMM yyyy") into mg
select new { Month = mg.Key, Orders = mg }
你的结果将是
{2014年1月,25} {Feb 2015,15} etc ...
答
这是为那些正在尝试完成但使用lambda表达式的人。
假设你已经有实体的集合,每个实体都有订购日期作为它的一个属性。
yourCollection
// This will return the list with the most recent date first.
.OrderByDescending(x => x.OrderDate)
.GroupBy(x => new {x.OrderDate.Year, x.OrderDate.Month})
// Bonus: You can use this on a drop down
.Select(x => new SelectListItem
{
Value = string.Format("{0}|{1}", x.Key.Year, x.Key.Month),
Text = string.Format("{0}/{1} (Count: {2})", x.Key.Year, x.Key.Month, x.Count())
})
.ToList();
如果您不需要SelectListItem的集合,然后只用这一个替代选择:
.Select(x => string.Format("{0}/{1} (Count: {2})", x.Key.Year, x.Key.Month, x.Count()))
答
这里是在分组的DateTime一个简单的解决方案。
List<leaveallview> lav = new List<leaveallview>();
lav = dbEntity.leaveallviews.Where(m =>m.created==alldate).ToList();
dynamic lav1 = lav.GroupBy(m=>m.created.Value.GetDateTimeFormats()).FirstOrDefault().ToList();
我想排序这个月份,像第一个当月在上个月和下个月一样等等。请帮助 – 2010-10-11 16:52:46