linq - EF 返回聚合和非聚合数据

Question

鉴于下表结构，我如何返回以下聚合 和 使用 Entity Framework 对字段进行分组。

必填字段
分组:
用户名
用户.姓氏

聚合:
最大值(订单.订单日期)
计数(订单.OrderId)
总和(付款.金额)
计数(过去 60 天内所有订单的订单.OrderId)
Sum(Payment.Amount for all orders in last 60 days)

表
用户:
用户 ID (PK)
名
姓

订单:
订单 ID (PK)
用户 ID (FK)
订购日期

付款:
付款 ID(PK)
订单编号 (FK)
支付金额

Answer 1

DateTime last60Days = new DateTime(xxx,xxx,xxx);

var query = from u in context.Users
            group u by new
            {
              u.Forename,
              u.Surname
            } into ug
            select new
            {
              Forename = ug.Key.Forename,
              Surname = ug.Key.Surname,
              MaxOrderdate = ug.SelectMany(x => x.Orders).Max(x => x.OrderDate),
              OrderCount = ug.SelectMany(x => x.Orders).Count(),
              Payment = ug.SelectMany(x => x.Orders.SelectMany(y => y.Payments)).Sum(x => x.PaymentAmount),
              OrderCountLast60 = ug.SelectMany(x => x.Orders).Where(x => x.OrderDate > last60Days).Count(),
              PaymentLast60 = ug.SelectMany(x => x.Orders.Where(x => x.OrderDate > last60Days).SelectMany(y => y.Payments)).Sum(x => x.PaymentAmount)
            };