EF返回汇总和非汇总数据
问题描述:
鉴于下表结构,如何使用实体frawework返回以下汇总的和分组字段。EF返回汇总和非汇总数据
必填字段
分组:
User.Forename
User.Surname
汇总:
最大值(Order.OrderDate) 计数(Order.OrderId)
萨姆(Payment.Amount)
Count(Order.OrderId for last orders in last 60 days) )
总和(Payment.Amount在过去60天内的所有订单)
表
用户:
用户ID(PK) 用的名字
姓
订单:
OrderId(PK)
UserId(FK)
次订购日期
付款:
PaymentId(PK)
的OrderId(FK)
PaymentAmount
答
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)
};
出色答卷。正是我需要的,谢谢。 – 2011-06-15 06:32:26