LINQ按日期分组-包括空天，不使用连接 [英] LINQ group by date - include empty days WITHOUT using join

问题描述

在LINQ中使用C#，NHibernate，NHibernate.使用NHibernate到LINQ，我没有JOIN功能.我也不能使用QueryOver.

Using C#, NHibernate, NHibernate to LINQ. Using NHibernate to LINQ, I do not have the JOIN functionality. I can not use QueryOver either.

我有一个LINQ查询，它计算潜在客户和销售量.该表仅在完成新的潜在客户或销售时创建，因此某些天没有插入任何行.进行以下查询即可

I have a LINQ query that counts the amount of Leads and Sales. This table only creates when a new lead or sale is done, so some days there is no row inserted. Doing the following query works

var query3 = query2.AsEnumerable()
           .Where(x => x.Created <= toDate.Value && x.Created >= fromDate.Value)
           .GroupBy(x => x.Created.Date)
           .Select(g => new ReferrerChart {
               Date = g.Key.Date,
               LeadsCount = g.Count(x => !x.IsSale),
               SalesCount = g.Count(x => x.IsSale)
           });

，但是其中没有某些日期(该日期有0个潜在顾客和销售).

but some dates are not in there (the dates there were 0 leads and sales).

如何包含这些日期，并让它们在不使用联接的情况下将LeadsCount = 0和SalesCount = 0设置为没有?

How can I include these dates and let them set LeadsCount = 0 and SalesCount = 0 WITHOUT using join?

有效的最终结果:

var selectedFromDate = fromDate.Value;
var selectedToDate = toDate.Value;

var selectedDates = Enumerable.Range(0, 1 + selectedToDate.Subtract(selectedFromDate).Days)
          .Select(offset => new ReferrerChart { 
                            Date = selectedFromDate.AddDays(offset),
                            LeadsCount = 0,
                            SalesCount = 0
                            })
          .ToList();

var query3 = Find(x => x.Source == "UserRef").AsEnumerable()
             .Where(x => x.Created <= toDate.Value && x.Created >= fromDate.Value)
             .GroupBy(x => x.Created.Date)
             .Select(g => new ReferrerChart {
                 Date = g.Key.Date,
                 LeadsCount = g.Count(x => !x.IsSale),
                 SalesCount = g.Count(x => x.IsSale)
             }).ToList();

var result = query3.Union(
                        selectedDates
                        .Where(e => !query3.Select(x => x.Date).Contains(e.Date)))
                        .OrderBy(x => x.Date);

推荐答案

您可以Union()使用在内存中创建的虚拟项目来查询数据.例如:

You can Union() the data from your query with dummy items that you create in memory. For example:

var query4 = query3.ToList(); // Prevent multiple execution.

var startDate = DateTime.Today.AddDays(-99);

var emptyData = Enumerable.Range(1,100).Select (i => 
    new ReferrerChart
        {
           Date = startDate.AddDays(i),
           LeadsCount = 0,
           SalesCount = 0
        });

var result = query4 .Union(
             emptyData
                .Where(e => !query4.Select(x => x.Date).Contains(e.Date)))
             .OrderBy(x => x.Date);

这篇关于LINQ按日期分组-包括空天，不使用连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！