CODEWITHSUNDEEP
linqentity-frameworklinq-to-entitiesaggregate
Mark Cooper
Mark Cooper

Reputation: 6884

EF Return Aggregated and Non-Aggregated Data

Given the below table structure, how can I return the following aggregated and grouped fields using entity frawework.

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

Upvotes: 2

Views: 89

Answers (1)

Aducci
Aducci

Reputation: 26634

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

Upvotes: 3

Related Questions