更新时间:2022-11-12 21:28:38
这应该为你做:
var now = DateTime.Now;
var last = new DateTime(now.Year,now.Month,1);
var first = last.AddMonths(-12);
var query =
from s in somethings
其中s.DateTimeField> = first
其中s.DateTimeField<最后
选择新
{
Month = new DateTime(s.DateTimeField.Year,s.DateTimeField.Month,1),
Something = s,
};
var lookup = query.ToLookup(x => x.Month,x => x.Something);
varcount =
从n可以枚举(-12,12)
让月= last.AddMonths(n)
选择新
{
Month,
Count = lookup [Month] .Count(),
};
您可能需要解决它,但结构应该是健全的。
I have a scenario whereby I need to retrieve a count of objects grouped by the month of a datetime field.
I found the following post which gets me part of the way there...
Linq: group by year and month, and manage empty months
...but I need to list the previous 12 months from today's date and the count of objects for each month, which is where I'm struggling.
I've seen a few other posts with similar issues/solutions but I chose the above one as it's also a requirement to produce a record for any months with a count of 0.
Thanks for any help I can get on this.
EDIT
OK, I got a little further thanks to Enigmativity (Thanks for taking the time!):
var news = from s in db.NewsItems
where s.SubmittedDate > first
select new
{
Date = s.SubmittedDate,
Title = s.Title,
};
var grouping = from g in news.AsEnumerable()
select new NewsCountCollection
(
g.Date,
g.Title
);
var lookup = grouping.ToLookup(x => x.Month, x => x.Title);
var counts = from n in Enumerable.Range(-11, 12)
let Month = last.AddMonths(n)
select new
{
Month,
Count = lookup[Month].Count(),
};
var countList = from c in counts.AsEnumerable()
select new NewsCountMonthList
(
c.Month.ToString("MMMM"),
c.Count
);
...and the following
public class NewsCountCollection
{
public DateTime Month { get; set; }
public string Title { get; set; }
public NewsCountCollection(DateTime date, string title)
{
this.Month = new DateTime(date.Year, date.Month, 1);
this.Title = title;
}
}
public class NewsCountMonthList
{
public string Month { get; set; }
public int Count { get; set; }
public NewsCountMonthList(string month, int count)
{
this.Month = month;
this.Count = count;
}
}
...seems very inefficient though...I can't help thinking there must be a better way than this. Am I on the right track?
This should do it for you:
var now = DateTime.Now;
var last = new DateTime(now.Year, now.Month, 1);
var first = last.AddMonths(-12);
var query =
from s in somethings
where s.DateTimeField >= first
where s.DateTimeField < last
select new
{
Month = new DateTime(s.DateTimeField.Year, s.DateTimeField.Month, 1),
Something = s,
};
var lookup = query.ToLookup(x => x.Month, x => x.Something);
var counts =
from n in Enumerable.Range(-12, 12)
let Month = last.AddMonths(n)
select new
{
Month,
Count = lookup[Month].Count(),
};
You may need to fiddle with it a bit, but the structure should be sound.