临床到实体，选择没有价值的组 [英] Linq to Entity, selecting group without value

问题描述

我希望能够按月份分组我的所有数据，即使几个月不包含任何数据。此时，我可以进行分组，但返回的数据只包含几个月的数据。

I would like to be able to group all my data by months even if some months doesn't contain any data. At this moment, I can group but the data returned contain only months with data.

这是我的代码如何：

var twelveMonthAgo = date.AddMonths(-12).Date;
var twelveMonthAgoFirstOfMonth = new DateTime(twelveMonthAgo.Year, twelveMonthAgo.Month, 1, 0, 0, 0, 0);

var data = (from i in Database.Users
            where i.RegisterDate >= twelveMonthAgoFirstOfMonth
            group i by new {y=i.RegisterDate.Year,m = i.RegisterDate.Month} into g
            select new UserStatistic{ Date = EntityFunctions.CreateDateTime(g.Key.y, g.Key.m, 1, 0, 0, 0)
                                       , UserCount = g.Count(o => o.Id)
                                     });

//The code below is what I would like to remove                                         
var toReturn = new List<UserStatistic>();
var allData = data.ToList();
DateTime datei = twelveMonthAgoFirstOfMonth;
while (datei.Year<= date.Year && datei.Month<=date.Month){
    var info = allData.SingleOrDefault(x => x.Date.HasValue && x.Date.Value.Year == datei.Year && x.Date.Value.Month == datei.Month);
    toReturn.Add(info ?? new UserStatistic { Date = datei, UserCount = 0, PaymentPaid = 0 });
    datei = datei.AddMonths(1);
}
return toReturn.AsQueryable();   

如您所见，注释下的代码构建了最近12个月的集合，并检查一些数据已经从数据库中删除，如果存在，则填写集合，否则放置0。

As you can see, the code under the comments build a collection of the last 12 months and check if some data has been out of the database and fill up the collection if some exist, otherwise put 0.

如何无需执行下面的代码呢？评论？

How can I do all that without having to do the code below the comment?

推荐答案

以下是使用组合加入子查询获取所需结果的一些代码：

Here's some code that uses a group join to a subquery get your desired results:

DateTime date = DateTime.Today;
DateTime firstOfMonth = new DateTime(date.Year, date.Month, 1);
DateTime twelveMonthAgoFirstOfMonth = firstOfMonth.AddMonths(-12);

// Generate a collection of the months and years for the last 12 months
var monthYears = Enumerable.Range(-12, 12).Select(monthOffset => { DateTime monthDate = firstOfMonth.AddMonths(monthOffset); return new { y = monthDate.Year, m = monthDate.Month }; });

// Go through the list of months and years and join them to the users retrieved from the database in the subquery.
var data = from monthYear in monthYears
           join i in (from i in Database.Users
                      where i.RegisterDate >= twelveMonthAgoFirstOfMonth && i.RegisterDate < firstOfMonth
                      select i) on monthYear equals new { y = i.RegisterDate.Year, m = i.RegisterDate.Month } into gj
           select new UserStatistic() { Date = new DateTime(monthYear.y, monthYear.m, 1), UserCount = gj.Count() });

这也可以在具有左外连接的子查询中表示为一个组：

This can also be expressed as a group in the subquery with a left outer join:

    DateTime date = DateTime.Today;
    DateTime firstOfMonth = new DateTime(date.Year, date.Month, 1);
    DateTime twelveMonthAgoFirstOfMonth = firstOfMonth.AddMonths(-12);
    var monthYears = Enumerable.Range(-12, 12).Select(monthOffset => { DateTime monthDate = firstOfMonth.AddMonths(monthOffset); return new { y = monthDate.Year, m = monthDate.Month }; });

    var data = (from monthYear in monthYears
                join i in (from i in Database.Users
                           where i.RegisterDate >= twelveMonthAgoFirstOfMonth && i.RegisterDate < firstOfMonth
                           group i by new {y = i.RegisterDate.Year, m = i.RegisterDate.Month} into g
                           select new { Key = g.Key, UserCount = g.Count() }) on monthYear equals i.Key into j
                 from k in j.DefaultIfEmpty()
                 select new UserStatistic() { Date = new DateTime(monthYear.y, monthYear.m, 1), UserCount = k != null ? k.UserCount : 0 });

由于我没有您的EF模型，您必须尝试一下，看看是否需要用 EntityFunctions.CreateDateTime 替换新的DateTime 。

Since I don't have your EF model, you'll have to try it and see if you need to replace new DateTime with EntityFunctions.CreateDateTime.

这篇关于临床到实体，选择没有价值的组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！