两个query的join查询,重点是group by的写法:(query可以用list等替换)
var recVoucher = db.GetQuery<YWRecVoucherEntity>();
var promoto = db.GetQuery<YWPromotionsEntity>();var data1 = (from a in recVoucher
join b in promoto
on a.PromotionsCode equals b.PromotionsCode
where a.CustomerCode == CustomerCode
&& a.PromotionsCode == PromotionsCode
&& a.OrderType != 2
&& a.BillDate >= sBeginDate
&& a.BillDate <= sEndDate
group new { a, b } by new { a.CustomerCode, a.PromotionsCode } into g
select new VRecVoucherEntity
{
PromotionsType = g.Max(q => q.b.PromotionsType),
CustomerCode = g.Key.CustomerCode,
CustomerName = customerName,
PromotionsCode = g.Key.PromotionsCode,
SumQty = g.Sum(q => q.a.Qty),
SumTotal = g.Sum(q => q.a.PayTotal)
}).ToList();
下面是单个的group by
var recVoucher = db.GetQuery<YWRecVoucherEntity>();
var data1 = (from a in recVoucher
where a.CustomerCode == CustomerCode
&& a.PromotionsCode == PromotionsCode
&& a.OrderType != 2
&& a.BillDate >= sBeginDate
&& a.BillDate <= sEndDate
group a by new {a.CustomerCode,a.PromotionsCode} into g
select new VRecVoucherEntity
{
CustomerCode = g.Key.CustomerCode,
CustomerName = customerName,
PromotionsCode = g.Key.PromotionsCode,
SumQty = g.Sum(q => q.Qty),
SumTotal = g.Sum(q => q.PayTotal)
}).ToList();