代码之家 › 专栏 › 技术社区 › Richard Reddy

使用Linq to SQL生成销售报告

sum grouping linq-to-sql c#

Richard Reddy · 技术社区 · 16 年前

我目前有以下代码来生成过去30天的销售报告。我想知道是否可以使用LINQ在一个步骤中生成这个报告,而不是我在这里使用的非常基本的循环。

对于我的要求,每天都需要向我返回一个值,因此如果任何一天没有销售,则返回0。

这里的任何sum-linq示例都无法解释如何包括where过滤器,因此我对如何获取每天的总金额感到困惑,或者如果没有销售,在我过去的几天里是0。

谢谢你的帮助, 丰富的

    //setup date ranges to use
    DateTime startDate = DateTime.Now.AddDays(-29);
    DateTime endDate = DateTime.Now.AddDays(1);
    TimeSpan startTS = new TimeSpan(0, 0, 0);
    TimeSpan endTS = new TimeSpan(23, 59, 59);

    using (var dc = new DataContext())
    {
        //get database sales from 29 days ago at midnight to the end of today
        var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS));

        //loop through each day and sum up the total orders, if none then set to 0
        while (startDate != endDate)
        {
            decimal totalSales = 0m;
            DateTime startDay = startDate.Date + startTS;
            DateTime endDay = startDate.Date + endTS;
            foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay))
            {
                totalSales += (decimal)sale.OrderPrice;
            }

            Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>");

            //move to next day
            startDate = startDate.AddDays(1);
        }
    }

编辑: 约翰内斯的回答是一个很好的方法来处理我的问题。下面是对代码的一个调整,以使它在本例中正常工作,以防其他人遇到此问题。这将从alldays表执行外部联接,当当天没有销售时返回0值。

var query = from d in allDays
                    join s in salesByDay on d equals s.Day into j
                    from s in j.DefaultIfEmpty()
                    select new { Day = d, totalSales = (s != null) ? s.totalSales : 0m };

2 回复 | 直到 16 年前

Johannes Rudolph 16 年前

您可以按天对所有数据进行分组,并对这些组进行求和。为了满足每天都有一个总金额的要求,即使是那些没有订单的,您可以加入所有日期的列表,或者简单地使用一个循环来确保包括所有日期。小提示:如果按 DateTime.Date 性质。

下面是使用生成器函数(取自Morelinq项目)的解决方案:

public static partial class MoreEnumerable
{

    public static IEnumerable<TResult> GenerateByIndex<TResult>(Func<int, TResult> generator)
    {
        // Looping over 0...int.MaxValue inclusive is a pain. Simplest is to go exclusive,
        // then go again for int.MaxValue.
        for (int i = 0; i < int.MaxValue; i++)
        {
            yield return generator(i);
        }
        yield return generator(int.MaxValue);
    }

}

public class MyClass
{
    private void test()
    {
        DateTime startDate = DateTime.Now.AddDays(-29);
        DateTime endDate = DateTime.Now.AddDays(1);

        using (var dc = new DataContext())
        {
            //get database sales from 29 days ago at midnight to the end of today
            var salesForPeriod = dc.Orders.Where(b => b.OrderDateTime > startDate.Date  && b.OrderDateTime <= endDate.Date);

            var allDays = MoreEnumerable.GenerateByIndex(i => startDate.AddDays(i)).Take(30);

            var salesByDay = from s in salesForPeriod
                        group s by s.OrderDateTime.Date into g
                        select new {Day = g.Key, totalSales = g.Sum(x=>(decimal)x.OrderPrice};

            var query = from d in allDays
                        join s in salesByDay on s.Day equals d
                        select new {Day = s.Day , totalSales = (s != null) ? s.totalSales : 0m;


            foreach (var item in query)
            {
                Response.Write("Date: " +item.Day.ToString() " Sales: " + String.Format("{0:0.00}", item.totalSales) + "<br>");
            }


        }
    }
}

Jimmy W 16 年前

我认为如果枚举不包含一天的数据,则不能返回该天的值。我能想到的最好方法是为每天创建一个值为零的order对象列表,并用查询结果创建一个联合。这就是我想到的。但是,我认为循环遍历每个组,检查是否有一天被“跳过”,并为被“跳过”的每一天返回零比在内存中创建自己的枚举更简单(除非您想要一个填充了“缺少间隙”的枚举)。请注意,我基本上假设,对于每个组,您希望对一天的所有值求和。

List<Order> zeroList = new List<Order>();
while (startDate <= endDate)
{
  zeroList.Add(new Order { OrderDateTime = startDate, OrderPrice = 0 });
  startDate = startDate.AddDays(1)
}

var comboList = zeroList.Union(dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS))

var groupedTotalSales = comboList.GroupBy(b => b.OrderDateTime.Date)
  .Select(b => new { StartDate = Convert.ToDateTime(b.Key + startTS), EndDate = Convert.ToDateTime(b.Key + endTS), Sum = b.Sum(x => x.OrderPrice });

foreach (totalSale in groupedTotalSales)
  Response.Write("From Date: " + totalSale.StartDate + " - To Date: " + totalSale.EndDate + ". Sales: " + String.Format("{0:0.00}", (decimal)totalSale.Sum) + "<br/>");