Linq多表合一查询

这里有四张表

//进货
public class Buy
{
	//进货表id
	public int BuyID { get; set; }
	//供应商id
	public int SupID { get; set; }
}

//进货明细
public class BuyItems
{
	//进货明细id
	public int BID { get; set; }
	//进货id
	public int BuyID { get; set; }
	//商品id
	public int ProductID { get; set; }
	//进货总数
	public int BuyCount { get; set; }

}

//退货
public class Returns
{
	//退货id
	public int ReturnID { get; set; }
	//供应商id
	public int SupID { get; set; }
}

//退货明细
public class ReturnItems
{
	//退货明细id
	public int RID { get; set; }
	//退货id
	public int ReturnID { get; set; }
	//商品id
	public int ProductID { get; set; }
	//退货总数
	public int ReturnCount { get; set; }
}


现在需要查询出供应商的所有进、退货记录,并按供应商与商品分组,最后按供应商与商品id的顺序排列,查询结果汇总在一张表中。

//BuyTable为进货表(Buy)
//BuyItemsTable为进货明细表(BuyItems)
//ReturnsTable为退货表(Returns)
//ReturnItemsTable为退货明细表(ReturnItems)

var query = BuyTable.Join(BuyItemsTable, a => a.BuyID, b => b.BuyID, (a, b) => new
{
	a.SupID,
	b.ProductID,
	BuyCount = b.BuyCount,
	ReturnCount = 0,
}).Concat(ReturnsTable.Join(ReturnItemsTable, a => a.ReturnID, b => b.ReturnID, (a, b) => new
{
	a.SupID,
	b.ProductID,
	BuyCount = 0,
	ReturnCount = b.ReturnCount,
})).GroupBy(p => new { p.SupID, p.ProductID }).Select(p => new
{
	p.Key.SupID,
	p.Key.ProductID,
	SumBuy = p.Sum(s => s.BuyCount),
	SumReturn = p.Sum(s => s.ReturnCount)
}).OrderBy(p => p.SupID).ThenBy(p => p.ProductID);


©️2020 CSDN 皮肤主题: 大白 设计师: CSDN官方博客 返回首页
实付0元
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值