Question

鉴于下表的结构,我如何利用实体的红外工作来回以下合计的和集群领域。

Required fields
Grouped:
User.Forename
User.Surname

Aggregated:
Maximum(Order.OrderDate) Count(Order.OrderId)
Sum(Payment.Amount)
Count(Order.OrderId for all orders in last 60 days)
Sum(Payment.Amount for all orders in last 60 days)

Tables
Users:
UserId (PK) Forename
Surname

Orders:
OrderId (PK)
UserId (FK)
OrderDate

Payments:
PaymentId (PK)
OrderId (FK)
PaymentAmount

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)
            };

友情链接