时间戳

首页 > 解决方案 > C# 实体框架按小时分组销售

c# - C# 实体框架按小时分组销售

问题描述

我有一个具有以下结构的 Order 和 OrderDetails 类

public class Order 
{
   public int Id {get;set;}
   ........
}
public class OrderDetails
{
   public int Id {get;set;}
   public int TotalSales {get;set;}
   public DateTime OrderDate { get;set;}
   public int OrderId // navigation property to Order class
   ......
}

我需要汇总销售额并按小时和此时的订单数量对它们进行分组换句话说我需要这样

    {
       "date":"10-11-2018",
       "Hour":"9:00 AM",
       "totalSales":228.91,
       "OrdersCount":10
    },


    {
       "date":"10-11-2018",
       "Hour":"10:00 AM",
       "totalSales":150.8,
       "OrdersCount":5
    },

任何帮助，将不胜感激。

标签： c#sql-serverentity-frameworkgroup-by

解决方案

这可能可以优化，但它会做你要求的

public class OrderCollectionDetails
{
    public DateTime ForDate { get; set; }
    public double TotalSales { get; set; }
    public int OrderCount { get; set; }
}

public class OrderDetails
{
    public DateTime OrderDate { get; set; }
    public double Sales { get; set; }
}

    static void Main(string[] args)
    {
        List<OrderDetails> orders = new List<OrderDetails>();
        orders.Add(new OrderDetails() { OrderDate  = DateTime.Parse("12/14/2018 3:00"), Sales = 10 });
        orders.Add(new OrderDetails() { OrderDate  = DateTime.Parse("12/14/2018 3:30"), Sales = 20.5 });
        orders.Add(new OrderDetails() { OrderDate  = DateTime.Parse("12/14/2018 3:40"), Sales = 55.23 });
        orders.Add(new OrderDetails() { OrderDate = DateTime.Parse("12/14/2018 4:00"), Sales = 1.11 });
        var orderGrp = orders.GroupBy(o => new DateTime(o.OrderDate.Year, o.OrderDate.Month, o.OrderDate.Day, o.OrderDate.Hour, 0, 0));
        List<OrderCollectionDetails> details = new List<OrderCollectionDetails>();
        foreach(var grp in orderGrp)
        {
            var firstItm = grp.FirstOrDefault();
            var targetDate = new DateTime(firstItm.OrderDate.Year, firstItm.OrderDate.Month, firstItm.OrderDate.Day, firstItm.OrderDate.Hour, 0, 0);
            details.Add(new OrderCollectionDetails()
            {
                ForDate = targetDate,
                OrderCount = grp.Count(),
                TotalSales = grp.Sum(g => g.Sales)
            });
        }
    }

推荐阅读