Linq统计订单语句 (续)
数据表
Order
ID SalesID PName QUANTITY AMOUNT CREATEDATE
1 1 P1 5 50 2012-4-22
2 1 P2 2 30 2012-4-15
3 1 P1 2 20 2012-4-23
4 1 P1 1 15 2012-4-30 <- //P1的价格有所变化(不必理会)
5 1 P3 1 20 2012-3-30
6 2 P1 5 60 2012-2-5
7 2 P4 2 30 2012-4-2
8 2 P6 1 100 2012-4-28

条件 统计出每一个销售相同产品的上月销售数量和价格按上述表为例,结果应该是:
Order
SalesID PName QUANTITY AMOUNT
1 P1 8 85
1 P2 2 30
2 P4 2 30
2 P6 1 100

求Linq或Sql语句
====================================================================
q107770540
var query = from o in db.Orders
  let temp=new DateTime(DateTime.Now.Year,DateTime.Now.Month,1)
  where o.CREATEDATE <temp && o.CREATEDATE>temp.AddMonth(-1) group o by o.PName into g
  select new {
  g.First().SalesID,
  PName=g.Key,
  QUANTITY = g.Sum(x => x.QUANTITY),
  AMOUNT = g.Sum(x => x.AMOUNT)
  };
=====================================================================
现在有一个新的问题,我要在新生成的表中加一个Products表中的Images
New Table
SalesID PName QUANTITY AMOUNT Images
1 P1 8 85 ~/Upload/1.jpg
1 P2 2 30 ~/Upload/2.jpg
2 P4 2 30 ~/Upload/3.jpg
2 P6 1 100 ~/Upload/4.jpg

修改语句如下:
var query = from o in db.Orders join p in db.Products on o.OrdersProductID equals p.ProductID
  let temp=new DateTime(DateTime.Now.Year,DateTime.Now.Month,1)
  where o.CREATEDATE <temp && o.CREATEDATE>temp.AddMonth(-1) 
  group o by o.PName into g (问题:如何将p添加至g中,且不破坏之前结果.现在我用New{xxx=xxx}的结构查询出来记过就有误了)
  select new {
  g.First().SalesID,
  PName=g.Key,
  QUANTITY = g.Sum(x => x.QUANTITY),
  AMOUNT = g.Sum(x => x.AMOUNT)
  };


------解决方案--------------------

C# code

var query = from o in db.Orders join p in db.Products
  on o.OrdersProductID equals p.ProductID
  let temp=new DateTime(DateTime.Now.Year,DateTime.Now.Month,1)
  where o.CREATEDATE <temp && o.CREATEDATE>temp.AddMonth(-1)  
  group o by o.PName into g  
  select new {
  g.First().SalesID,
  PName=g.Key,
  QUANTITY = g.Sum(x => x.QUANTITY),
  AMOUNT = g.Sum(x => x.AMOUNT),
  Images=db.Products.FirstOrDefault(m=>m.ProudctID== g.First().OrdersProductID ).Images
  };