Linq - group by datetime过去12个月 - 包括空月 [英] Linq - group by datetime for previous 12 months - include empty months

问题描述

我有一个场景，我需要检索按日期时间字段月份分组的对象数。

我发现以下帖子让我成为在那里...

Linq：按年份和月份分组，并管理空的月份

...但是我需要列出上一个从今天起12个月，每个月的对象数量，这是我正在努力的地方。

我看过一些其他类似的问题/解决方案但是我选择了上述的一个，因为它也是要求在任何月份生成一个记录，计数为0。

感谢任何帮助我可以得到这个。 / p>

编辑

好的，我有点进一步感谢Enigmativity（感谢花费时间！）：

  var news = from db in the db.NewsItems 
其中s.SubmittedDate>首先
选择新
 {
 Date = s.SubmittedDate，
 Title = s.Title，
}; 
 
 var grouping = from g in news.AsEnumerable（）
选择新的NewsCountCollection 
（
 g.Date，
 g.Title 
 ）; 
 
 var lookup = grouping.ToLookup（x => x.Month，x => x.Title）; 
 
 varcount =从n可以枚举（-11，12）
允许月= last.AddMonths（n）
选择新
 {
月，
计数=查找[月] .Count（），
}; 
 
 var countList = from c in counts.AsEnumerable（）
选择新的NewsCountMonthList 
（
 c.Month.ToString（MMMM），
 c.Count 
）; 
  

...和以下

  public class NewsCountCollection 
 {
 public DateTime Month {get;组; } 
 public string标题{get;组; 
 
 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;组; } 
 public int Count {get;组; 
 
 public NewsCountMonthList（string month，int count）
 {
 this.Month = month; 
 this.Count = count; 
} 
} 
  

...似乎非常低效，但...不禁想到一定要有比这更好的办法。我在正确的轨道上吗？

解决方案

这应该为你做：

  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.

这篇关于Linq - group by datetime过去12个月 - 包括空月的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！